Postgresql 如何获取实例化视图的列级权限

有information_schema.column_privileges个:

视图column_privileges标识授予当前启用的角色或由当前启用的角色在列上授予的所有特权.列、授权者和被授权者的每个组合都有一行.

出于某种原因，它似乎可以很好地用于表和视图，但不能用于实例化视图:demo

create role alice;

create table sales(
  id int generated always as identity primary key, 
  secret_column int, 
  description int,
  fin_date date);

create view public.recent_sales_v as 
  select * from sales where fin_date>now()-'1 week'::interval;
grant select (id, description) on recent_sales_v to alice;

create materialized view public.recent_sales_mv as 
  select * from sales where fin_date>now()-'1 week'::interval;
grant select (id, description) on recent_sales_mv to alice;

select * from information_schema.column_privileges 
  where table_schema='public'
  and not current_user=all(array[grantor,grantee]);

请注意，它列出了带有_v Sufix的视图，但没有列出带有_mv的实例化视图:

如果你仔细看看column_privileges的定义，你会发现这很简单，因为当它从pg_class中取出东西时，matview被省略了:

WHERE pg_class.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'f'::"char", 'p'::"char"])

从pg_class.relkind人起:

relkind,char.

r=普通表，i=索引，S=序列，t=Toast表，v=视图，105 = materialized view,c=复合类型，f=外表，p=分区表，I=分区索引

所以我们就差m号了.抓起定义，加上它，你就会得到完整的视图:demo2

为了完整起见，下面是添加了matview的column_privileges的定义，您可以使用它来获取所需的信息:

SELECT * FROM (
SELECT u_grantor.rolname::information_schema.sql_identifier AS grantor,
    grantee.rolname::information_schema.sql_identifier AS grantee,
    current_database()::information_schema.sql_identifier AS table_catalog,
    nc.nspname::information_schema.sql_identifier AS table_schema,
    x.relname::information_schema.sql_identifier AS table_name,
    x.attname::information_schema.sql_identifier AS column_name,
    x.prtype::information_schema.character_data AS privilege_type,
        CASE
            WHEN pg_has_role(x.grantee, x.relowner, 'USAGE'::text) OR x.grantable THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.yes_or_no AS is_grantable
   FROM ( SELECT pr_c.grantor,
            pr_c.grantee,
            a.attname,
            pr_c.relname,
            pr_c.relnamespace,
            pr_c.prtype,
            pr_c.grantable,
            pr_c.relowner
           FROM ( SELECT pg_class.oid,
                    pg_class.relname,
                    pg_class.relnamespace,
                    pg_class.relowner,
                    (aclexplode(COALESCE(pg_class.relacl, acldefault('r'::"char", pg_class.relowner)))).grantor AS grantor,
                    (aclexplode(COALESCE(pg_class.relacl, acldefault('r'::"char", pg_class.relowner)))).grantee AS grantee,
                    (aclexplode(COALESCE(pg_class.relacl, acldefault('r'::"char", pg_class.relowner)))).privilege_type AS privilege_type,
                    (aclexplode(COALESCE(pg_class.relacl, acldefault('r'::"char", pg_class.relowner)))).is_grantable AS is_grantable
                   FROM pg_class
                  WHERE pg_class.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"])) pr_c(oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable),
            pg_attribute a
          WHERE a.attrelid = pr_c.oid AND a.attnum > 0 AND NOT a.attisdropped
        UNION
         SELECT pr_a.grantor,
            pr_a.grantee,
            pr_a.attname,
            c.relname,
            c.relnamespace,
            pr_a.prtype,
            pr_a.grantable,
            c.relowner
           FROM ( SELECT a.attrelid,
                    a.attname,
                    (aclexplode(COALESCE(a.attacl, acldefault('c'::"char", cc.relowner)))).grantor AS grantor,
                    (aclexplode(COALESCE(a.attacl, acldefault('c'::"char", cc.relowner)))).grantee AS grantee,
                    (aclexplode(COALESCE(a.attacl, acldefault('c'::"char", cc.relowner)))).privilege_type AS privilege_type,
                    (aclexplode(COALESCE(a.attacl, acldefault('c'::"char", cc.relowner)))).is_grantable AS is_grantable
                   FROM pg_attribute a
                     JOIN pg_class cc ON a.attrelid = cc.oid
                  WHERE a.attnum > 0 AND NOT a.attisdropped) pr_a(attrelid, attname, grantor, grantee, prtype, grantable),
            pg_class c
          WHERE pr_a.attrelid = c.oid AND (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"]))) x,
    pg_namespace nc,
    pg_authid u_grantor,
    ( SELECT pg_authid.oid,
            pg_authid.rolname
           FROM pg_authid
        UNION ALL
         SELECT 0::oid AS oid,
            'PUBLIC'::name) grantee(oid, rolname)
  WHERE x.relnamespace = nc.oid AND x.grantee = grantee.oid AND x.grantor = u_grantor.oid AND (x.prtype = ANY (ARRAY['INSERT'::text, 'SELECT'::text, 'UPDATE'::text, 'REFERENCES'::text])) AND (pg_has_role(u_grantor.oid, 'USAGE'::text) OR pg_has_role(grantee.oid, 'USAGE'::text) OR grantee.rolname = 'PUBLIC'::name)
) AS column_privileges_but_with_matviews
WHERE table_schema='public'
AND NOT current_user=ALL(array[grantor,grantee]);