PostgreSQL: Show all the privileges for a concrete user

Question

How to make a query to the Postgres data dictionary to find out all the privileges that a particular user has.

I've been looking for a solution and I can not find anything. Thanks and good day

Answer 1

Answer from 2k24: there is \dp concrete_TABLE - but it works "other way around" - get's you privileges by table (view, sequence), not by user:

\dp     [PATTERN]      list table, view, and sequence access privileges

Usage:

=> \dp some_things
                                       Access privileges
 Schema |    Name     | Type  |        Access privileges  | Column privileges | Policies
--------+-------------+-------+---------------------------+-------------------+----------
 public | some_things | table | dba=arwdDxt/main-db      +|                   |
        |             |       | metabase=r/main-db       +|                   |
        |             |       | jane_dou=arwdDxt/main-db  |                   |
(1 row)

Answer 2

Below are queries that return the direct privileges a role/user has been granted - and by that I mean:

• GRANTs on/OWNERships of databases, tablespaces, parameters, schemas, tables (including the table-like views, materialized views, partitioned tables and foreign tables), table(-like) columns, sequences, functions, procedures, large objects, types (base, composite, enum, pseudo, range and multirange), domains, languages, foreign-data wrappers, and foreign servers

  (Note there is no concept of OWNERnership of parameters, nor of table(-like) columns independent from each column's table)

• GRANTs of membership to other roles

• The ability to login, create roles, create databases, and if they're a superuser

• But not including anything granted to the PUBLIC role

Notes:

• Because only direct privileges are returned, any privileges inherited from role membership are not included.
• Depending on the query, on a database with quite large catalog tables (at least for my world), for me they take between ~10ms and ~20ms.
• The privilege_type column contains PostgreSQL-known privilege types, with the addition of MEMBER, OWNER, LOGIN, SUPERUSER, CREATE ROLE and CREATE DATABASE
• Login, role-creation, database-creation and superuser status is cluster-wide - i.e. independant of the currently connected database
• Role memberships and permissions on databases, tablespaces and parameters are also cluster-wide
• Everything else returned is only for the currently connected database.
• Parameters and table(-like) columns have no concept of ownership.
• For permissions stored with the role, it uses pg_roles. For role membership, it uses pg_auth_members. And for everything else, it goes via pg_shdepend to determine OWNERship, and lead to the pg_* catalog table in question that stores ACL-permissions. While this makes the query long, I think by going via pg_shdepend it can be more efficient when there are many rows in the catalog tables - avoiding full table scans of pg_class for example when it's very big.

Example output:

          on          |                  name                   | privilege_type
----------------------+-----------------------------------------+----------------
 cluster              |                                         | LOGIN
 role                 | other_role                              | MEMBER
 database             | new_database                            | OWNER
 database             | postgres                                | CONNECT
 schema               | my_schema                               | USAGE
 table                | my_schema.my_table                      | SELECT
 sequence             | my_schema.my_sequence                   | USAGE
 table column         | my_schema.my_table.id                   | SELECT
 table column         | my_view.id                              | SELECT
 function             | prod(double precision,double precision) | OWNER
 aggregate function   | incremented_max(integer)                | EXECUTE
 large object         | 175076                                  | SELECT
 composite type       | my_schema.compfoo                       | OWNER
 domain               | my_schema.mydom                         | OWNER
 composite type       | my_schema.compfoo                       | USAGE
 pseudo type          | "any"                                   | USAGE
 language             | sql                                     | OWNER
 language             | sql                                     | USAGE
 tablespace           | my_new_tablespace                       | OWNER
 foreign-data wrapper | dummy                                   | USAGE
 foreign server       | myserver                                | OWNER
 parameter            | max_connections                         | SET

And the query is:

-- Cluster permissions not "on" anything else
SELECT
  'cluster' AS on,
  NULL AS name,
  unnest(
    CASE WHEN rolcanlogin THEN ARRAY['LOGIN'] ELSE ARRAY[]::text[] END
    || CASE WHEN rolsuper THEN ARRAY['SUPERUSER'] ELSE ARRAY[]::text[] END
    || CASE WHEN rolcreaterole THEN ARRAY['CREATE ROLE'] ELSE ARRAY[]::text[] END
    || CASE WHEN rolcreatedb THEN ARRAY['CREATE DATABASE'] ELSE ARRAY[]::text[] END
  ) AS privilege_type
FROM pg_roles
WHERE oid = quote_ident('YOUR_ROLE')::regrole

UNION ALL

-- Direct role memberships
SELECT 'role' AS on, groups.rolname AS name, 'MEMBER' AS privilege_type
FROM pg_auth_members mg
INNER JOIN pg_roles groups ON groups.oid = mg.roleid
INNER JOIN pg_roles members ON members.oid = mg.member
WHERE members.rolname = 'YOUR_ROLE'

-- Direct ACL or ownerships
UNION ALL (
  -- ACL or owned-by dependencies of the role - global or in the currently connected database
  WITH owned_or_acl AS (
    SELECT
      refobjid,  -- The referenced object: the role in this case
      classid,   -- The pg_class oid that the dependant object is in
      objid,     -- The oid of the dependant object in the table specified by classid
      deptype,   -- The dependency type: o==is owner, and might have acl, a==has acl and not owner
      objsubid   -- The 1-indexed column index for table column permissions. 0 otherwise.
    FROM pg_shdepend
    WHERE refobjid = quote_ident('YOUR_ROLE')::regrole
    AND refclassid='pg_catalog.pg_authid'::regclass
    AND deptype IN ('a', 'o')
    AND (dbid = 0 OR dbid = (SELECT oid FROM pg_database WHERE datname = current_database()))
  ),

  relkind_mapping(relkind, type) AS (
    VALUES 
      ('r', 'table'),
      ('v', 'view'),
      ('m', 'materialized view'),
      ('f', 'foreign table'),
      ('p', 'partitioned table'),
      ('S', 'sequence')
  ),

  prokind_mapping(prokind, type) AS (
    VALUES 
      ('f', 'function'),
      ('p', 'procedure'),
      ('a', 'aggregate function'),
      ('w', 'window function')
  ),

  typtype_mapping(typtype, type) AS (
    VALUES
      ('b', 'base type'),
      ('c', 'composite type'),
      ('e', 'enum type'),
      ('p', 'pseudo type'),
      ('r', 'range type'),
      ('m', 'multirange type'),
      ('d', 'domain')
  )

  -- Database ownership
  SELECT 'database' AS on, quote_ident(datname) AS name, 'OWNER' AS privilege_type
  FROM pg_database d
  INNER JOIN owned_or_acl a ON a.objid = d.oid 
  WHERE classid = 'pg_database'::regclass AND deptype = 'o'

  UNION ALL

  -- Database privileges
  SELECT 'database' AS on, quote_ident(datname) AS name, privilege_type
  FROM pg_database d
  INNER JOIN owned_or_acl a ON a.objid = d.oid 
  CROSS JOIN aclexplode(COALESCE(d.datacl, acldefault('d', d.datdba)))
  WHERE classid = 'pg_database'::regclass AND grantee = refobjid

  UNION ALL

  -- Schema ownership
  SELECT 'schema' AS on, n.oid::regnamespace::text AS name, 'OWNER' AS privilege_type
  FROM pg_namespace n
  INNER JOIN owned_or_acl a ON a.objid = n.oid 
  WHERE classid = 'pg_namespace'::regclass AND deptype = 'o'

  UNION ALL

  -- Schema privileges
  SELECT 'schema' AS on, n.oid::regnamespace::text AS name, privilege_type
  FROM pg_namespace n
  INNER JOIN owned_or_acl a ON a.objid = n.oid
  CROSS JOIN aclexplode(COALESCE(n.nspacl, acldefault('n', n.nspowner)))
  WHERE classid = 'pg_namespace'::regclass AND grantee = refobjid

  UNION ALL

  -- Table(-like) ownership
  SELECT r.type AS on, c.oid::regclass::text AS name, 'OWNER' AS privilege_type
  FROM pg_class c
  INNER JOIN owned_or_acl a ON a.objid = c.oid 
  INNER JOIN relkind_mapping r ON r.relkind = c.relkind
  WHERE classid = 'pg_class'::regclass AND deptype = 'o' AND objsubid = 0

  UNION ALL

  -- Table(-like) privileges
  SELECT r.type AS on, c.oid::regclass::text AS name, privilege_type
  FROM pg_class c
  INNER JOIN owned_or_acl a ON a.objid = c.oid
  CROSS JOIN aclexplode(COALESCE(c.relacl, acldefault('r', c.relowner)))
  INNER JOIN relkind_mapping r ON r.relkind = c.relkind
  WHERE classid = 'pg_class'::regclass AND grantee = refobjid AND objsubid = 0

  UNION ALL

  -- Column privileges
  SELECT 'table column', t.attrelid::regclass::text || '.' || quote_ident(attname) AS name, privilege_type
  FROM pg_attribute t
  INNER JOIN pg_class c ON c.oid = t.attrelid
  INNER JOIN owned_or_acl a ON a.objid = t.attrelid
  CROSS JOIN aclexplode(COALESCE(t.attacl, acldefault('c', c.relowner)))
  WHERE classid = 'pg_class'::regclass AND grantee = refobjid AND objsubid != 0

  UNION ALL

  -- Function and procdedure ownership
  SELECT m.type AS on, p.oid::regprocedure::text AS name, 'OWNER' AS privilege_type
  FROM pg_proc p
  INNER JOIN owned_or_acl a ON a.objid = p.oid 
  INNER JOIN prokind_mapping m ON m.prokind = p.prokind
  WHERE classid = 'pg_proc'::regclass AND deptype = 'o'

  UNION ALL

  -- Function and procedure privileges
  SELECT m.type AS on, p.oid::regprocedure::text AS name, privilege_type
  FROM pg_proc p
  INNER JOIN owned_or_acl a ON a.objid = p.oid
  CROSS JOIN aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner)))
  INNER JOIN prokind_mapping m ON m.prokind = p.prokind
  WHERE classid = 'pg_proc'::regclass AND grantee = refobjid

  UNION ALL

  -- Large object ownership
  SELECT 'large object' AS on, l.oid::text AS name, 'OWNER' AS privilege_type
  FROM pg_largeobject_metadata l
  INNER JOIN owned_or_acl a ON a.objid = l.oid 
  WHERE classid = 'pg_largeobject'::regclass AND deptype = 'o'

  UNION ALL

  -- Large object privileges
  SELECT 'large object' AS on, l.oid::text AS name, privilege_type
  FROM pg_largeobject_metadata l
  INNER JOIN owned_or_acl a ON a.objid = l.oid
  CROSS JOIN aclexplode(COALESCE(l.lomacl, acldefault('L', l.lomowner)))
  WHERE classid = 'pg_largeobject'::regclass AND grantee = refobjid

  UNION ALL

  -- Type ownership
  SELECT m.type, t.oid::regtype::text AS name, 'OWNER' AS privilege_type
  FROM pg_type t
  INNER JOIN owned_or_acl a ON a.objid = t.oid 
  INNER JOIN typtype_mapping m ON m.typtype = t.typtype
  WHERE classid = 'pg_type'::regclass AND deptype = 'o'

  UNION ALL

  -- Type privileges
  SELECT m.type, t.oid::regtype::text AS name, privilege_type
  FROM pg_type t
  INNER JOIN owned_or_acl a ON a.objid = t.oid
  CROSS JOIN aclexplode(COALESCE(t.typacl, acldefault('T', t.typowner)))
  INNER JOIN typtype_mapping m ON m.typtype = t.typtype
  WHERE classid = 'pg_type'::regclass AND grantee = refobjid

  UNION ALL

  -- Language ownership
  SELECT 'language' AS on, quote_ident(l.lanname) AS name, 'OWNER' AS privilege_type
  FROM pg_language l
  INNER JOIN owned_or_acl a ON a.objid = l.oid 
  WHERE classid = 'pg_language'::regclass AND deptype = 'o'

  UNION ALL

  -- Language privileges
  SELECT 'language' AS on, quote_ident(l.lanname) AS name, privilege_type
  FROM pg_language l
  INNER JOIN owned_or_acl a ON a.objid = l.oid
  CROSS JOIN aclexplode(COALESCE(l.lanacl, acldefault('l', l.lanowner)))
  WHERE classid = 'pg_language'::regclass AND grantee = refobjid

  UNION ALL

  -- Tablespace ownership
  SELECT 'tablespace' AS on, quote_ident(t.spcname) AS name, 'OWNER' AS privilege_type
  FROM pg_tablespace t
  INNER JOIN owned_or_acl a ON a.objid = t.oid 
  WHERE classid = 'pg_tablespace'::regclass AND deptype = 'o'

  UNION ALL

  -- Tablespace privileges
  SELECT 'tablespace' AS on, quote_ident(t.spcname) AS name, privilege_type
  FROM pg_tablespace t
  INNER JOIN owned_or_acl a ON a.objid = t.oid
  CROSS JOIN aclexplode(COALESCE(t.spcacl, acldefault('t', t.spcowner)))
  WHERE classid = 'pg_tablespace'::regclass AND grantee = refobjid

  UNION ALL

  -- Foreign data wrapper ownership
  SELECT 'foreign-data wrapper' AS on, quote_ident(f.fdwname) AS name, 'OWNER' AS privilege_type
  FROM pg_foreign_data_wrapper f
  INNER JOIN owned_or_acl a ON a.objid = f.oid 
  WHERE classid = 'pg_foreign_data_wrapper'::regclass AND deptype = 'o'

  UNION ALL

  -- Foreign data wrapper privileges
  SELECT 'foreign-data wrapper' AS on, quote_ident(f.fdwname) AS name, privilege_type
  FROM pg_foreign_data_wrapper f
  INNER JOIN owned_or_acl a ON a.objid = f.oid
  CROSS JOIN aclexplode(COALESCE(f.fdwacl, acldefault('F', f.fdwowner)))
  WHERE classid = 'pg_foreign_data_wrapper'::regclass AND grantee = refobjid

  UNION ALL

  -- Foreign server ownership
  SELECT 'foreign server' AS on, quote_ident(f.srvname) AS name, 'OWNER' AS privilege_type
  FROM pg_foreign_server f
  INNER JOIN owned_or_acl a ON a.objid = f.oid 
  WHERE classid = 'pg_foreign_server'::regclass AND deptype = 'o'

  UNION ALL

  -- Foreign server privileges
  SELECT 'foreign server' AS on, quote_ident(f.srvname) AS name, privilege_type
  FROM pg_foreign_server f
  INNER JOIN owned_or_acl a ON a.objid = f.oid
  CROSS JOIN aclexplode(COALESCE(f.srvacl, acldefault('S', f.srvowner)))
  WHERE classid = 'pg_foreign_server'::regclass AND grantee = refobjid

  UNION ALL

  -- Parameter privileges
  SELECT 'parameter' AS on, quote_ident(p.parname) AS name, privilege_type
  FROM pg_parameter_acl p
  INNER JOIN owned_or_acl a ON a.objid = p.oid
  CROSS JOIN aclexplode(p.paracl)
  WHERE classid = 'pg_parameter_acl'::regclass AND grantee = refobjid
);

