Reject Specific Database PostgreSQL User Authentication [closed]

Lets say I have two databases: Sales and Inventory

And two users: A and B

I want user A to be able to do: psql sales B

But user A must not be able to do: psql inventory B

Right now user A can access both of the above psql databases by logging in as user B, as done above.

Here’s 3 options I’ve considered:

• Edit the /opt/pgsql/data/pg_hba.conf file
  • But, configuring that file creates tons of security issues
• Create a ~/.pgpass file for user A and B
  • For some reason I can’t even get postgres to acknowledge the file’s existence, even when I enter ~/.pgpass‘s contents as *:*:*:B:wrong_password
• Create a PGPASSWORD environment variable
  • Not database specific

If those two databases are on the same cluster (beware of the PostgreSQL terminology, it’s a name for an instance or installation or whatever you want), then you cannot prevent the users from logging in. All databases on a single cluster share the same users:

Database roles are conceptually completely separate from operating system users. In practice it might be convenient to maintain a correspondence, but this is not required. Database roles are global across a database cluster installation (and not per individual database).

As Neil McGuigan pointed out in his comment, this is not completely true – one can set db_user_namespace to have database specific user names. I don’t recommend using this, the reason is in the documentation:

Note: This feature is intended as a temporary measure until a complete solution is found. At that time, this option will be removed.

Also, if user B knows the password of a@databasea, this cannot prevent him from logging in.

What you can do, however, is preventing user A from doing anything in databaseB by revoking every permission there might be, especially connecting to a database. The background is that logging in to a cluster is not the same thing as connecting to a database. Preventing the latter is easy:

REVOKE ALL ON DATABASE databaseb FROM a; -- ALL means CONNECT, too

Please note that pg_hba.conf does its work also on the database level, so you can disallow connections there, too:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
host    databasea       a               10.228.116.0/24         md5
host    databaseb       b               10.228.116.0/24         md5