Re: catalog views to check on grants - Mailing list pgsql-admin

Different objects have a different set of privileges, and there is not a single view for then all. You can check the system catalogs [1] for a specific object and check its privileges as an aclitem[] [2], e.g. for relations you can check pg_class grants using a query like so:

select relname,
       (select rolname from pg_roles where oid = grantor) as grantor,
       (select rolname from pg_roles where oid = grantee) as grantee,
       privilege_type,
       is_grantable
from (select relname,
       (aclexplode(relacl)).grantor,
       (aclexplode(relacl)).grantee,
       (aclexplode(relacl)).privilege_type,
       (aclexplode(relacl)).is_grantable
  from pg_class
  where relacl is not null) as pg_class_privs;

[1] https://www.postgresql.org/docs/current/catalogs.html
[2] https://www.postgresql.org/docs/current/ddl-priv.html#PRIVILEGE-ABBREVS-TABLE

Regards,

Juan José Santamaría Flecha