PostgreSQL Grants – How to List All Grants per User/Role on PostgreSQL

permissionspostgresql

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.

Best Answer

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 ;
Related Question