postgres: list role grants for all users

Question

I have an audit requirement to query all roles granted to users; listed by user. For example:

Username Roles
user1 role1_rw, role2_ro, rol3_rw
user2 role2_rw

I cannnot use psql meta-commands as this result set will be merged with some other queries to do analysis of the grants. The roles have been created to manage ro/rw access to various application schemas. In oracle, this is quite straight forward; I dont see a single view to return this in Postgres. The querying user is a superuser.

Does anyone have a query to return this? Postgres 9.4 on ubuntu

• András Váczi: The psql meta-commands are backed by ordinary SQL queries. Just do a \set ECHO_HIDDEN on before running a \du, for example. It will show you the query being used to obtain the output.

Answer 1

You can see the users using the psql client with the option \du+(like @dezso already explained).

But, if you want it, you can make a query on pg_roles, eg:

 SELECT 
 r.rolname, 
 r.rolsuper, 
 r.rolinherit,
 r.rolcreaterole,
 r.rolcreatedb,
 r.rolcanlogin,
 r.rolconnlimit, r.rolvaliduntil,
 ARRAY(SELECT b.rolname
 FROM pg_catalog.pg_auth_members m
 JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
 WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
ORDER BY 1;

Please, take a look at the documentation for more details: https://www.postgresql.org/docs/current/static/user-manag.html

• RonJohn: What version does this require? (r.rolbypassrls does not exist in 9.2.7.)

Answer 2

+1 for Sebastian's answer.

I don't have the reputation to add a comment but I'd like to answer RonJohn's question as well. rolbypassrls was introduced in postgresql version 9.5. You can see this by comparing the pg_roles doc page between versions 9.4 and 9.5. (You may also notice the other difference is rolcatupdate was removed)

Here is Sebastion's snippet simply adjusted for the missing column:

SELECT 
 r.rolname, 
 r.rolsuper, 
 r.rolinherit,
 r.rolcreaterole,
 r.rolcreatedb,
 r.rolcanlogin,
 r.rolconnlimit, r.rolvaliduntil,
 ARRAY(SELECT b.rolname
 FROM pg_catalog.pg_auth_members m
 JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
 WHERE m.member = r.oid) as memberof
, r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;