From: | Juan José Santamaría Flecha <juanjo(dot)santamaria(at)gmail(dot)com> |
---|---|
To: | Ankush Chawla <ankushchawla03(at)gmail(dot)com> |
Cc: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: catalog views to check on grants |
Date: | 2020-04-16 13:39:40 |
Message-ID: | CAC+AXB1KNGHMuWs2-FOLjkSxacG=OmCsODDqA1Aomv2s=odenA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
On Thu, Apr 16, 2020 at 2:11 PM Ankush Chawla <ankushchawla03(at)gmail(dot)com>
wrote:
>
> Please share the dictionary tables to view the grants on objects, system
> and object privileges etc.
>
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
From | Date | Subject | |
---|---|---|---|
Next Message | Brandon Hsu | 2020-04-17 02:09:05 | Re: pgAdmin4 dependency package "python3-psycopg2" should be place in "pgdg-common" repo. |
Previous Message | Ankush Chawla | 2020-04-16 12:10:31 | catalog views to check on grants |