patternsqlMajor
postgres: list role grants for all users
Viewed 0 times
postgresallroleforgrantslistusers
Problem
I have an audit requirement to query all roles granted to users; listed by user.
For example:
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
For example:
Username Roles
user1 role1_rw, role2_ro, rol3_rw
user2 role2_rwI 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
But, if you want it, you can make a query on
Please, take a look at the documentation for more details:
https://www.postgresql.org/docs/current/static/user-manag.html
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.