CODEWITHSUNDEEP
postgresqlpermissions
MozenRath
MozenRath

Reputation: 10030

Postgres permission issue

I have a schema owned by app_owner. I have run the following grants to another user:

GRANT USAGE ON SCHEMA app_schema TO app_writer;
ALTER DEFAULT PRIVILEGES IN SCHEMA app_schema
  GRANT ALL ON TABLES to app_writer;

How do I check (maybe after a month) if app_writer has all these privileges?

Upvotes: 0

Views: 59

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246013

You can simply query the metadata to check if all privileges are granted on all tables in a schema:

SELECT t.table_name,
       bool_and(
          has_table_privilege(
             'app_writer',
             format('%I.%I', t.table_schema, t.table_name),
             p.priv
          )
       ) AS has_all_privs
FROM information_schema.tables AS t
   CROSS JOIN (
      VALUES ('SELECT'), ('INSERT'), ('UPDATE'), ('DELETE'),
             ('TRUNCATE'), ('REFERENCES'), ('TRIGGER')
   ) AS p(priv)
WHERE t.table_schema = 'app_schema'
GROUP BY t.table_name;

Upvotes: 1

Related Questions