How to Check List of Users in All PostgreSQL Databases

postgresqlusers

I have around 50 of postgres database. I need to check all users with their respective database name. I know \du gives the list of all users like,

                                    List of roles
            Role name             |            Attributes             |      Member of
----------------------------------+-----------------------------------+----------------------
foo                             Superuser                              {}
..
..

which doesn't display database name. I can manually login to each database and run \du to see the list of users present in that database, but that would take lot of time. So is there single line command or simple workaround which can display user list with their respective database name ?

Best Answer

Users are global to the Postgres instance (aka "cluster"). So there is no such thing as "their respective database name". \du will always show you the same list of users regardless of the database you are connected to.

If you want to get a list of databases a user is allowed to connect to, you can do that with this query:

select u.usename,
       (select string_agg(d.datname, ',' order by d.datname) 
        from pg_database d 
        where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_databases
from pg_user u
order by u.usename

To turn that query "around" and list the users that are allowed to connect to each database, you can use this:

select d.datname,
       (select string_agg(u.usename, ',' order by u.usename) 
        from pg_user u 
        where has_database_privilege(u.usename, d.datname, 'CONNECT')) as allowed_users
from pg_database d
order by d.datname;
Related Question