How to show what privileges a user has on a database in Postgres database?

Question

I have tried to write a script that should show me who has privileges on a database and what are these privileges. My problem is that the script is outputting that everyone has all the privileges connect, create, temp on all the databases, however that is not true.

My script is like this:

DO
$$
DECLARE
db_r record;
BEGIN
     FOR db_r IN
      select db.datname, rl.rolname, case 
                           when has_database_privilege(rl.rolname, db.datname, 'connect, create, temp') then
                           cast ('connect, create, temp' AS pg_catalog.text)
                           when has_database_privilege(rl.rolname, db.datname, 'connect, create') then
                           cast ('connect, create' AS pg_catalog.text)
                           when has_database_privilege(rl.rolname, db.datname, 'connect, temp') then
                           cast ('connect, temp' AS pg_catalog.text)
                           when has_database_privilege(rl.rolname, db.datname, 'create, temp') then
                           cast ('create, temp' AS pg_catalog.text)
                           when has_database_privilege(rl.rolname, db.datname, 'connect') then
                           cast ('connect' AS pg_catalog.text)
                           when has_database_privilege(rl.rolname, db.datname, 'create') then
                           cast ('create' AS pg_catalog.text)
                           when has_database_privilege(rl.rolname, db.datname, 'temp') then
                           cast ('temp' AS pg_catalog.text)
                         end privilege
      from pg_roles rl
        cross join pg_database db
      where rl.rolcanlogin
        and db.datallowconn
        and db.datname not in ('postgres', 'template0', 'template1')
      order by db.datname, rl.rolname
        LOOP
         IF db_r.privilege is not null then
         RAISE NOTICE '% has % privilege on database %;',
                 db_r.rolname, db_r.privilege, db_r.datname;
         END IF;
        END LOOP;
END
$$;

There must surely be something wrong in the logics of my script, but I cannot see it. Could anyone help me with it?

Answer 1

I ended up finding an answer by myself.

The problem was in the way I built my case statement.

I have corrected it and it is like this now:

DO
$$
DECLARE
db_r record;
BEGIN
     FOR db_r IN
      select db.datname, rl.rolname, case 
                                       when has_database_privilege(rl.rolname, db.datname, 'connect')
                                       and has_database_privilege(rl.rolname, db.datname, 'create')
                                       and has_database_privilege(rl.rolname, db.datname, 'temp') then
                                       cast ('connect, create, temp' AS pg_catalog.text)
                                       
                                       when has_database_privilege(rl.rolname, db.datname, 'connect')
                                       and has_database_privilege(rl.rolname, db.datname, 'create') then
                                       cast ('connect, create' AS pg_catalog.text)
                                       
                                       when has_database_privilege(rl.rolname, db.datname, 'connect')
                                       and has_database_privilege(rl.rolname, db.datname, 'temp') then
                                       cast ('connect, temp' AS pg_catalog.text)
                                       
                                       when has_database_privilege(rl.rolname, db.datname, 'create')
                                       and has_database_privilege(rl.rolname, db.datname, 'temp') then
                                       cast ('create, temp' AS pg_catalog.text)
                                       
                                       when has_database_privilege(rl.rolname, db.datname, 'connect') then
                                       cast ('connect' AS pg_catalog.text)
                                       
                                       when has_database_privilege(rl.rolname, db.datname, 'create') then
                                       cast ('create' AS pg_catalog.text)
                                       
                                       when has_database_privilege(rl.rolname, db.datname, 'temp') then
                                       cast ('temp' AS pg_catalog.text)
                         end privilege
      from pg_roles rl
        cross join pg_database db
      where rl.rolcanlogin
        and db.datallowconn
        and db.datname not in ('postgres', 'template0', 'template1')
      order by db.datname, rl.rolname
        LOOP
         IF db_r.privilege is not null then
         RAISE NOTICE '% has % privilege on database %;',
                 db_r.rolname, db_r.privilege, db_r.datname;
         END IF;
        END LOOP;
END
$$;

The problem was that I was trying to test if a user/role has more than one privilege on a database using one function call. To correct it, I had to call the function once for every privilege using the operator and in my case statement, in order to evaluate the case that a user/role has more than one privilege on a database.

Now the code shows the correct output: what privileges a user/role has on a database.