snippetsqlModerate

How to list all grants per user/role on PostgreSQL

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

postgresqlhowperalluserrolegrantslist

Problem

I've run these statements on Postgres CLI (I'm using PostgreSQL v13.1):

CREATE ROLE blog_user;
GRANT blog_user TO current_user;

And I created a function

CREATE FUNCTION SIGNUP(username TEXT, email TEXT, password TEXT)
RETURNS jwt_token AS
$
DECLARE
  token_information jwt_token;
BEGIN
....
END;
$ LANGUAGE PLPGSQL VOLATILE SECURITY DEFINER;

Finally I granted a permission:

GRANT EXECUTE ON FUNCTION SIGNUP(username TEXT, email TEXT, password TEXT) TO anonymous;

I wish to list all grants per user/role in my schema/database. \du and \du+ show basic information, which does not contain info about the grant (execute on function) made recently.

Solution

While the following is not a complete solution (column privs aren't included, it doesn't have the function signatures) you should hopefully be able to get most of what you're asking for using:

SELECT rug.grantor,
        rug.grantee,
        rug.object_catalog,
        rug.object_schema,
        rug.object_name,
        rug.object_type,
        rug.privilege_type,
        rug.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_usage_grants rug
    WHERE rug.object_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rtg.grantor,
        rtg.grantee,
        rtg.table_catalog,
        rtg.table_schema,
        rtg.table_name,
        tab.table_type,
        rtg.privilege_type,
        rtg.is_grantable,
        rtg.with_hierarchy
    FROM information_schema.role_table_grants rtg
    LEFT JOIN information_schema.tables tab
        ON ( tab.table_catalog = rtg.table_catalog
            AND tab.table_schema = rtg.table_schema
            AND tab.table_name = rtg.table_name )
    WHERE rtg.table_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rrg.grantor,
        rrg.grantee,
        rrg.routine_catalog,
        rrg.routine_schema,
        rrg.routine_name,
        fcn.routine_type,
        rrg.privilege_type,
        rrg.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_routine_grants rrg
    LEFT JOIN information_schema.routines fcn
        ON ( fcn.routine_catalog = rrg.routine_catalog
            AND fcn.routine_schema = rrg.routine_schema
            AND fcn.routine_name = rrg.routine_name )
    WHERE rrg.specific_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rug.grantor,
        rug.grantee,
        rug.udt_catalog,
        rug.udt_schema,
        rug.udt_name,
        ''::text AS udt_type,
        rug.privilege_type,
        rug.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_udt_grants rug
    WHERE rug.udt_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND substr ( rug.udt_schema, 1, 3 ) <> 'pg_'
        AND grantor <> grantee ;

Updated 2023-01-26 to add an updated query that uses the pg catalog tables instead of the information_schema views. This updated query also contains column grants and function/procedure signatures.

Updated 2024-03-15 Replaced pg_authid with pg_roles (per Eric Jensen).

