create user ysw_1  password 'y1234567.';
create user ysw_2  password 'y1234567.';
create user ysw_3  password 'y1234567.';

--登陆ysw_1用户
gsql postgres -r -p 25308 -U ysw_1 -W y1234567.
--将schema：ysw_1的select权限给用户ysw_2
ALTER DEFAULT PRIVILEGES FOR USER ysw_1 IN SCHEMA ysw_1 GRANT SELECT ON TABLES TO ysw_2;
--将schema：ysw_1的insert权限给用户ysw_3.
ALTER DEFAULT PRIVILEGES FOR USER ysw_1 IN SCHEMA ysw_1 GRANT INSERT ON TABLES TO ysw_3;

select * from pg_default_acl;

SELECT 
pg_catalog.pg_get_userbyid(d.defaclrole) AS "Granter",  
n.nspname AS "Schema",  
CASE d.defaclobjtype WHEN 'r' THEN 'TABLES' WHEN 'S' THEN 'SEQUENCES' WHEN 'f' THEN 'FUNCTIONS' WHEN 'T' THEN 'TYPES' END AS "Type",
pg_catalog.array_to_string(d.defaclacl, E', ') AS "Access privileges" 
FROM pg_catalog.pg_default_acl d 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace 
ORDER BY 1, 2, 3;

2、建立权限映射表

create table public.permission_mapping
(
    prototype varchar(1),
    aim varchar(10)
);
insert into permission_mapping values('r','select');
insert into permission_mapping values('a','insert');

3、创建一个函数进行权限的格式转化

比如将ar转化为：insert,select

create or replace function permission_exchange(varchar)
returns varchar
as
$$
declare
re varchar :='';
tmp varchar := '';
single varchar :='';
i int := 1;
length int := char_length($1);
begin
while i<=length loop
select substring($1,i,1) into single;
select aim into tmp from permission_mapping where prototype = single;
re :=concat(re,tmp);
if i<length
then re :=concat(re,',');
end if;
i :=i+1;
end loop;
return re;
end;
$$
LANGUAGE plpgsql;

4、生成原始赋权语句的sql

alter default privileges for user 授权用户 in schema 模式 grant 拼接权限 on 类型 to 用户;

最终查询sql

with tmp as(
    select defaclrole,defaclnamespace,defaclobjtype,unnest(defaclacl) as defaclacl from pg_catalog.pg_default_acl
)
SELECT 
pg_catalog.pg_get_userbyid(d.defaclrole) AS "granter",
n.nspname AS "schema",
CASE d.defaclobjtype WHEN 'r' THEN 'tables' WHEN 'S' THEN 'sequences' WHEN 'f' THEN 'functions' WHEN 'T' THEN 'types' END AS "Type",
d.defaclacl,
permission_exchange(to_char(regexp_substr(d.defaclacl::text, '(?<=\=).*?(?=\/)'))) as right,
regexp_substr(d.defaclacl::text,'\w+(?=\=)') as to_user
FROM tmp d 
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = d.defaclnamespace 
ORDER BY 1, 2, 3;

提示：可根据需要按照示例模板拼接赋权的sql语句