postgresql查看用户拥有权限
postgresql查看用户拥有权限（database，schema，table）
我们已经知道在pg中可以使用元命令列出相应权限，例如 \l+查看database，\dn+查看schema，\dp查看table、view 、sequence。
如果只想看某一用户拥有的权限，也需要在列出来的信息中逐个查找。
那么就自己动手
在某个database中查询用户拥有的权限
根据用户名查询database权限，sql如下
结果展示，用户test拥有postgres、test_db的TEMPORARY和CONNECT权限，拥有db1的TEMPORARY权限，拥有db2的CONNECT权限
根据用户名查询schema权限，sql如下
结果展示，用户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执行，不需要密码
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$;
- 发表于 2024-11-03 22:20
- 阅读 ( 35 )
