Basic configuration of database permissions

Question

I'm learning databases and I have a question how to prepare the rules and groups configuration for the following assumptions:

I have two databases:

• databaseA
• databaseB

Each database will have several users connecting using the connectionString (server, port, database, username, password). So I have 3 users to each database:

• userA1, userA2, userA3
• userB1, userB2, userB3

How to create groups and roles and how to grant permission so that users A can only connect to database A and users B to database B. Can I make A users not see database B, have not seen users assigned to database B?

Probably it is a simple question, but I would like to see an example of how to do it - for now I have A and B database and the postgres user.

Answer 1

There is no point in trying to keep others from seeing a database's or user's metadata, don't try. PostgreSQL does not support that.

You create a user (a “login role”) with

CREATE ROLE user1a LOGIN;

As to the permissions, you first have to remove the default privilege that allows everyone to connect to the database:

REVOKE CONNECT, TEMP ON DATABASE databasea FROM PUBLIC;

Then you have to specifically allow the required users in:

GRANT CONNECT ON DATABASE databasea TO usera1, usera2, usera3;

If you have many users, or the users change frequently, it is better to use a group (a “nologin role”) that has the CONNECTprivilege, and you add the users to the group.

You have to configure pg_hba. conf so that the users are allowed to authenticate, see the documentation.