```
WITH rol AS (
SELECT oid,
rolname::text AS role_name
FROM pg_roles
UNION
SELECT 0::oid AS oid,
'public'::text
),
schemas AS ( -- Schemas
SELECT oid AS schema_oid,
n.nspname::text AS schema_name,
n.nspowner AS owner_oid,
'schema'::text AS object_type,
coalesce ( n.nspacl, acldefault ( 'n'::"char", n.nspowner ) ) AS acl
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_'
AND n.nspname <> 'information_schema'
),
classes AS ( -- Tables, views, etc.
SELECT schemas.schema_oid,
schemas.schema_name AS object_schema,
c.oid,
c.relname::text AS object_name,
c.relowner AS owner_oid,
CASE
WHEN c.relkind = 'r' THEN 'table'
WHEN c.relkind = 'v' THEN 'view'
WHEN c.relkind = 'm' THEN 'materialized view'
WHEN c.relkind = 'c' THEN 'type'
WHEN c.relkind = 'i' THEN 'index'
WHEN c.relkind = 'S' THEN 'sequence'
WHEN c.relkind = 's' THEN 'special'
WHEN c.relkind = 't' THEN 'TOAST table'
WHEN c.relkind = 'f' THEN 'foreign table'
WHEN c.relkind = 'p' THEN 'partitioned table'
WHEN c.relkind = 'I' THEN 'partitioned index'
ELSE c.relkind::text
END AS object_type,
CASE
WHEN c.relkind = 'S' THEN coalesce ( c.relacl, acldefault ( 's'::"char", c.relowner ) )
ELSE coalesce ( c.relacl, acldefault ( 'r'::"char", c.relowner ) )
END AS acl
FROM pg_class c
JOIN schemas
ON ( schemas.schema_oid = c.relnamespace )
WHERE c.relkind IN ( 'r', 'v', 'm', 'S', 'f', 'p' )
),
cols AS ( -- Columns
SELECT c.object_schema,
null::integer AS oid,
c.object_name || '.' || a.attname::text AS object_name,
'column' AS object_type,
c.owner_oid,
coalesce ( a.attacl, acldefault ( 'c'::"char", c.owner_oid ) ) AS acl
FROM pg_attribute a
JOIN classes c
ON ( a.attrelid = c.oid )
WHERE a.attnum > 0
AND NOT a.attisdropped
),
procs AS ( -- Procedures and functions
SELECT schemas.schema_oid,
schemas.schema_name AS object_schema,
p.oid,
p.proname::text AS object_name,

Code Snippets

SELECT rug.grantor,
        rug.grantee,
        rug.object_catalog,
        rug.object_schema,
        rug.object_name,
        rug.object_type,
        rug.privilege_type,
        rug.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_usage_grants rug
    WHERE rug.object_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rtg.grantor,
        rtg.grantee,
        rtg.table_catalog,
        rtg.table_schema,
        rtg.table_name,
        tab.table_type,
        rtg.privilege_type,
        rtg.is_grantable,
        rtg.with_hierarchy
    FROM information_schema.role_table_grants rtg
    LEFT JOIN information_schema.tables tab
        ON ( tab.table_catalog = rtg.table_catalog
            AND tab.table_schema = rtg.table_schema
            AND tab.table_name = rtg.table_name )
    WHERE rtg.table_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rrg.grantor,
        rrg.grantee,
        rrg.routine_catalog,
        rrg.routine_schema,
        rrg.routine_name,
        fcn.routine_type,
        rrg.privilege_type,
        rrg.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_routine_grants rrg
    LEFT JOIN information_schema.routines fcn
        ON ( fcn.routine_catalog = rrg.routine_catalog
            AND fcn.routine_schema = rrg.routine_schema
            AND fcn.routine_name = rrg.routine_name )
    WHERE rrg.specific_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rug.grantor,
        rug.grantee,
        rug.udt_catalog,
        rug.udt_schema,
        rug.udt_name,
        ''::text AS udt_type,
        rug.privilege_type,
        rug.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_udt_grants rug
    WHERE rug.udt_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND substr ( rug.udt_schema, 1, 3 ) <> 'pg_'
        AND grantor <> grantee ;

WITH rol AS (
    SELECT oid,
            rolname::text AS role_name
        FROM pg_roles
    UNION
    SELECT 0::oid AS oid,
            'public'::text
),
schemas AS ( -- Schemas
    SELECT oid AS schema_oid,
            n.nspname::text AS schema_name,
            n.nspowner AS owner_oid,
            'schema'::text AS object_type,
            coalesce ( n.nspacl, acldefault ( 'n'::"char", n.nspowner ) ) AS acl
        FROM pg_catalog.pg_namespace n
        WHERE n.nspname !~ '^pg_'
            AND n.nspname <> 'information_schema'
),
classes AS ( -- Tables, views, etc.
    SELECT schemas.schema_oid,
            schemas.schema_name AS object_schema,
            c.oid,
            c.relname::text AS object_name,
            c.relowner AS owner_oid,
            CASE
                WHEN c.relkind = 'r' THEN 'table'
                WHEN c.relkind = 'v' THEN 'view'
                WHEN c.relkind = 'm' THEN 'materialized view'
                WHEN c.relkind = 'c' THEN 'type'
                WHEN c.relkind = 'i' THEN 'index'
                WHEN c.relkind = 'S' THEN 'sequence'
                WHEN c.relkind = 's' THEN 'special'
                WHEN c.relkind = 't' THEN 'TOAST table'
                WHEN c.relkind = 'f' THEN 'foreign table'
                WHEN c.relkind = 'p' THEN 'partitioned table'
                WHEN c.relkind = 'I' THEN 'partitioned index'
                ELSE c.relkind::text
                END AS object_type,
            CASE
                WHEN c.relkind = 'S' THEN coalesce ( c.relacl, acldefault ( 's'::"char", c.relowner ) )
                ELSE coalesce ( c.relacl, acldefault ( 'r'::"char", c.relowner ) )
                END AS acl
        FROM pg_class c
        JOIN schemas
            ON ( schemas.schema_oid = c.relnamespace )
        WHERE c.relkind IN ( 'r', 'v', 'm', 'S', 'f', 'p' )
),
cols AS ( -- Columns
    SELECT c.object_schema,
            null::integer AS oid,
            c.object_name || '.' || a.attname::text AS object_name,
            'column' AS object_type,
            c.owner_oid,
            coalesce ( a.attacl, acldefault ( 'c'::"char", c.owner_oid ) ) AS acl
        FROM pg_attribute a
        JOIN classes c
            ON ( a.attrelid = c.oid )
        WHERE a.attnum > 0
            AND NOT a.attisdropped
),
procs AS ( -- Procedures and functions
    SELECT schemas.schema_oid,
            schemas.schema_name AS object_schema,
            p.oid,
            p.proname::text AS object_name,
            p.proowner AS owner_oid,
            CASE p.prokind
                WHEN 'a' THEN 'aggregate'
                WHEN 'w' THEN 'window'
                WHEN 'p' THEN 'procedure'
                ELSE 'function'
                END AS object_type,
            pg_catalog.pg_get_function_arguments ( p.oid ) AS calling_arguments,
            coalesce ( p.proacl, acldefault ( 'f'::"char", p.proowner ) ) AS acl
        FROM pg_proc p
        JOIN schemas
            ON ( schemas.schema_oid = p.pronamespace )
),
udt

Context

StackExchange Database Administrators Q#285591, answer score: 15

Revisions (0)

No revisions yet.