Or if you want slightly more structured output that can more easily be used (once safely quoted/escaped) in further SQL to manage permisisons:

          on          |      name_1       |     name_2      | name_3  | privilege_type
----------------------+-------------------+-----------------+---------+----------------
 cluster              |                   |                 |         | LOGIN
 role                 | other_role        |                 |         | MEMBER
 database             | new_database      |                 |         | OWNER
 database             | postgres          |                 |         | CONNECT
 schema               | my_schema         |                 |         | USAGE
 table                | my_schema         | my_table        |         | SELECT
 sequence             | my_schema         | my_sequence     |         | USAGE
 table column         | my_schema         | my_table        | id      | SELECT
 table column         | public            | my_view         | id      | SELECT
 function             | public            | prod            | 32754   | OWNER
 aggregate function   | public            | incremented_max | integer | EXECUTE
 large object         | 175076            |                 |         | SELECT
 composite type       | my_schema         | compfoo         |         | OWNER
 domain               | my_schema         | mydom           |         | OWNER
 composite type       | my_schema         | compfoo         |         | USAGE
 pseudo type          | pg_catalog        | any             |         | USAGE
 language             | sql               |                 |         | OWNER
 language             | sql               |                 |         | USAGE
 tablespace           | my_new_tablespace |                 |         | OWNER
 foreign-data wrapper | dummy             |                 |         | USAGE
 foreign server       | myserver          |                 |         | OWNER
 parameter            | max_connections   |                 |         | SET

