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