PostgreSQL: View database connect permissions

Question

How do you view users that have been issued GRANT CONNECT ON DATABASE <database> TO <user>?

• \dp - lists table/view permissions
• \dn+ - lists schema permissions
• \l+ does not list all users that can access the database

Answer 1

A bit odd if the \l+ command just displays some of the users that have permission/privilege to connect to the database. I could not repeat that myself on a PostgreSQL 8.4 installation (Ubuntu 10.04 LTS). What version are you using?

Anyway, perhaps you could check the table holding the ACL's for that particular database and from that deduce whether the user has the correct privileges or not:

SELECT datname as "Relation", datacl as "Access permissions" FROM pg_database WHERE datname = 'databasename'; 

If you just want to check one user you could do something like this:

SELECT * FROM has_database_privilege('username', 'database', 'connect'); 

How are the permissions/privileges to interpreted? The privileges are to be read like this:

user = privileges / granted by  

Omitting user means that PUBLIC is granted the privilege, ie all roles. For example if the privilege is =Tc/postgres then all roles may connect and create temporary tables in that particular database and it is the postgres user who granted the privilege.

There is a synopsis at the PostgreSQL site explaining the different privileges: https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE.

rolename=xxxx -- privileges granted to a role         =xxxx -- privileges granted to PUBLIC              r -- SELECT ("read")             w -- UPDATE ("write")             a -- INSERT ("append")             d -- DELETE             D -- TRUNCATE             x -- REFERENCES             t -- TRIGGER             X -- EXECUTE             U -- USAGE             C -- CREATE             c -- CONNECT             T -- TEMPORARY       arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)             * -- grant option for preceding privilege          /yyyy -- role that granted this privilege