主页 / dba / 问题 / 285591
Accepted
Ουιλιαμ Αρκευα
Asked: 2021-02-18 19:39:55 +0800 CST

如何在 PostgreSQL 上列出每个用户/角色的所有授权

  • 772

我在 Postgres CLI 上运行了这些语句（我使用的是 PostgreSQL v13.1）：

CREATE ROLE blog_user;
GRANT blog_user TO current_user;

我创建了一个函数

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;

最后我授予了权限：

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

我希望在我的模式/数据库中列出每个用户/角色的所有授权。\du并\du+显示基本信息，其中不包含有关最近进行的授权（在函数上执行）的信息。

postgresql permissions

3 个回答

  1. Best Answer

    虽然以下不是一个完整的解决方案（不包括列 privs，它没有函数签名），但您应该希望能够获得您要求使用的大部分内容：

    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 ;
    • 7

  2. 在 PostgreSQL 中没有简单的方法可以做到这一点。您必须单独检查所有对象。

    • 1

  3. 这个查询回答了我同样的问题：如何知道 acl on function ？希望它可以提供帮助。

    SELECT
    routine_catalog AS fct_db,
    routine_schema  AS fct_sch,
    routine_name    AS fct_nam,
    privilege_type  AS fct_priv,
    array_agg (grantee::text ORDER BY grantee::text) AS fct_rol
FROM
    information_schema.routine_privileges
WHERE
    routine_schema NOT IN ('information_schema','pg_catalog')
GROUP BY
    routine_catalog, routine_schema, routine_name, privilege_type
ORDER BY
    routine_catalog, routine_schema, routine_name, privilege_type
;

    但是，如果函数具有相同的名称但参数不同，则效果不佳。例如dblink (text), dblink (text, boolean), dblink (text, text). 对于您的功能，您必须替换routine_schema NOT IN ('information_schema','pg_catalog')为routine_name = 'your_function_name'

    • -1

相关问题