You can use the more complex (and a touch slower) query:

-- Cluster permissions not "on" anything else
SELECT
  'cluster' AS on,
  NULL AS name_1,
  NULL AS name_2,
  NULL AS name_3,
  unnest(
    CASE WHEN rolcanlogin THEN ARRAY['LOGIN'] ELSE ARRAY[]::text[] END
    || CASE WHEN rolsuper THEN ARRAY['SUPERUSER'] ELSE ARRAY[]::text[] END
    || CASE WHEN rolcreaterole THEN ARRAY['CREATE ROLE'] ELSE ARRAY[]::text[] END
    || CASE WHEN rolcreatedb THEN ARRAY['CREATE DATABASE'] ELSE ARRAY[]::text[] END
  ) AS privilege_type
FROM pg_roles
WHERE oid = quote_ident('manual_test')::regrole

UNION ALL

-- Direct role memberships
SELECT 'role' AS on, groups.rolname AS name_1, NULL AS name_2, NULL AS name_3, 'MEMBER' AS privilege_type
FROM pg_auth_members mg
INNER JOIN pg_roles groups ON groups.oid = mg.roleid
INNER JOIN pg_roles members ON members.oid = mg.member
WHERE members.rolname = 'manual_test'

-- Direct ACL or ownerships
UNION ALL (
  -- ACL or owned-by dependencies of the role - global or in the currently connected database
  WITH owned_or_acl AS (
    SELECT
      refobjid,  -- The referenced object: the role in this case
      classid,   -- The pg_class oid that the dependant object is in
      objid,     -- The oid of the dependant object in the table specified by classid
      deptype,   -- The dependency type: o==is owner, and might have acl, a==has acl and not owner
      objsubid   -- The 1-indexed column index for table column permissions. 0 otherwise.
    FROM pg_shdepend
    WHERE refobjid = quote_ident('manual_test')::regrole
    AND refclassid='pg_catalog.pg_authid'::regclass
    AND deptype IN ('a', 'o')
    AND (dbid = 0 OR dbid = (SELECT oid FROM pg_database WHERE datname = current_database()))
  ),

  relkind_mapping(relkind, type) AS (
    VALUES 
      ('r', 'table'),
      ('v', 'view'),
      ('m', 'materialized view'),
      ('f', 'foreign table'),
      ('p', 'partitioned table'),
      ('S', 'sequence')
  ),

  prokind_mapping(prokind, type) AS (
    VALUES 
      ('f', 'function'),
      ('p', 'procedure'),
      ('a', 'aggregate function'),
      ('w', 'window function')
  ),

  typtype_mapping(typtype, type) AS (
    VALUES
      ('b', 'base type'),
      ('c', 'composite type'),
      ('e', 'enum type'),
      ('p', 'pseudo type'),
      ('r', 'range type'),
      ('m', 'multirange type'),
      ('d', 'domain')
  )

  -- Database ownership
  SELECT 'database' AS on, datname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
  FROM pg_database d
  INNER JOIN owned_or_acl a ON a.objid = d.oid 
  WHERE classid = 'pg_database'::regclass AND deptype = 'o'

  UNION ALL

  -- Database privileges
  SELECT 'database' AS on, datname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
  FROM pg_database d
  INNER JOIN owned_or_acl a ON a.objid = d.oid 
  CROSS JOIN aclexplode(COALESCE(d.datacl, acldefault('d', d.datdba)))
  WHERE classid = 'pg_database'::regclass AND grantee = refobjid

  UNION ALL

  -- Schema ownership
  SELECT 'schema' AS on, nspname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
  FROM pg_namespace n
  INNER JOIN owned_or_acl a ON a.objid = n.oid 
  WHERE classid = 'pg_namespace'::regclass AND deptype = 'o'

  UNION ALL

  -- Schema privileges
  SELECT 'schema' AS on, nspname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
  FROM pg_namespace n
  INNER JOIN owned_or_acl a ON a.objid = n.oid
  CROSS JOIN aclexplode(COALESCE(n.nspacl, acldefault('n', n.nspowner)))
  WHERE classid = 'pg_namespace'::regclass AND grantee = refobjid

  UNION ALL

  -- Table(-like) ownership
  SELECT r.type AS on, nspname AS name_1, relname AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
  FROM pg_class c
  INNER JOIN pg_namespace n ON n.oid = c.relnamespace
  INNER JOIN owned_or_acl a ON a.objid = c.oid 
  INNER JOIN relkind_mapping r ON r.relkind = c.relkind
  WHERE classid = 'pg_class'::regclass AND deptype = 'o' AND objsubid = 0

  UNION ALL

  -- Table(-like) privileges
  SELECT r.type AS on, nspname AS name_1, relname AS name_2, NULL AS name_3, privilege_type
  FROM pg_class c
  INNER JOIN pg_namespace n ON n.oid = c.relnamespace
  INNER JOIN owned_or_acl a ON a.objid = c.oid
  CROSS JOIN aclexplode(COALESCE(c.relacl, acldefault('r', c.relowner)))
  INNER JOIN relkind_mapping r ON r.relkind = c.relkind
  WHERE classid = 'pg_class'::regclass AND grantee = refobjid AND objsubid = 0

  UNION ALL

  -- Column privileges
  SELECT 'table column', nspname AS name_1, relname AS name_2, attname AS name_3, privilege_type
  FROM pg_attribute t
  INNER JOIN pg_class c ON c.oid = t.attrelid
  INNER JOIN pg_namespace n ON n.oid = c.relnamespace
  INNER JOIN owned_or_acl a ON a.objid = t.attrelid
  CROSS JOIN aclexplode(COALESCE(t.attacl, acldefault('c', c.relowner)))
  WHERE classid = 'pg_class'::regclass AND grantee = refobjid AND objsubid != 0

  UNION ALL

  -- Function and procdedure ownership
  SELECT m.type AS on, nspname AS name_1, proname AS name_2, p.oid::text AS name_3, 'OWNER' AS privilege_type
  FROM pg_proc p
  INNER JOIN pg_namespace n ON n.oid = p.pronamespace
  INNER JOIN owned_or_acl a ON a.objid = p.oid 
  INNER JOIN prokind_mapping m ON m.prokind = p.prokind
  WHERE classid = 'pg_proc'::regclass AND deptype = 'o'

  UNION ALL

  -- Function and procedure privileges
  SELECT m.type AS on, nspname AS name_1, proname AS name_2, p.oid::text AS name_3, privilege_type
  FROM pg_proc p
  INNER JOIN pg_namespace n ON n.oid = p.pronamespace
  INNER JOIN owned_or_acl a ON a.objid = p.oid
  CROSS JOIN aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner)))
  INNER JOIN prokind_mapping m ON m.prokind = p.prokind
  WHERE classid = 'pg_proc'::regclass AND grantee = refobjid

  UNION ALL

  -- Large object ownership
  SELECT 'large object' AS on, l.oid::text AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
  FROM pg_largeobject_metadata l
  INNER JOIN owned_or_acl a ON a.objid = l.oid 
  WHERE classid = 'pg_largeobject'::regclass AND deptype = 'o'

  UNION ALL

  -- Large object privileges
  SELECT 'large object' AS on, l.oid::text AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
  FROM pg_largeobject_metadata l
  INNER JOIN owned_or_acl a ON a.objid = l.oid
  CROSS JOIN aclexplode(COALESCE(l.lomacl, acldefault('L', l.lomowner)))
  WHERE classid = 'pg_largeobject'::regclass AND grantee = refobjid

  UNION ALL

  -- Type ownership
  SELECT m.type, nspname AS name_1, typname AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
  FROM pg_type t
  INNER JOIN pg_namespace n ON n.oid = t.typnamespace
  INNER JOIN owned_or_acl a ON a.objid = t.oid 
  INNER JOIN typtype_mapping m ON m.typtype = t.typtype
  WHERE classid = 'pg_type'::regclass AND deptype = 'o'

  UNION ALL

  -- Type privileges
  SELECT m.type, nspname AS name_1, typname AS name_2, NULL AS name_3, privilege_type
  FROM pg_type t
  INNER JOIN pg_namespace n ON n.oid = t.typnamespace
  INNER JOIN owned_or_acl a ON a.objid = t.oid
  CROSS JOIN aclexplode(COALESCE(t.typacl, acldefault('T', t.typowner)))
  INNER JOIN typtype_mapping m ON m.typtype = t.typtype
  WHERE classid = 'pg_type'::regclass AND grantee = refobjid

  UNION ALL

  -- Language ownership
  SELECT 'language' AS on, l.lanname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
  FROM pg_language l
  INNER JOIN owned_or_acl a ON a.objid = l.oid 
  WHERE classid = 'pg_language'::regclass AND deptype = 'o'

  UNION ALL

  -- Language privileges
  SELECT 'language' AS on, l.lanname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
  FROM pg_language l
  INNER JOIN owned_or_acl a ON a.objid = l.oid
  CROSS JOIN aclexplode(COALESCE(l.lanacl, acldefault('l', l.lanowner)))
  WHERE classid = 'pg_language'::regclass AND grantee = refobjid

  UNION ALL

  -- Tablespace ownership
  SELECT 'tablespace' AS on, t.spcname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
  FROM pg_tablespace t
  INNER JOIN owned_or_acl a ON a.objid = t.oid 
  WHERE classid = 'pg_tablespace'::regclass AND deptype = 'o'

  UNION ALL

  -- Tablespace privileges
  SELECT 'tablespace' AS on, t.spcname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
  FROM pg_tablespace t
  INNER JOIN owned_or_acl a ON a.objid = t.oid
  CROSS JOIN aclexplode(COALESCE(t.spcacl, acldefault('t', t.spcowner)))
  WHERE classid = 'pg_tablespace'::regclass AND grantee = refobjid

  UNION ALL

  -- Foreign data wrapper ownership
  SELECT 'foreign-data wrapper' AS on, f.fdwname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
  FROM pg_foreign_data_wrapper f
  INNER JOIN owned_or_acl a ON a.objid = f.oid 
  WHERE classid = 'pg_foreign_data_wrapper'::regclass AND deptype = 'o'

  UNION ALL

  -- Foreign data wrapper privileges
  SELECT 'foreign-data wrapper' AS on, f.fdwname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
  FROM pg_foreign_data_wrapper f
  INNER JOIN owned_or_acl a ON a.objid = f.oid
  CROSS JOIN aclexplode(COALESCE(f.fdwacl, acldefault('F', f.fdwowner)))
  WHERE classid = 'pg_foreign_data_wrapper'::regclass AND grantee = refobjid

  UNION ALL

  -- Foreign server ownership
  SELECT 'foreign server' AS on, f.srvname AS name_1, NULL AS name_2, NULL AS name_3, 'OWNER' AS privilege_type
  FROM pg_foreign_server f
  INNER JOIN owned_or_acl a ON a.objid = f.oid 
  WHERE classid = 'pg_foreign_server'::regclass AND deptype = 'o'

  UNION ALL

  -- Foreign server privileges
  SELECT 'foreign server' AS on, f.srvname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
  FROM pg_foreign_server f
  INNER JOIN owned_or_acl a ON a.objid = f.oid
  CROSS JOIN aclexplode(COALESCE(f.srvacl, acldefault('S', f.srvowner)))
  WHERE classid = 'pg_foreign_server'::regclass AND grantee = refobjid

  UNION ALL

  -- Parameter privileges
  SELECT 'parameter' AS on, p.parname AS name_1, NULL AS name_2, NULL AS name_3, privilege_type
  FROM pg_parameter_acl p
  INNER JOIN owned_or_acl a ON a.objid = p.oid
  CROSS JOIN aclexplode(p.paracl)
  WHERE classid = 'pg_parameter_acl'::regclass AND grantee = refobjid
);

