{"id":6084,"date":"2026-08-31T07:00:00","date_gmt":"2026-08-31T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=6084"},"modified":"2026-08-29T14:30:31","modified_gmt":"2026-08-29T11:30:31","slug":"pos-elegchoyme-ta-dikaiomata-grants-default-privileges-s","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-elegchoyme-ta-dikaiomata-grants-default-privileges-s\/","title":{"rendered":"How to check permissions, Grants and Default Privileges in PostgreSQL"},"content":{"rendered":"

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.<\/p>\n\n\n\n

If you're wondering who has access to which table<\/strong> the what automatic rights will future paintings receive<\/strong>, the following two ready-made SQL queries will answer this question.<\/p>\n\n\n\n

Check current permissions on tables, views and sequences<\/h2>\n\n\n\n

The following query shows you which users (who can log in) have active permissions (SELECT<\/code>, INSERT<\/code>, UPDATE<\/code>, DELETE<\/code>) on existing objects:<\/p>\n\n\n\n

SELECT \n    r.rolname AS grantee,\n    n.nspname AS schema_name,\n    c.relname AS object_name,\n    CASE c.relkind \n        WHEN 'r' THEN 'table' \n        WHEN 'v' THEN 'view' \n        WHEN 'S' THEN 'sequence' \n        ELSE c.relkind::text \n    END AS object_type,\n    pg_catalog.has_table_privilege(r.rolname, c.oid, 'SELECT') AS has_select,\n    pg_catalog.has_table_privilege(r.rolname, c.oid, 'INSERT') AS has_insert,\n    pg_catalog.has_table_privilege(r.rolname, c.oid, 'UPDATE') AS has_update,\n    pg_catalog.has_table_privilege(r.rolname, c.oid, 'DELETE') AS has_delete\nFROM pg_class c\nJOIN pg_namespace n ON n.oid = c.relnamespace\nCROSS JOIN pg_roles r\nWHERE c.relkind IN ('r', 'v', 'S')\n  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')\n  AND r.rolcanlogin = true\n  AND (\n       pg_catalog.has_table_privilege(r.rolname, c.oid, 'SELECT') OR\n       pg_catalog.has_table_privilege(r.rolname, c.oid, 'INSERT') OR\n       pg_catalog.has_table_privilege(r.rolname, c.oid, 'UPDATE') OR\n       pg_catalog.has_table_privilege(r.rolname, c.oid, 'DELETE')\n  )\nORDER BY schema_name, object_name, grantee;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Check Default Privileges (For future tables)<\/h2>\n\n\n\n
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<\/code> not<\/strong> will automatically inherit these permissions unless you have specified ALTER DEFAULT PRIVILEGES<\/code>.<\/p>\n\n\n\n
To see which Default Privileges rules are enabled on your database, run:<\/p>\n\n\n\n
SELECT \n    defaclrole::regrole AS owner_role,\n    defaclnamespace::regnamespace AS schema_name,\n    CASE defaclobjtype \n        WHEN 'r' THEN 'table'\n        WHEN 'S' THEN 'sequence'\n        WHEN 'f' THEN 'function'\n        WHEN 'T' THEN 'type'\n        ELSE defaclobjtype::text \n    END AS object_type,\n    defaclacl AS granted_privileges\nFROM pg_default_acl;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
It shows us which owner_role has set up automatic permission granting rules for future objects per schema or overall in the database.<\/p>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n