1

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.

2

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 ;
2
  • Thanks man. This statement GRANT SELECT(id) ON users TO anonymous; apparently does not appear in the result. – Ουιλιαμ Αρκευα Feb 18 at 16:58
  • 1
    I didn't include the column privs. Those can be obtained from the information_schema.role_column_grants view. – gsiems Feb 18 at 20:11
0

There is no simple way to do that in PostgreSQL. You'd have to examine all objects individually.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.