Answer 3

You can use this script to retrieve all priviliges for all PostgreSQL roles (server level, db ownership, object ownership, object permission, schema permissions). Add filters to get privileges for a specific user

WITH server_permissions AS (
        SELECT 
            r.rolname, 
            'Server_Permissions' AS "Level", 
            r.rolsuper, 
            r.rolinherit,
            r.rolcreaterole, 
            r.rolcreatedb, 
            r.rolcanlogin,
            ARRAY(
                SELECT b.rolname
                FROM pg_catalog.pg_auth_members m
                JOIN pg_catalog.pg_roles b ON m.roleid = b.oid
                WHERE m.member = r.oid
            ) AS memberof,
            r.rolbypassrls
        FROM pg_catalog.pg_roles r
        WHERE r.rolname !~ '^pg_'
    ),
    
    db_ownership AS (
        SELECT 
            r.rolname, 
            'DB_Ownership' AS "Level", 
            d.datname
        FROM pg_catalog.pg_database d, pg_catalog.pg_roles r
        WHERE d.datdba = r.oid
    ),
    
    schema_permissions AS (
        SELECT
            'Schema Permissions' AS "Level",                
            r.rolname AS role_name,
            nspname AS schema_name,
            pg_catalog.has_schema_privilege(r.rolname, nspname, 'CREATE') AS create_grant,
            pg_catalog.has_schema_privilege(r.rolname, nspname, 'USAGE') AS usage_grant
        FROM pg_namespace pn, pg_catalog.pg_roles r
        WHERE array_to_string(nspacl, ',') LIKE '%' || r.rolname || '%' 
              AND nspowner > 1
    ),
    
    table_ownership AS (
        SELECT 
            'Table Ownership' AS "Level",
            tableowner, 
            schemaname, 
            tablename
        FROM pg_tables
        GROUP BY tableowner, schemaname, tablename
    ),
    
    object_permissions AS (
        SELECT  
            'Object Permissions' AS "Level",
            COALESCE(NULLIF(s[1], ''), 'public') AS rolname,
            n.nspname,
            relname, 
            CASE 
                WHEN relkind = 'm' THEN 'Materialized View'
                WHEN relkind = 'p' THEN 'Partitioned Table'
                WHEN relkind = 'S' THEN 'Sequence'
                WHEN relkind = 'I' THEN 'Partitioned Index'
                WHEN relkind = 'v' THEN 'View'
                WHEN relkind = 'i' THEN 'Index'
                WHEN relkind = 'c' THEN 'Composite Type'
                WHEN relkind = 't' THEN 'TOAST table'
                WHEN relkind = 'r' THEN 'Table'
                WHEN relkind = 'f' THEN 'Foreign Table'
            END AS "Object Type",
            s[2] AS privileges
        FROM 
            pg_class c
            JOIN pg_namespace n ON n.oid = relnamespace
            JOIN pg_roles r ON r.oid = relowner,
            UNNEST(COALESCE(relacl::text[], FORMAT('{%s=arwdDxt/%s}', rolname, rolname)::text[])) acl, 
            REGEXP_SPLIT_TO_ARRAY(acl, '=|/') s 
        WHERE relkind <> 'i' AND relkind <> 't'
    )   
    
    SELECT 
        "Level", 
        rolname AS "Role", 
        'N/A' AS "Object Name", 
        'N/A' AS "Schema Name", 
        'N/A' AS "DB Name", 
        'N/A' AS "Object Type", 
        'N/A' AS "Privileges", 
        rolsuper::text AS "Is SuperUser", 
        rolinherit::text,
        rolcreaterole::text, 
        rolcreatedb::text, 
        rolcanlogin::text,
        memberof::text,
        rolbypassrls::text 
    FROM server_permissions
    
    UNION
    
    SELECT 
        dow."Level", 
        dow.rolname,
        'N/A',  
        'N/A', 
        datname,
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM db_ownership AS dow 
    
    UNION
    
    SELECT
        "Level", 
        role_name, 
        'N/A', 
        schema_name, 
        'N/A', 
        'N/A',
        CASE 
            WHEN create_grant IS TRUE AND usage_grant IS TRUE THEN 'Usage+Create' 
            WHEN create_grant IS TRUE AND usage_grant IS FALSE THEN 'Create' 
            WHEN create_grant IS FALSE AND usage_grant IS TRUE THEN 'Usage' 
            ELSE 'None' 
        END, 
        'N/A', 
        'N/A', 
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM schema_permissions
    
    UNION
    
    SELECT 
        "Level", 
        tableowner, 
        tablename, 
        schemaname,
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM table_ownership
    
    UNION
    
    SELECT 
        "Level", 
        rolname, 
        relname,  
        nspname, 
        'N/A', 
        "Object Type", 
        privileges,
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A',
        'N/A'
    FROM object_permissions
    ORDER BY "Role";

