参考:查看用户拥有权限

postgresql查看用户拥有权限（database，schema，table）

我们已经知道在pg中可以使用元命令列出相应权限，例如 \l+查看database，\dn+查看schema，\dp查看table、view 、sequence。

但是，展示出来的信息并不直观，(CTc,UC,arwdDxt这些权限信息看起来不太友好）

在某个database中查询用户拥有的权限
根据用户名查询database权限，sql如下

select a.datname,b.rolname,string_agg(a.pri_t,',') from (select datname,(aclexplode(COALESCE(datacl, acldefault('d'::"char",datdba)))).grantee as grantee,(aclexplode(COALESCE(datacl, acldefault('d'::"char", datdba)))).privilege_type as pri_t from pg_database where datname not like 'template%') a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='test' group by a.datname,b.rolname;

如果只想看某一用户拥有的权限，也需要在列出来的信息中逐个查找。

结果展示，用户test拥有postgres、test_db的TEMPORARY和CONNECT权限，拥有db1的TEMPORARY权限，拥有db2的CONNECT权限

根据用户名查询schema权限，sql如下

select a.nspname,b.rolname,string_agg(a.pri_t,’,’) from (select nspname,(aclexplode(COALESCE(nspacl, acldefault(‘n’::”char”,nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault(‘n’::”char”,nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like ‘pg%’ and nspname <> ‘information_schema’) a,pg_authid b where (a.grantee=b.oid or a.grantee=0) and b.rolname=’test’ group by a.nspname,b.rolname;
结果展示，用户test拥有public、test的USAGE和CREATE权限，拥有yhru的USAGE权限

根据用户名查询table权限，可以通过视图information_schema.table_privileges来查看，为了方便展示，sql如下

select table_name,table_schema,grantee,string_agg(privilege_type,’,’) from information_schema.table_privileges where grantee=’test’ group by table_name,table_schema,grantee;
结果展示，test用户拥有test的SELECT权限，拥有test3的INSERT、SELECT、UPDATE、DELETE权限

在实例中查询用户拥有的权限
在pg中用户是全局的，所以某个用户可能拥有多个database的权限，想要查询该用户拥有的所有权限就需要登录不同的库去查询，可以使用dblink插件，实现在不同的库中获取结果。

于是产生了以下函数，下面展示函数执行结果，函数详细信息见结尾

用户test有test_db中public.bank的SELECT、UPDATE权限

• NOTICE：
  1、database默认权限是TEMPORARY和CONNECT
  2、所有用户默认拥有public schema的USAGE和CREATE权限
  3、需要访问表时，必须拥有表的SELECT权限和对应schema的USAGE权限，缺一不可
  4、database、schema、table的owner默认拥有database或schema或table所有权限

• FUNCTION：
  函数中使用了插件dblink，需安装dblink插件后才可正确执行，由于dblink连接只接受superuser使用无密码方式，所以提供两个版本

get_user_privilege –使用superuser执行，不需要密码

CREATE OR REPLACE FUNCTION public.get_user_privilege(user_name name)
 RETURNS  table(bject_name name,object_type varchar,dbname name,schema_name name,rolname name,user_privilege varchar)
 LANGUAGE plpgsql
AS $function$
DECLARE
    ob_name name;
    rol_name name;
    db_name name;
    sch_name name;
    user_pri varchar;
    sql varchar;
    super_flag boolean;
BEGIN
   --clear temporary table
   drop table if exists user_privilege;
   create temporary table if not exists user_privilege (id serial,object_name name,object_type varchar,dbname name,schema_name name,rolname name,user_privilege varchar);
   --super user has all privilege
   sql := 'select rolsuper from pg_roles where rolname='||''''||user_name||'''';
   execute sql into super_flag;
   if super_flag = true then
      insert into user_privilege (object_name,object_type,rolname,user_privilege) values ('all','all',user_name,'super user has all privilege');
   else
      --get user_privilege of database
      sql := 'select a.datname,b.rolname,string_agg(a.pri_t,'','') from (select datname,(aclexplode(COALESCE(datacl, acldefault(''d''::"char",datdba)))).grantee as grantee,(aclexplode(COALESCE(datacl, acldefault(''d''::"char", datdba)))).privilege_type as pri_t from pg_database where datname not like ''template%'' ) a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='''||user_name||''' group by a.datname,b.rolname';
      for ob_name,rol_name,user_pri in execute sql
      loop
         insert into user_privilege (object_name,object_type,rolname,user_privilege) values (ob_name,'database',rol_name,user_pri);
      end loop;
      --get user_privilege of schema,table by database
      for db_name in select object_name from user_privilege a where a.object_type='database' and a.user_privilege ~ 'CONNECT'
      loop
          --get user_privilege of schema
         sql := 'select * from dblink(''dbname='||db_name||''',''select a.nspname,b.rolname,string_agg(a.pri_t,'''','''') from (select nspname,(aclexplode(COALESCE(nspacl, acldefault(''''n''''::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault(''''n''''::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like ''''pg%'''' and nspname <> ''''information_schema'''') a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='''''||user_name||''''' group by a.nspname,b.rolname'') as (ob_name name,rol_name name,user_pri varchar)';
         for ob_name,rol_name,user_pri in execute sql 
         loop
            insert into user_privilege (object_name,object_type,dbname,rolname,user_privilege) values (ob_name,'schema',db_name,rol_name,user_pri);
         end loop;
         --get user_privilege of table
         sql := 'select * from dblink(''dbname='||db_name||''',''select table_name,table_schema,grantee,string_agg(privilege_type,'''','''') from information_schema.table_privileges where grantee='''''||user_name||''''' group by table_name,table_schema,grantee'') as (ob_name name,sch_name name,rol_name name,user_pri varchar)';
         for ob_name,sch_name,rol_name,user_pri in execute sql
         loop
            insert into user_privilege (object_name,object_type,dbname,schema_name,rolname,user_privilege) values (ob_name,'table',db_name,sch_name,rol_name,user_pri);
         end loop;
      end loop;
   end if;
   return query select d.object_name,d.object_type,d.dbname,d.schema_name,d.rolname,d.user_privilege from user_privilege d  order by d.id;
end;
$function$;

get_user_privilege_with_passwd –使用普通用户执行，需要密码

CREATE OR REPLACE FUNCTION public.get_user_privilege_with_passwd(user_name name,passwd varchar)
 RETURNS  table(bject_name name,object_type varchar,dbname name,schema_name name,rolname name,user_privilege varchar)
 LANGUAGE plpgsql
AS $function$
DECLARE
    ob_name name;
    rol_name name;
    db_name name;
    sch_name name;
    user_pri varchar;
    sql varchar;
    super_flag boolean;
    port int;
BEGIN
   --clear temporary table
   drop table if exists user_privilege;
   create temporary table if not exists user_privilege (id serial,object_name name,object_type varchar,dbname name,schema_name name,rolname name,user_privilege varchar);
   --super user has all privilege
   sql := 'select rolsuper from pg_roles where rolname='||''''||user_name||'''';
   execute sql into super_flag;
   if super_flag = true then
      insert into user_privilege (object_name,object_type,rolname,user_privilege) values ('all','all',user_name,'super user has all privilege');
   else
      --get server port
      sql := 'select setting  from pg_settings where name =''port''';
      execute sql into port;
      --get user_privilege of database
      sql := 'select a.datname,b.rolname,string_agg(a.pri_t,'','') from (select datname,(aclexplode(COALESCE(datacl, acldefault(''d''::"char",datdba)))).grantee as grantee,(aclexplode(COALESCE(datacl, acldefault(''d''::"char", datdba)))).privilege_type as pri_t from pg_database where datname not like ''template%'' ) a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='''||user_name||''' group by a.datname,b.rolname';
      for ob_name,rol_name,user_pri in execute sql
      loop
         insert into user_privilege (object_name,object_type,rolname,user_privilege) values (ob_name,'database',rol_name,user_pri);
      end loop;
      --get user_privilege of schema,table by database
      for db_name in select object_name from user_privilege a where a.object_type='database' and a.user_privilege ~ 'CONNECT'
      loop
          --get user_privilege of schema
         sql := 'select * from dblink(''dbname='||db_name||' user='||user_name||' hostaddr='||COALESCE(inet_out(inet_server_addr()),'127.0.0.1')||' port='||COALESCE(inet_server_port(),port)||' password='||passwd||''',''select a.nspname,b.rolname,string_agg(a.pri_t,'''','''') from (select nspname,(aclexplode(COALESCE(nspacl, acldefault(''''n''''::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault(''''n''''::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like ''''pg%'''' and nspname <> ''''information_schema'''') a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='''''||user_name||''''' group by a.nspname,b.rolname'') as (ob_name name,rol_name name,user_pri varchar)';
         for ob_name,rol_name,user_pri in execute sql 
         loop
            insert into user_privilege (object_name,object_type,dbname,rolname,user_privilege) values (ob_name,'schema',db_name,rol_name,user_pri);
         end loop;
         --get user_privilege of table
         sql := 'select * from dblink(''dbname='||db_name||' user='||user_name||' hostaddr='||COALESCE(inet_out(inet_server_addr()),'127.0.0.1')||' port='||COALESCE(inet_server_port(),port)||' password='||passwd||''',''select table_name,table_schema,grantee,string_agg(privilege_type,'''','''') from information_schema.table_privileges where grantee='''''||user_name||''''' group by table_name,table_schema,grantee'') as (ob_name name,sch_name name,rol_name name,user_pri varchar)';
         for ob_name,sch_name,rol_name,user_pri in execute sql
         loop
            insert into user_privilege (object_name,object_type,dbname,schema_name,rolname,user_privilege) values (ob_name,'table',db_name,sch_name,rol_name,user_pri);
         end loop;
      end loop;
   end if;
   return query select d.object_name,d.object_type,d.dbname,d.schema_name,d.rolname,d.user_privilege from user_privilege d  order by d.id;
end;
$function$;