PostgresSQL创建只读账户

创建只读账户

单独

--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');