Reject Specific Database PostgreSQL User Authentication 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