HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

postgres: list role grants for all users

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
postgresallroleforgrantslistusers

Problem

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

Solution

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

Code Snippets

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;

Context

StackExchange Database Administrators Q#145739, answer score: 21

Revisions (0)

No revisions yet.