Re: Re: double check the role has what's kind of the privilege? And the same for the objects. Thanks.

leaf_yxj wrote:
> My bosses ask me to list
>
> 1)all the users and the roles associated with the users.

This will list all roles in the cluster, whether they can login
(are users) or not, and the array of all roles they are directly
or indirectly members of:

WITH RECURSIVE is_member_of(member, roleid) AS
(SELECT oid, oid
FROM pg_roles
UNION
SELECT m.member, r.roleid
FROM is_member_of m JOIN
pg_auth_members r ON (m.roleid = r.member))
SELECT u.rolname, u.rolcanlogin, array_agg(r.rolname) AS belongs_to
FROM is_member_of m JOIN
pg_roles u ON (m.member = u.oid) JOIN
pg_roles r ON (m.roleid = r.oid)
GROUP BY u.rolname, u.rolcanlogin;

> 2) all the users and the privileges associated with that users.

That is pretty difficult. You have to construct queries for each type
of object. The following query will show the desired information for all
privileges on tables, views and sequences (but not privileges granted on
columns of tables):

SELECT rolname, tablename, privilege_type, is_grantable
FROM (
SELECT r.rolname, r.oid, n.nspname || '.' || t.relname AS tablename,
(aclexplode(t.relacl)).grantee,
(aclexplode(t.relacl)).privilege_type,
(aclexplode(t.relacl)).is_grantable
FROM pg_shdepend shd JOIN
pg_roles r ON (shd.refobjid = r.oid) JOIN
pg_class t ON (shd.objid = t.oid) JOIN
pg_namespace n ON (t.relnamespace = n.oid)
WHERE shd.classid = 'pg_class'::regclass
AND shd.deptype='a'
AND objsubid = 0) AS q
WHERE oid = grantee;

You'd have to construct similar queries for all other objects for
which privileges can be granted: table columns, databases,
foreign data wrappers, foreign servers, functions, languages,
large objects, schemata.

I leave this as exercise for the reader.

Yours,
Laurenz Albe