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?
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;