361
阿裏雲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_superuser
xhc_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 public
xhc_dba=>
注1
單單回收某個用戶的create權限是沒有用的，必須回收public的create權限，
hc_dba=> revoke create on schema public from xhc_rw;
REVOKe
xhc_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 TABLE
xhc_dba=>
注2
xhc_dba=> \c
psql (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，權限的
注3
hc_dba=> \c
psql (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 TABLE
xhc_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;
GRANT
xhc_dba=> alter default privileges in schema public grant select,update,delete,insert on tables to xhc_rw;
ALTER DEFAULT PRIVILEGES
xhc_dba=> alter default privileges in schema public grant select,usage on sequences to xhc_rw;
ALTER DEFAULT PRIVILEGES
最後更新：2017-05-23 13:01:10