PostgreSQLSecurity & users

Who has which privileges in a schema

Table privileges in one schema by role, the default privileges that apply to tables created later, and which login roles are members of which group roles. Run it after granting access to check it took, or when a user gets "permission denied".

Not yet verified. How scripts are tested

pg-privileges-check.sql

1-- Replace <schema>. Run it in the database that holds the schema.2 3-- 1. Privileges on existing tables and views, one line per role and table4SELECT grantee, table_name,5       string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges6FROM information_schema.role_table_grants7WHERE table_schema = '<schema>'8GROUP BY grantee, table_name9ORDER BY grantee, table_name;10 11-- 2. Default privileges: what tables (r) and sequences (S) created later will get12SELECT pg_get_userbyid(d.defaclrole) AS when_created_by,13       d.defaclobjtype AS object_type,14       d.defaclacl AS grants15FROM pg_default_acl d16LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace17WHERE n.nspname = '<schema>' OR d.defaclnamespace = 018ORDER BY 1, 2;19 20-- 3. Schema access: USAGE is needed to see anything inside it21SELECT r.rolname,22       has_schema_privilege(r.oid, '<schema>', 'USAGE')  AS can_use,23       has_schema_privilege(r.oid, '<schema>', 'CREATE') AS can_create24FROM pg_roles r25WHERE r.rolname NOT LIKE 'pg\_%'26ORDER BY r.rolname;27 28-- 4. Group role membership29SELECT m.rolname AS member, g.rolname AS group_role30FROM pg_auth_members a31JOIN pg_roles m ON m.oid = a.member32JOIN pg_roles g ON g.oid = a.roleid33WHERE g.rolname NOT LIKE 'pg\_%'34ORDER BY 1, 2;

-- Replace <schema>. Run it in the database that holds the schema.

-- 1. Privileges on existing tables and views, one line per role and table
SELECT grantee, table_name,
       string_agg(privilege_type, ', ' ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE table_schema = '<schema>'
GROUP BY grantee, table_name
ORDER BY grantee, table_name;

-- 2. Default privileges: what tables (r) and sequences (S) created later will get
SELECT pg_get_userbyid(d.defaclrole) AS when_created_by,
       d.defaclobjtype AS object_type,
       d.defaclacl AS grants
FROM pg_default_acl d
LEFT JOIN pg_namespace n ON n.oid = d.defaclnamespace
WHERE n.nspname = '<schema>' OR d.defaclnamespace = 0
ORDER BY 1, 2;

-- 3. Schema access: USAGE is needed to see anything inside it
SELECT r.rolname,
       has_schema_privilege(r.oid, '<schema>', 'USAGE')  AS can_use,
       has_schema_privilege(r.oid, '<schema>', 'CREATE') AS can_create
FROM pg_roles r
WHERE r.rolname NOT LIKE 'pg\_%'
ORDER BY r.rolname;

-- 4. Group role membership
SELECT m.rolname AS member, g.rolname AS group_role
FROM pg_auth_members a
JOIN pg_roles m ON m.oid = a.member
JOIN pg_roles g ON g.oid = a.roleid
WHERE g.rolname NOT LIKE 'pg\_%'
ORDER BY 1, 2;

Paste it into your query tool.

Open in denrepo

Who has which privileges in a schema

More PostgreSQL scripts: Security & users