Answer 4

The answer from Vao Tsun regarding how to see permissions for schemas is outdated and potentially hides a lot of results (because pg_user only has users that can log in, so the joins fail when the grantor or grantee is a role that cannot log in). Use this instead:

Show schema permissions (2023)

The following lists schemas and permissions for the current database.

WITH users AS (select rolname, oid
               from pg_roles
               union
               select 'PUBLIC', 0)
SELECT r.rolname AS grantor,
       e.rolname AS grantee,
       nspname   as schema,
       privilege_type,
       is_grantable
FROM pg_namespace,
     aclexplode(nspacl) AS a
     JOIN users AS e
          ON a.grantee = e.oid
     JOIN users AS r
          ON a.grantor = r.oid
-- Add a WHERE clause to limit results to a single user
-- WHERE e.rolname = 'PUBLIC' or e.rolname = 'THE_ROLE_YOU_WANT'

Answer 5

You can also just use this to see if your user has anything other than SELECT

SELECT * FROM information_schema.role_table_grants WHERE grantee = 'username' AND with_hierarchy = 'YES'

Answer 6

Table permissions:

SELECT *
  FROM information_schema.role_table_grants 
 WHERE grantee = 'YOUR_USER';

Ownership:

SELECT *
  FROM pg_tables 
 WHERE tableowner = 'YOUR_USER';

