postgresql查看用户拥有权限 -m6米乐安卓版下载

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