postgresql-revoke 回收权限及删除角色

回收权限及删除角色

revoke回收权限

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    FROM { [ GROUP ] role_name | PUBLIC } [, ...]
    [ CASCADE | RESTRICT ]

• 删除用户

--创建表并赋权限
postgres=#  create schema schema1;
CREATE SCHEMA
postgres=#  set search_path=schema1;
SET
postgres=# create table schema1.test(id int);
CREATE TABLE
postgres=# insert into schema1.test select generate_series(1,10);
INSERT 0 10
postgres=#  create role role_a  with password '123456' login;
CREATE ROLE
postgres=# grant all on database postgres to role_a;
GRANT
postgres=#  grant select on all tables in schema schema1 to role_a;
GRANT
postgres=#  grant all on schema schema1 to role_a;
GRANT

--将前面对象赋权时创建的role_a删除
postgres=# drop role role_a;
ERROR:  role "role_a" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema schema1
privileges for table test
privileges for database postgres

--删除role失败
postgres=# drop role role_a;
ERROR:  role "role_a" cannot be dropped because some objects depend on it
DETAIL:  privileges for schema schema1
privileges for table test
privileges for database postgres

--要想删除用户必须回收所有权限
postgres=# revoke all on schema schema1 from role_a;
REVOKE
postgres=# drop role role_a;
ERROR:  role "role_a" cannot be dropped because some objects depend on it
DETAIL:  privileges for table test
privileges for database postgres

postgres=# revoke all on all tables in schema schema1 from role_a;
REVOKE
postgres=# drop role role_a;
ERROR:  role "role_a" cannot be dropped because some objects depend on it
DETAIL:  privileges for database postgres
postgres=# 

postgres=# revoke all on database postgres  from role_a;
REVOKE
postgres=# drop role role_a;
DROP ROLE

删除用户前，需要回收权限

• 回收template0的连接权限：

postgres=# revoke connect on database template1 from role_a;
REVOKE
postgres=# c template1 role_a
psql (9.6.4, server 9.5.3)
You are now connected to database "template1" as user "role_a".

回收template1的连接权限并不生效，控制template1的连接，可以在pg_hba.conf配置，参考前面pg_hba.conf的配置

要删除一个组角色，执行DROP ROLE group_role命令即可。然而在删除该组角色之后，它与其成员角色之间的关系将被立即撤销(成员角色本身不会受影响)。不过需要注意的是，在删除之前，任何属于该组角色的对象都必须先被删除或者将对象的所有者赋予其它角色，与此同时，任何赋予该组角色的权限也都必须被撤消。