CODEWITHSUNDEEP
postgresqlpsql
mpapec
mpapec

Reputation: 50647

Obtaining sql permissions on views/tables/etc from command line

These are permissions for ie. my_view view

pwx1=# \dp my_view
                                    Access privileges
  Schema  |      Name       | Type |   Access privileges   | Column privileges | Policies
----------+-----------------+------+-----------------------+-------------------+----------
 schema_x | my_view         | view | user1=arwdDxt/user1  +|                   |
          |                 |      | user2=arwdDxt/user1  +|                   |
          |                 |      | user3=r/user1         |                   |

and I would like to get these grants in sql form like:

GRANT SELECT ON TABLE my_view TO user3;

I can get these grants using pgadmin, but want to do it from psql or command line if possible?

Upvotes: 0

Views: 332

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246523

You can get the ACL information from pg_class and use the aclexplode function for more pretty presentation:

SELECT acl.grantor::regrole AS grantor,
       acl.grantee::regrole AS grantee,
       privilege_type,
       is_grantable
FROM pg_catalog.pg_class AS t
   CROSS JOIN LATERAL aclexplode(t.relacl) AS acl
WHERE t.oid = 'schema_x.my_view'::regclass;

In the long run, it is a good idea to learn to read PostgreSQL ACLs.

Upvotes: 3

Related Questions