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

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

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;