How to check permissions, Grants and Default Privileges in PostgreSQL
Certifications:
- How to check permissions, Grants and Default Privileges in PostgreSQL - 31 August 2026
- How to Backup and Restore PostgreSQL using the pgBackrest tool - 28 August 2026
- How to Point in Time Restore a PostgreSQL database without using 3rd party tools - 24 August 2026
In managing a PostgreSQL database, access control is one of the most critical parts. Unlike other RDBMSs that have single system views for all permissions, PostgreSQL relies on System Catalogs and special functions.
If you're wondering who has access to which table ή what automatic rights will future paintings receive, the following two ready-made SQL queries will answer this question.
Check current permissions on tables, views and sequences
The following query shows you which users (who can log in) have active permissions (SELECT, INSERT, UPDATE, DELETE) on existing objects:
SELECT
r.rolname AS grantee,
n.nspname AS schema_name,
c.relname AS object_name,
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'S' THEN 'sequence'
ELSE c.relkind::text
END AS object_type,
pg_catalog.has_table_privilege(r.rolname, c.oid, 'SELECT') AS has_select,
pg_catalog.has_table_privilege(r.rolname, c.oid, 'INSERT') AS has_insert,
pg_catalog.has_table_privilege(r.rolname, c.oid, 'UPDATE') AS has_update,
pg_catalog.has_table_privilege(r.rolname, c.oid, 'DELETE') AS has_delete
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN pg_roles r
WHERE c.relkind IN ('r', 'v', 'S')
AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
AND r.rolcanlogin = true
AND (
pg_catalog.has_table_privilege(r.rolname, c.oid, 'SELECT') OR
pg_catalog.has_table_privilege(r.rolname, c.oid, 'INSERT') OR
pg_catalog.has_table_privilege(r.rolname, c.oid, 'UPDATE') OR
pg_catalog.has_table_privilege(r.rolname, c.oid, 'DELETE')
)
ORDER BY schema_name, object_name, grantee;
Check Default Privileges (For future tables)
A common problem in Postgres is that if you grant permissions to a user today, new tables created tomorrow by another user, e.g. postgres not will automatically inherit these permissions unless you have specified ALTER DEFAULT PRIVILEGES.
To see which Default Privileges rules are enabled on your database, run:
SELECT
defaclrole::regrole AS owner_role,
defaclnamespace::regnamespace AS schema_name,
CASE defaclobjtype
WHEN 'r' THEN 'table'
WHEN 'S' THEN 'sequence'
WHEN 'f' THEN 'function'
WHEN 'T' THEN 'type'
ELSE defaclobjtype::text
END AS object_type,
defaclacl AS granted_privileges
FROM pg_default_acl;
It shows us which owner_role has set up automatic permission granting rules for future objects per schema or overall in the database.
