创建只读账户
单独
--1.创建用户
create user zd_user with login password '6789#JKL';
--2.设置用户默认事务只读
alter user zd_user set default_transaction_read_only = on;
--3.用户连接的数据库权限
grant connect on database "DB_FY" to zd_user;
--4.授权对schema的使用
grant usage on schema schema_test to zd_user;
--5.赋予序列权限
grant select on all sequences in schema schema_test to zd_user;
--6.授权访问schema下的所有表
grant select on all tables in schema schema_test to zd_user;
--7.未来新增表的查看权限
alter default privileges in schema schema_test grant select on tables to zd_user;
批量
当schema很多时,一个个赋权很麻烦
--1.创建用户
create user zd_user with login password '6789#JKL';
--设置用户默认事务只读
alter user zd_user set default_transaction_read_only = on;
--2.用户连接的数据库权限
grant connect on database "DB_FY" to zd_user;
--3、创建自定义函数
create or replace function user_readonly(userName varchar(300))
returns "pg_catalog"."varchar" as $BODY$
declare
schemaName1 cursor for select distinct schemaname as schema1 from pg_stat_all_tables where schemaname not in ('pg_toast','pg_catalog','information_schema');
schemaName2 varchar(300);
str1 varchar(900);
str2 varchar(900);
str3 varchar(900);
str4 varchar(900);
begin
open schemaName1;
fetch schemaName1 into schemaName2;
while found loop
--INSERT INTO public.t_aa VALUES (schemaName2);
str1 := 'grant usage on schema ' || schemaName2 || ' to ' || userName;
str2 := 'grant select on all sequences in schema ' || schemaName2 || ' to ' || userName;
str3 := 'grant select on all tables in schema ' || schemaName2 || ' to ' || userName;
str4 := 'alter default privileges in schema ' || schemaName2 || ' grant select on tables to ' || userName;
execute str1;
execute str2;
execute str3;
execute str4;
fetch schemaName1 into schemaName2;
end loop;
close schemaName1;
RETURN 'success';
end;
$BODY$
language 'plpgsql'
;
--4、批量赋权
select user_readonly('t3wh_zd');