阿里云rds postgres回收用户权限
1，提工单由于当初设计都是用的public schema，而public schema对所有用户权限没有限制，所以需要回收部分用户的建表权限，=> dn List of schemas Name Owner public pg2490375 而public 默认owner是初始化实列的用户，所以提供单将public owner改成自己建的用户将test,template1数据库public schema的owner给xhc_test 。=> dn List of schemas Name Owner public xhc_test 2，若为rds_superuser 则改为nords_superuserxhc_dba=> alter user xhc_rw nords_superuser; 3，回收create 权限xhc_dba=> revoke create on schema public from public;xhc_dba=> c - xhc_rw;You are now connected to database "xhc_dba" as user "xhc_rw”. —回收成功xhc_dba=> create table t000(id serial,id2 bigint);ERROR: permission denied for schema publicxhc_dba=> 注1单单回收某个用户的create权限是没有用的，必须回收public的create权限，hc_dba=> revoke create on schema public from xhc_rw;REVOKexhc_dba=> c - xhc_rw;You are now connected to database "xhc_dba" as user "xhc_rw".xhc_dba=> create t000(id bigint,id2 serial primary key);xhc_dba=> create table t000(id bigint,id2 serial primary key);CREATE TABLExhc_dba=> 注2xhc_dba=> cpsql (9.5.2, server 9.4.10)You are now connected to database "xhc_dba" as user "xhc_rw".xhc_dba=> d List of relations Schema Name Type Owner public t000 table xhc_rw xhc_dba=> drop table t000;虽然回收了xhc_rw的create权限，但是xhc_rw对以前建的表还是有ddl，权限的 注3hc_dba=> cpsql (9.5.2, server 9.4.10)You are now connected to database "xhc_dba" as user "xhc_test".xhc_dba=> dn List of schemas Name Owner public xhc_test xhc_dba=> d Schema Name Type Owner public t11 table xhc_rw xhc_dba=> alter table t11 owner to xhc_test;ALTER TABLExhc_dba=> alter table t11 owner to xhc_rw;ERROR: permission denied for schema public因为回收了所有用户的create权限，所以表的所属权是不可逆的，原来属于xhc_rw的表改成xhc_test之后就不能再改回来了 要想以后xhc_rw对不属于自己的表也有读写权限需要执行以下4句 grant select,insert,update,delete on all tables in schema public to xhc_rw;WARNING: no privileges were granted for “test” —因为test表本来就属于xhc_rw用户，所以会有警告，如果把test 表owner改成xhc_test就不会有警告了xhc_dba=> grant select,usage on all sequences in schema public to xhc_rw;GRANTxhc_dba=> alter default privileges in schema public grant select,update,delete,insert on tables to xhc_rw;ALTER DEFAULT PRIVILEGESxhc_dba=> alter default privileges in schema public grant select,usage on sequences to xhc_rw;ALTER DEFAULT PRIVILEGES