Schema permissions:

      SELECT r.usename AS grantor,
             e.usename AS grantee,
             nspname,
             privilege_type,
             is_grantable
        FROM pg_namespace
JOIN LATERAL (SELECT *
                FROM aclexplode(nspacl) AS x) a
          ON true
        JOIN pg_user e
          ON a.grantee = e.usesysid
        JOIN pg_user r
          ON a.grantor = r.usesysid 
       WHERE e.usename = 'YOUR_USER';

Answer 7

This command was helpful for me:

\l

Here's how I used it:

postgres=# \l

                        List of databases
 Name   | Owner    | Encoding | Collate | Ctype |          Access privileges          
------------------------------+-----------------+----------+---------+-------+-------------------------------------
 mydb1  | postgres | UTF8     | en_NG   | en_NG | =Tc/postgres                       +
        |          |          |         |       | postgres=CTc/postgres              +
        |          |          |         |       | myuser=CTc/postgres
 mydb2  | postgres | UTF8     | en_NG   | en_NG | =Tc/postgres                       +
        |          |          |         |       | postgres=CTc/postgres              +
        |          |          |         |       | my_user=CTc/postgres

Resources: PostgreSQL: List the database privileges using psql

That's all.

I hope this helps

Answer 8

This is what worked for me the best. short and clean.

\du lists all user accounts and roles and \du+ is the extended version which shows even more information.

# \du
                                        List of roles
     Role name      |                         Attributes                         | Member of
--------------------+------------------------------------------------------------+-----------
 padmin             | Superuser, Create role, Create DB                          | {}
 test               |                                                            | {}
 postgres           | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root               | Superuser, Create role, Create DB                          | {}

# \du+
                                               List of roles
     Role name      |                         Attributes                         | Member of | Description
--------------------+------------------------------------------------------------+-----------+-------------
 padmin             | Superuser, Create role, Create DB                          | {}        |
 test               |                                                            | {}        |
 postgres           | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |
 root               | Superuser, Create role, Create DB                          | {}        |