How to check all roles/user/group_role have what privileges in postgres database?

Question

I have postgres database. I want the list of users with access privileges they are being assigned.

I tried to find query and also looked in to psql command line help. (\nu and all) but I haven't found any usefull information.

Is anyone knows about that can help me out.

Thanks.

Answer 1

There are few basic command like \du and \l that will provide the general information.

For getting the detailed information you may use the below function.

CREATE OR REPLACE FUNCTION database_privs(text) RETURNS table(username text,dbname name,privileges  text[])
AS
$$
SELECT $1, datname, array(select privs from unnest(ARRAY[
( CASE WHEN has_database_privilege($1,c.oid,'CONNECT') THEN 'CONNECT' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'CREATE') THEN 'CREATE' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMPORARY') THEN 'TEMPORARY' ELSE NULL END),
(CASE WHEN has_database_privilege($1,c.oid,'TEMP') THEN 'CONNECT' ELSE NULL END)])foo(privs) WHERE privs IS NOT NULL) FROM pg_database c WHERE 
has_database_privilege($1,c.oid,'CONNECT,CREATE,TEMPORARY,TEMP') AND datname not in ('template0');
$$ language sql;

and then call the same function by providing the username/role that you get from \du

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 test      |                                                            | {}
 test2     |                                                            | {}
 test3     |                                                            | {}

postgres=# select * from database_privs('test');
 username |  dbname   |         privileges          
----------+-----------+-----------------------------
 test     | postgres  | {CONNECT,TEMPORARY,CONNECT}
 test     | template1 | {CONNECT}
 test     | test      | {CONNECT,TEMPORARY,CONNECT}
(3 rows)