5

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

  • 4
    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. – dezso Aug 3 '16 at 12:25
7

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

  • 1
    What version does this require? (r.rolbypassrls does not exist in 9.2.7.) – RonJohn Mar 9 '18 at 22:33
4

+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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.