阿里云rds postgres回收用户权限

  • 时间:
  • 浏览:0

注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=>

要想本来 xhc_rw对不属于我本人的表就有读写权限时要执行以下4句

注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

2,若为rds_superuser 则改为nords_superuser

xhc_dba=> alter user xhc_rw nords_superuser;

1,提工单

机会当初设计就有用的public schema,而public schema对所有用户权限不出限制,很多很多时要回收每段用户的建表权限,

=> dn

List of schemas

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_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

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

而public 默认owner是初始化实列的用户,很多很多提供单将public owner改成我本人建的用户

将test,template1数据库public schema的owner给xhc_test 。

=> dn

List of schemas

xhc_dba=> d

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=>