列出在PostgreSQL中的物化视图的授予和特权清单。

Question

列出在PostgreSQL中的物化视图的授予和特权清单。

databasepostgresqlprivilegesmaterialized-viewsinformation-schema

18

我需要确定数据库中某些物化视图当前授予了哪些权限。

对于表或标准视图，查询这个信息相当简单：

SELECT grantee, string_agg(privilege_type, ', ') AS privileges
FROM information_schema.table_privileges
WHERE table_schema = 'some_schema' AND table_name = 'some_table'
GROUP by grantee;

虽然如此，似乎没有针对物化视图的类似表格。PostgreSQL将这些信息存储在哪里？

- Leah Sapan

您的查询适用于普通视图，但不适用于物化视图。 - user330315

2个回答

4

在Klin提供的有用答案的基础上，我提出了一个视图，列出了所有角色的pg_class中出现的所有关系（表、视图、材料化视图、索引、序列、外部表、组合类型）的所有权限的摘要：

CREATE VIEW show_privileges AS (
    SELECT
        grantee,
        string_agg(relname, ', ' ORDER BY relname) AS rel_names,
        privileges
    FROM (
        SELECT 
            relname,
            coalesce(nullif(s[1], ''), 'public') grantee, 
            (SELECT string_agg(privilege, ', ' ORDER BY privilege ASC)
                FROM (SELECT
                    CASE ch
                        WHEN 'r' THEN 'SELECT'
                        WHEN 'w' THEN 'UPDATE'
                        WHEN 'a' THEN 'INSERT'
                        WHEN 'd' THEN 'DELETE'
                        WHEN 'D' THEN 'TRUNCATE'
                        WHEN 'x' THEN 'REFERENCES'
                        WHEN 't' THEN 'TRIGGER'
                    END AS privilege
                    FROM regexp_split_to_table(s[2], '') ch
                ) s
            ) AS privileges
        FROM 
            pg_class
            JOIN pg_namespace ON pg_namespace.oid = relnamespace
            JOIN pg_roles ON pg_roles.oid = relowner,
            unnest(coalesce(relacl::text[], format('{%s=arwdDxt/%s}', rolname, rolname)::text[])) AS acl,
            regexp_split_to_array(acl, '=|/') AS s
        WHERE nspname = 'public'
    ) AS t
    GROUP BY grantee, privileges
    ORDER BY grantee, privileges, rel_names
);

- gilad905

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- klin · Accepted Answer

在Postgres中，系统目录是有关安装和数据库的基本完整信息。系统目录是最可靠的信息来源。信息模式作为辅助功能基于系统目录，并提供与其他RDBMs的兼容性。

信息模式在SQL标准中定义，因此可以期望其具有可移植性并保持稳定性 - 与系统目录不同，后者特定于PostgreSQL，并且基于实现考虑进行建模。然而，信息模式视图不包含有关PostgreSQL特定功能的信息; 要查询这些信息，您需要查询系统目录或其他PostgreSQL特定视图。

材料化视图不是SQL标准对象，因此信息模式不包含有关它们的信息。

系统目录pg_class包含列relacl中权限的所有信息。

如果列为null，则所有者具有所有权限。

在acl字符串中，用户名为空字符串表示public。

create materialized view test_view as select 1;
grant select on test_view to public;
grant delete on test_view to a_user;

select 
    coalesce(nullif(s[1], ''), 'public') as grantee, 
    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 nspname = 'public' and relname = 'test_view';

 grantee  | privileges 
----------+------------
 postgres | arwdDxt
 public   | r
 a_user   | d
(3 rows)

你需要一个函数来以易读的格式显示权限：

create or replace function priviliges_from_acl(text)
returns text language sql as $$
    select string_agg(privilege, ', ')
    from (
        select 
            case ch
                when 'r' then 'SELECT'
                when 'w' then 'UPDATE'
                when 'a' then 'INSERT'
                when 'd' then 'DELETE'
                when 'D' then 'TRUNCATE'
                when 'x' then 'REFERENCES'
                when 't' then 'TRIGGER'
            end privilege
        from
            regexp_split_to_table($1, '') ch
    ) s 
$$;

使用：

select 
    coalesce(nullif(s[1], ''), 'public') as grantee, 
    priviliges_from_acl(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 nspname = 'public' and relname = 'test_view';

 grantee  |                          privileges                           
----------+---------------------------------------------------------------
 postgres | INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
 public   | SELECT
 a_user   | DELETE
(3 rows)