PostgreSQL – List Database CONNECT Privileges

permissionspostgresql

How can I view database-level privileges like CONNECT of users?

I found 2 ways already, but neither of them is perfect:

Use function has_database_privilege().

This works, but I have to run it manually for every user one by one:

SELECT has_database_privilege('david', 'postgres', 'connect');

This one seems to return an incomplete result. It returns NULL at first, but david and other users can still connect to postgres DB. Only after I run any GRANT command (and then REVOKE of course) on that DB, the table is filled with some result: {=Tc/postgres,postgres=CTc/postgres}. So I guess I have to GRANT and then REVOKE every time before I use this method?

SELECT datname as "Relation", 
       datacl as "Permissions" 
FROM pg_database 
WHERE datname = 'postgres';

Using PostgreSQL 10.9 on Ubuntu

Best Answer

You can use the function as part of a select that retrieves all users:

SELECT rolname, has_database_privilege(rolname, 'postgres', 'connect')
from pg_roles
where rolcanlogin;

You can extend that, to show this information for every user in every database:

select db.datname, r.rolname, has_database_privilege(r.rolname, db.datname, 'connect')
from pg_roles r
  cross join pg_database db
where r.rolcanlogin
  and db.datallowconn
order by db.datname, r.rolname;
Related Question