PostgreSQL用户和权限配置

在PostgresSQL中如果尝试删除一个用户,可能会遇到这个报错:

1
2
ERROR:  role "XXX" cannot be dropped because some objects depend on it
DETAIL: privileges for table YYY

这是因为没有撤销该用户的权限所导致的。

具体以一个例子来说明。创建一张叫做“xxx”的表,再创建一个“test”用户,并将这张表的所有权限赋予“test”用户。

直接尝试删除这个用户,就会出现这个权限提示。

1
2
3
4
5
6
7
8
9
root=# CREATE TABLE xxx ( id SERIAL PRIMARY KEY , name CHAR(15) NOT NULL );
CREATE TABLE
root=# CREATE USER test PASSWORD 'test';
CREATE ROLE
root=# GRANT ALL ON xxx TO test;
GRANT
root=# DROP USER test;
ERROR: role "test" cannot be dropped because some objects depend on it
DETAIL: privileges for table xxx

可以通过下面这条语句查询PG中“test”用户的权限,并根据查询到的结果撤销之。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
root=# SELECT * FROM information_schema.table_privileges WHERE grantee='test';
grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy
---------+---------+---------------+--------------+------------+----------------+--------------+----------------
root | test | root | public | xxx | INSERT | NO | NO
root | test | root | public | xxx | SELECT | NO | YES
root | test | root | public | xxx | UPDATE | NO | NO
root | test | root | public | xxx | DELETE | NO | NO
root | test | root | public | xxx | TRUNCATE | NO | NO
root | test | root | public | xxx | REFERENCES | NO | NO
root | test | root | public | xxx | TRIGGER | NO | NO
(7 rows)

root=# REVOKE ALL ON xxx FROM test;
REVOKE
root=# DROP USER test;
DROP ROLE
root=#

批量撤销用户在数据库中所有表的权限:

1
2
revoke all on database XX from test;
revoke all on all tables in schema public from test;

PG权限的基本操作

赋予权限的语句是

1
GRANT *privilege ON *obj TO *user

撤销权限的语句从语法上与之对应:

1
REVOKE *privilege ON *obj FROM *user
  • privilege − SELECT,INSERT,UPDATE,DELETE, RULE,ALL
  • obj − 要授予访问权限的对象名称,可以是 table, view,sequence
  • user可以是以下情况
    • PUBLIC − 所有用户
    • GROUP group − 用户组
    • username − 用户名 (PUBLIC 是代表所有用户的简短形式)

删除用户:

查看用户权限:

多线程m3u8文件下载器 岁末寄语
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×