Display default access privileges for relations, sequences and functions in Postgres

postgresql, privileges, sql

Solution

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.

Problem

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

Original source