问题描述

我想编写一个查询来转储每个用户对到达表的权限。进行此搜索的原因是我可以对表权限进行快照，然后在服务器之间或在运行大型 GRANTS 重置脚本之前和之后比较它们。我正在寻找易于比较的输出，所以是这样的：

schema_name table_name   qualified_name  owner_name privilege   setting
api         base4        api.base4       postgres   delete      TRUE
api         bucket_test  api.bucket_test postgres   delete      TRUE

我已经编写了一个查询，它可以让我部分地到达那里，但权限名称和设置作为列对。我可以通过在特权之后命名它们来将列切成两半，但我遵循上面的窄行格式。这使得我将对表+用户+权限进行细化的比较。

schema_name   table_name   qualified_name   owner_name   privilege   delete   privilege   insert   privilege    references  privilege setting   privilege   trigger   privilege   truncate   privilege   update
api           base4        api.base4        postgres     delete      TRUE     insert      TRUE     references   TRUE        select    TRUE      trigger     TRUE      truncate    TRUE       update      TRUE
api          bucket_test   api.bucket_test  postgres     delete      TRUE     insert      TRUE     references   TRUE        select    TRUE      trigger     TRUE      truncate    TRUE       update      TRUE

有人可以建议正确的加入或 unnest+join 来重新处理我现在得到的查询吗？

而且，是的，这个查询会生成很多结果行。没关系，这就是我所追求的。

with 
table_list as
(   select schemaname as schema_name,
            tablename as table_name,
        quote_ident(schemaname) || '.' || quote_ident(tablename) as qualified_name,
             tableowner as owner_name

      from pg_tables

     where schemaname in ('data','api')  

  order by 3),

user_list as 
(   select usename as user_name
      from pg_user
  order by 1)

     select table_list.schema_name,
            table_list.table_name,
            table_list.qualified_name,
            table_list.owner_name,
         'delete' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'delete') as delete,
         'insert' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'insert') as insert,
         'references' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'references') as references,
         'select' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'select') as select,
         'trigger' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'trigger') as trigger,
         'truncate' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'truncate') as truncate,
         'update' as privilege, has_table_privilege(user_list.user_name, concat(table_list.qualified_name), 'update') as update

       from table_list
 cross join user_list

我在 RDS 上使用 Postgres 11.4。

跟进

对于以后发现此问题的任何人，以下是最终查询的一个版本作为视图：

DROP VIEW IF EXISTS data.table_grants;
CREATE OR REPLACE VIEW data.table_grants AS

with 
table_list as
(   select schemaname as schema_name,
            tablename as table_name,
             schemaname::text || '.' || tablename::text as qualified_name,
             tableowner as owner_name

      from pg_tables

     where schemaname in ('data','api')  

  order by 3),

user_list as 
(   select usename as user_name
      from pg_user
  order by 1)

select
  table_list.*,
  user_list.user_name,
  privilege,
  has_table_privilege(user_name, qualified_name, privilege) as setting
from
  table_list
  cross join user_list
  cross join (values
    ('delete'), ('insert'), ('references'), ('select'), ('trigger'), ('truncate'), ('update')
  ) as p(privilege);

ALTER TABLE data.table_grants
    OWNER TO user_change_structure;

这使得在表授予上的搜索更简单一些，就像这个查看在名为的表上授予的权限item

  select *
    from table_grants 
   where table_name = 'item'

order by user_name,
         privilege;

或此查询以获取特定表上用户权限的汇总视图：

  select qualified_name,
         owner_name,
         user_name,
         array_agg(privilege) as rights

    from table_grants 

   where table_name = 'item' and
         setting = true

group by qualified_name,
         owner_name,
         user_name;

上面的查询不一定是最有效的，视图的产品是表 * 用户 * 8 ......但对我来说，这一切都是即时的，只有不到 100 个表和大约 15 个角色。

标签： postgresqlgrant

select table_list.*, privilege, has_table_privilege(user_name, qualified_name, privilege) as setting from table_list cross join user_list cross join (values ('delete'), ('insert'), ('references'), ('select'), ('trigger'), ('truncate'), ('update') ) as p(privilege)

postgresql - 查询以返回每个表的用户权限，每个结果行一个权限

问题描述

跟进

解决方案

推荐阅读