After altering the default privileges on a Postgres database object, how can you view them?
For instance, if you grant all privileges to role_name
for all tables created in the schema schema_name
:
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT ALL ON TABLES TO role_name;
Advertisement
Answer
Using a SQL query
SELECT nspname, -- schema name defaclobjtype, -- object type defaclacl -- default access privileges FROM pg_default_acl a JOIN pg_namespace b ON a.defaclnamespace=b.oid;
Where the value of defaclobjtype
is r = relation (table, view), S = sequence, f = function.
These access privileges are only for newly created objects within the schema namespace.