postgresql 库级表级权限说明

postgresql 权限详解
角色
前言
PostgreSQL使用角色的概念管理数据库访问权限。根据角色的设置方式，可以将角色视为数据库用户或数据库用户组。角色可以拥有数据库对象(例如，表和函数)，并可以将对这些对象的权限分配给其他角色，以控制谁有权访问哪些对象。此外，还可以将角色的成员资格授予另一个角色，从而允许成员角色使用分配给另一个角色的权限。

角色的概念包含了“用户”和“组”的概念。在8.1之前的PostgreSQL版本中，用户和组是不同类型的实体，但现在只有角色。任何角色都可以充当用户、组或两者。

角色包含用户和组两个概念，具有login权限的角色称之为用户。任何角色(带或不带rolcanlogin)都可以有其他角色作为成员。

不允许A是B的成员，同时B也是A的成员。

public角色
public是一个特殊的角色，在元数据表中都查不到该角色

select rolname from pg_roles ;
rolname
---------------------------
postgres
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_read_server_files
pg_write_server_files
pg_execute_server_program
pg_signal_backend
(9 rows)

Time: 0.955 ms

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
但它确实是存在的，它默认拥有：

数据库：connect,temp/temprary权限，与模式无关

任何新建的数据库，系统会自动为public角色赋予connect和在任何schema下创建临时表的权限。

public模式：usage，create权限，与数据库无关

在任何新建的数据库的public schema下有usage和create的权限。

函数：execute权限，仅限于public模式下

language语言：usage权限，与模式无关

更为重要的是，public角色属于一个全局性的角色，这就意味着你所创建的角色都可以理解为是public角色组成员。

而且对public权限的继承完全不受NOINHERIT的控制，一旦创建了一个拥有login权限的角色，它会立即继承拥有上述权限，此时如果想通过revoke（比如revoke connect on database）来回收的话不会成功，因为这是通过组-组成员来继承的，这种继承权限无法通过单纯的对角色成员revoke掉，只能对组进行revoke，通过继承来实现回收。

实验1 用户自动继承public权限
创建用户dlq16050

postgres@HQ-SIT-kafka013:5432/postgres=# CREATE USER dlq16050 ;
CREATE ROLE
Time: 3.371 ms

1.
2.
3.
4.
5.
用户登录数据库

#没有为用户单独赋予任何额外权限
postgres@HQ-SIT-kafka013:5432/postgres=# \c postgres dlq16050
You are now connected to database "postgres" as user "dlq16050".
dlq16050@HQ-SIT-kafka013:5432/postgres=> \c test dlq16050
You are now connected to database "test" as user "dlq16050".
1.
2.
3.
4.
5.
创建表

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
可见，新建用户是自动继承public角色的权限的。

实验2 revoke继承public的权限
收回权限

postgres@HQ-SIT-kafka013:5432/postgres=# REVOKE CONNECT on DATABASE test from dlq16050;
REVOKE
Time: 3.215 ms
postgres@HQ-SIT-kafka013:5432/postgres=# \c test postgres
You are now connected to database "test" as user "postgres".
postgres@HQ-SIT-kafka013:5432/test=# REVOKE CREATE on SCHEMA public FROM dlq16050 ;
REVOKE
Time: 2.280 ms
1.
2.
3.
4.
5.
6.
7.
8.
验证权限

postgres@HQ-SIT-kafka013:5432/test=# \c test dlq16050
You are now connected to database "test" as user "dlq16050".
dlq16050@HQ-SIT-kafka013:5432/test=> CREATE TABLE t2(id int);
CREATE TABLE
Time: 8.312 ms
dlq16050@HQ-SIT-kafka013:5432/test=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | dlq16050
public | t2 | table | dlq16050
(2 rows)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
可见connect和create on schema public的权限并没有被收回。

实验3 revoke public角色的权限
回收connect权限

postgres@HQ-SIT-kafka013:5432/postgres=# REVOKE CONNECT on DATABASE test FROM PUBLIC ;
REVOKE
Time: 3.427 ms

postgres@HQ-SIT-kafka013:5432/postgres=# \c test dlq16050
FATAL: permission denied for database "test"
DETAIL: User does not have CONNECT privilege.
Previous connection kept

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
用户dlq16050已经没有登录test数据库的权限了。

回收create 权限

postgres@HQ-SIT-kafka013:5432/postgres=# GRANT CONNECT on DATABASE test TO dlq16050
postgres-# ;
GRANT
Time: 3.608 ms
postgres@HQ-SIT-kafka013:5432/postgres=# \c test postgres
You are now connected to database "test" as user "postgres".
postgres@HQ-SIT-kafka013:5432/test=# REVOKE ALL on SCHEMA public FROM PUBLIC ;
REVOKE
Time: 3.810 ms
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.

postgres@HQ-SIT-kafka013:5432/test=# \c test dlq16050
You are now connected to database "test" as user "dlq16050".
dlq16050@HQ-SIT-kafka013:5432/test=> CREATE TABLE t3(id int);
ERROR: no schema has been selected to create in
LINE 1: CREATE TABLE t3(id int);
^
Time: 2.187 ms
dlq16050@HQ-SIT-kafka013:5432/test=> CREATE TABLE public,t3(id int);
ERROR: syntax error at or near ","
LINE 1: CREATE TABLE public,t3(id int);
^
Time: 0.798 ms
dlq16050@HQ-SIT-kafka013:5432/test=> CREATE TABLE public.t3(id int);
ERROR: permission denied for schema public
LINE 1: CREATE TABLE public.t3(id int);
^
Time: 1.159 ms

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
dlq16050用户已经没有在public schema下创建表的权限了。

总结：

数据库中存在一个全局public角色，它不具体存在，但会影响到数据库中已有或将有角色的权限
public就像一个public组，数据库中所有角色默认继承其权限
数据库中角色继承的权限不能仅仅对角色进行revoke，这样是不会成功的，只有通过对其所在组的权限进行revoke才可
创建角色
在PostgreSQL中可以由超级用户或者拥有createrole权限的角色使用用create role命令定义一个全局（所有数据库均可用）的新角色。

具体语法：

CREATE USER/ROLE name [ [ WITH ] option [ ... ] ] : 关键词 USER,ROLE； name 用户或角色名；

where option can be:

SUPERUSER | NOSUPERUSER :超级权限，拥有所有权限，默认nosuperuser。
| CREATEDB | NOCREATEDB :建库权限，默认nocreatedb。
| CREATEROLE | NOCREATEROLE :建角色权限，拥有创建、修改、删除角色，默认nocreaterole。
| INHERIT | NOINHERIT :继承权限，可以把除superuser权限继承给其他用户/角色，默认inherit。
| LOGIN | NOLOGIN :登录权限，作为连接的用户，默认nologin，除非是create user（默认登录）。
| REPLICATION | NOREPLICATION :复制权限，用于物理或则逻辑复制（复制和删除slots），默认是noreplication。
| BYPASSRLS | NOBYPASSRLS :安全策略RLS权限，默认nobypassrls。
| CONNECTION LIMIT connlimit :限制用户并发数，默认-1，不限制。正常连接会受限制，后台连接和prepared事务不受限制。
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL :设置密码，密码仅用于有login属性的用户，不使用密码身份验证，则可以省略此选项。可以选择将空密码显式写为PASSWORD NULL。加密方法由配置参数password_encryption确定，密码始终以加密方式存储在系统目录中。
| VALID UNTIL 'timestamp' :密码有效期时间，不设置则用不失效。
| IN ROLE role_name [, ...] :新角色将立即添加为新成员。
| IN GROUP role_name [, ...] :同上
| ROLE role_name [, ...] :ROLE子句列出一个或多个现有角色，这些角色自动添加为新角色的成员。（这实际上使新角色成为“组”）。
| ADMIN role_name [, ...] :与ROLE类似，但命名角色将添加到新角色WITH ADMIN OPTION，使他们有权将此角色的成员资格授予其他人。
| USER role_name [, ...] :同上
| SYSID uid :被忽略，但是为向后兼容性而存在。
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
示例

postgres@HQ-SIT-kafka013:5432/test=# CREATE ROLE role1 NOSUPERUSER NOCREATEROLE NOCREATEDB NOINHERIT NOREPLICATION PASSWORD 'oracle';
CREATE ROLE
Time: 5.802 ms
postgres@HQ-SIT-kafka013:5432/test=# CREATE USER user1 in ROLE role1 PASSWORD 'oracle';
CREATE ROLE
Time: 5.751 ms
postgres@HQ-SIT-kafka013:5432/test=# CREATE ROLE role2 NOSUPERUSER NOCREATEROLE NOCREATEDB NOINHERIT NOREPLICATION LOGIN PASSWORD 'oracle';
CREATE ROLE
Time: 2.040 ms
postgres@HQ-SIT-kafka013:5432/test=# CREATE ROLE role3 in ROLE role2 LOGIN ;
CREATE ROLE
Time: 2.041 ms
postgres@HQ-SIT-kafka013:5432/test=# CREATE ROLE role4 NOSUPERUSER NOCREATEROLE NOCREATEDB NOINHERIT NOREPLICATION LOGIN PASSWORD 'oracle';
CREATE ROLE
Time: 2.031 ms
postgres@HQ-SIT-kafka013:5432/test=# CREATE ROLE role5 ADMIN role4 LOGIN ;
CREATE ROLE
Time: 2.345 ms
postgres@HQ-SIT-kafka013:5432/test=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
dlq16050 | | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
role1 | No inheritance, Cannot login | {} |
role2 | No inheritance | {} |
role3 | | {role2} |
role4 | No inheritance | {role5} |
role5 | | {} |
user1 | | {role1} |

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
set role
将当前会话的当前角色更改为所指定角色，修改成功后，当前会话的命令权限使用的是新角色权限。需要注意的是：指定角色必须是超级用户或者是当前会话角色所在的角色组。

示例

# 创建role6
postgres@HQ-SIT-kafka013:5432/test=# CREATE ROLE role6 ;
CREATE ROLE
Time: 2.517 ms
# 创建user2 指定组为role6,并且不继承role6的权限
postgres@HQ-SIT-kafka013:5432/test=# CREATE USER user2 in ROLE role6 noinherit password 'oracle';
CREATE ROLE
Time: 3.191 ms

# 目前user2是没有登录数据库test的权限的。
postgres@HQ-SIT-kafka013:5432/test=# \c test user2
FATAL: permission denied for database "test"
DETAIL: User does not have CONNECT privilege.
Previous connection kept

# 1. 赋予user2 connect 的权限
postgres@HQ-SIT-kafka013:5432/test=# GRANT CONNECT on DATABASE test to user2;
GRANT
Time: 2.469 ms
# 2. 赋予t1的select权限给user2
postgres@HQ-SIT-kafka013:5432/test=# GRANT SELECT ON t1 TO user2 ;
GRANT
Time: 3.366 ms
# 使用user2连接数据库test
postgres@HQ-SIT-kafka013:5432/test=# \c test user2
You are now connected to database "test" as user "user2".
user2@HQ-SIT-kafka013:5432/test=> \d
Did not find any relations.
user2@HQ-SIT-kafka013:5432/test=> SELECT * from t1;
ERROR: relation "t1" does not exist
LINE 1: SELECT * from t1;
^
Time: 1.248 ms
user2@HQ-SIT-kafka013:5432/test=> SELECT * from public.t1;
ERROR: permission denied for schema public
LINE 1: SELECT * from public.t1;
^
#########################################################
这里需要重点说明一下，明明赋予了select权限，可还是没有查询t1的权限。
需要先赋予用户schame的usage权限。
usage具体的解释，看完官方文件也没有完全理解。
个人感觉，可以把schmea看成是一个目录，usage权限就是允许角色进入到schema中
至于schema中的对象权限，还需单独的赋予。
#########################################################

user2@HQ-SIT-kafka013:5432/test=> SELECT * from t1 ;
id
----
(0 rows)

Time: 1.569 ms
user2@HQ-SIT-kafka013:5432/test=> SELECT * from t2 ;
ERROR: permission denied for table t2
Time: 1.338 ms
# 此时user2能查询t1,但是无法查询t2
# 4. 赋予role6查询t2的权限

user2@HQ-SIT-kafka013:5432/test=> \c test postgres
You are now connected to database "test" as user "postgres".
postgres@HQ-SIT-kafka013:5432/test=# GRANT SELECT ON public.t2 to role6 ;
GRANT
Time: 3.951 ms

# 5. set default role
user2@HQ-SIT-kafka013:5432/test=> \c test postgres
You are now connected to database "test" as user "postgres".
postgres@HQ-SIT-kafka013:5432/test=# GRANT SELECT ON public.t2 to role6 ;
GRANT
Time: 3.951 ms
postgres@HQ-SIT-kafka013:5432/test=# \c test user2
You are now connected to database "test" as user "user2".
user2@HQ-SIT-kafka013:5432/test=> SELECT * from t2;
ERROR: permission denied for table t2
Time: 3.690 ms
user2@HQ-SIT-kafka013:5432/test=> SET role role6;
SET
Time: 0.764 ms
user2@HQ-SIT-kafka013:5432/test=> SELECT * from t2;
ERROR: relation "t2" does not exist
LINE 1: SELECT * from t2;
^
Time: 1.396 ms
#### 这里同样是因为role6 没有public的usage权限
user2@HQ-SIT-kafka013:5432/test=> \c test postgres
You are now connected to database "test" as user "postgres".
postgres@HQ-SIT-kafka013:5432/test=# GRANT USAGE ON SCHEMA public to role6;
GRANT
Time: 2.920 ms
postgres@HQ-SIT-kafka013:5432/test=# \c test user2
You are now connected to database "test" as user "user2".
user2@HQ-SIT-kafka013:5432/test=> set role role6
test-> ;
SET
Time: 0.798 ms
user2@HQ-SIT-kafka013:5432/test=> SELECT * from t2;
id
----
(0 rows)

Time: 1.272 ms
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
set session authorization
该命令切换会话的连接用户。

postgres@HQ-SIT-kafka013:5432/test=# SELECT current_user,session_user;
current_user | session_user
--------------+--------------
postgres | postgres
(1 row)

Time: 1.463 ms
postgres@HQ-SIT-kafka013:5432/test=# set session AUTHORIZATION role6;
SET
Time: 0.792 ms
role6@HQ-SIT-kafka013:5432/test=> SELECT current_user,session_user;
current_user | session_user
--------------+--------------
role6 | role6
(1 row)

Time: 0.866 ms

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
后续再执行任何命令，都是使用role6角色

和set role的区别

set role 改变的是current_user,并没有改变session_user;

postgres@HQ-SIT-kafka013:5432/test=# SELECT current_user,session_user;
current_user | session_user
--------------+--------------
postgres | postgres
(1 row)

Time: 1.637 ms
postgres@HQ-SIT-kafka013:5432/test=# set role user2;
SET
Time: 0.796 ms
postgres@HQ-SIT-kafka013:5432/test=> SELECT current_user,session_user;
current_user | session_user
--------------+--------------
user2 | postgres
(1 row)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
权限

postgresql的权限分两大类，系统权限和对象权限。

系统权限
系统权限也可以说是角色的属性。

SUPERUSER|NOSUPERUSER
这些子句决定新角色是否是一个“超级用户”，它可以越过数据库内的所有访问限制。超级用户状态很危险并且只应该在确实需要时才用。要创建一个新超级用户，你必须自己是一个超级用户。如果没有指定，默认值是NOSUPERUSER。

CREATEDB|NOCREATEDB
这些子句定义一个角色创建数据库的能力。如果指定了CREATEDB，被定义的角色将被允许创建新的数据库。指定NOCREATEDB将否定一个角色创建数据库的能力。如果没有指定，默认值是NOCREATEDB。

CREATEROLE|NOCREATEROLE
这些子句决定一个角色是否被允许创建新的角色（也就是执行CREATE ROLE）。一个带有CREATEROLE特权的角色也能修改和删除其他角色。如果没有指定，默认值是NOCREATEROLE。

INHERIT|NOINHERIT
如果新的角色是其他角色的成员，这些子句决定新角色是否从那些角色中“继承”特权，把新角色作为成员的角色称为新角色的父角色。一个带有INHERIT属性的角色能够自动使用已经被授予给其直接或间接父角色的任何数据库特权。如果没有INHERIT，在另一个角色中的成员关系只会把SET ROLE的能力授予给那个其他角色，只有在这样做后那个其他角色的特权才可用。如果没有指定，默认值是INHERIT。

LOGIN|NOLOGIN
这些子句决定一个角色是否被允许登录，也就是在客户端连接期间该角色是否能被给定为初始会话认证名称。一个具有LOGIN属性的角色可以被考虑为一个用户。没有这个属性的角色对于管理数据库特权很有用，但是却不是用户这个词的通常意义。如果没有指定，默认值是NOLOGIN，不过当CREATE ROLE被通过CREATE USER调用时默认值会是LOGIN。

REPLICATION|NOREPLICATION
这些子句确定角色是否是复制角色。角色必须具有此属性（或成为超级用户）才能以复制模式（物理或逻辑复制）连接到服务器，和能够创建或删除复制插槽。一个具有REPLICATION属性的角色是一个具有非常高特权的角色，并且只应被用于确实需要复制的角色上。如果没有指定，默认值是 NOREPLICATION。

BYPASSRLS|NOBYPASSRLS
这些子句决定是否一个角色可以绕过每一条行级安全性（RLS）策略。默认是NOBYPASSRLS。注意 pg_dump 将默认把row_security设置为OFF，以确保一个表的所有内容被转储出来。如果运行 pg_dump 的用户不具有适当的权限，将会返回一个错误。超级用户和被转储表的拥有者总是可以绕过 RLS。

CONNECTION LIMIT connlimit
如果角色能登录，这指定该角色能建立多少并发连接。-1（默认值）表示无限制。请注意，只有正常连接才会计入此限制。预备事务和后台工作连接都不计入此限制。

[ ENCRYPTED ] PASSWORD password
设置角色的口令（口令只对具有LOGIN属性的角色有用，但是不管怎样你还是可以为没有该属性的角色定义一个口令）。如果你没有计划使用口令认证，你可以忽略这个选项。如果没有指定口令，口令将被设置为空并且该用户的口令认证总是会失败。也可以用PASSWORD NULL显式地写出一个空口令。

创建用户指定系统权限

postgres@HQ-SIT-kafka013:5432/test=# CREATE USER user1 CREATEDB createrole password 'oracle';
CREATE ROLE
Time: 2.222 ms
1.
2.
3.
如何查询一个用户有哪些系统权限？

#方法二
postgres@HQ-SIT-kafka013:5432/test=# \du+
List of roles
Role name | Attributes | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
dlq16050 | | {} |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} |
user1 | Create role, Create DB | {} |
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
修改用户系统权限

postgres@HQ-SIT-kafka013:5432/test=# ALTER USER user1 NOCREATEROLE nocreatedb superuser;
ALTER ROLE
Time: 2.283 ms
postgres@HQ-SIT-kafka013:5432/test=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
dlq16050 | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
user1 | Superuser | {}

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
对象权限
我们要先了解PostgreSQL的逻辑结构、以及与用户之间的关系。盗用德哥的图;来诠释下逻辑结构；

PostgreSQL逻辑结构有4层：实例->数据库->schema->数据库对象

可以看出用户不在PostgreSQL里面；是独立之外的object；这个跟Oracle逻辑结构不一致。它不属于某个数据库、或者某个schema。

若用户不是数据库属主的用户；要访问对象；该怎么办？有三步。这个类似走后门类似

首先你把数据库connect的权限赋予用户（国家允许）
再则你需要把对象所在的schema的usage权限赋予用户（省政府允许）
最后你需要把对象的权限赋予用户
database级权限
数据库级的权限有create、connect、temporary/temp三种

语法：

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE database_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
1.
2.
3.
connect
这个比较简单，用户拥有了数据库的connect权限，才能登录数据库

postgres@HQ-SIT-kafka013:5432/test=# create user user1 PASSWORD 'oracle';
CREATE ROLE
Time: 2.298 ms
postgres@HQ-SIT-kafka013:5432/test=# \c test user1
FATAL: permission denied for database "test"
DETAIL: User does not have CONNECT privilege.
Previous connection kept
1.
2.
3.
4.
5.
6.
7.
用户默认是没有数据库的connect权限的（除非没有回收PUBLIC角色的connect权限）

赋予connect权限

postgres@HQ-SIT-kafka013:5432/test=# GRANT CONNECT ON DATABASE test to user1 ;
GRANT
Time: 2.512 ms
postgres@HQ-SIT-kafka013:5432/test=# \c test user1
You are now connected to database "test" as user "user1".
1.
2.
3.
4.
5.
temporary/temp
在数据库中创建临时表的权限

# 默认没有创建临时表的权限

postgres@HQ-SIT-kafka013:5432/test=# \c test user1
You are now connected to database "test" as user "user1".
user1@HQ-SIT-kafka013:5432/test=> CREATE TEMPORARY TABLE tmp(id int);
ERROR: permission denied to create temporary tables in database "test"
LINE 1: CREATE TEMPORARY TABLE tmp(id int);
^
Time: 1.653 ms
1.
2.
3.
4.
5.
6.
7.
8.
9.
赋予temporary/temp权限

user1@HQ-SIT-kafka013:5432/test=> \c test postgres
You are now connected to database "test" as user "postgres".
postgres@HQ-SIT-kafka013:5432/test=# GRANT TEMPORARY on DATABASE test TO user1 ;
GRANT
Time: 3.328 ms
postgres@HQ-SIT-kafka013:5432/test=# \c test user1
You are now connected to database "test" as user "user1".
user1@HQ-SIT-kafka013:5432/test=> CREATE TEMPORARY TABLE tmp(id int);
CREATE TABLE
Time: 7.575 ms
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
create
赋予数据库create权限，只是允许用户在数据库下创建schema，并不包含创建其他对象的权限。

示例：

user1@HQ-SIT-kafka013:5432/test=> \c test postgres
You are now connected to database "test" as user "postgres".
postgres@HQ-SIT-kafka013:5432/test=# GRANT CREATE on DATABASE test TO user1 ;
GRANT
Time: 3.943 ms
postgres@HQ-SIT-kafka013:5432/test=# \c test user1
You are now connected to database "test" as user "user1".
user1@HQ-SIT-kafka013:5432/test=> CREATE SCHEMA user1;
CREATE SCHEMA
Time: 2.974 ms
user1@HQ-SIT-kafka013:5432/test=> \dn
List of schemas
Name | Owner
--------+----------
public | postgres
user1 | user1
(2 rows)

user1@HQ-SIT-kafka013:5432/test=> CREATE TABLE public.t3(id int);
ERROR: permission denied for schema public
LINE 1: CREATE TABLE public.t3(id int);
^
Time: 0.904 ms

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
user1用户并没有创建表的权限

但是user1用户有在schema user1下创建任何对象的权限，因为user1 schema的owner是用户user1。

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
ALL
对于数据库来说，ALL权限就是create、connect、temporary/temp这三个权限集合，并不是所有权限

user1@HQ-SIT-kafka013:5432/test=> select * from t1;
ERROR: relation "t1" does not exist
LINE 1: select * from t1;
^
Time: 1.169 ms

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
PUBLIC下有表t1，但是ALL权限并不包含对t1的任何权限。

后续其他对象的ALL权限也与这里类似，都是对象上的各类权限集合。

权限查看
我们如何知道，一个用户对某个数据库级有哪些权限呢？

# 方法二
postgres@HQ-SIT-kafka013:5432/test=# select datname,datacl from pg_database ;
datname | datacl
-----------+----------------------------------------------------------------
postgres | NULL
template1 | {=c/postgres,postgres=CTc/postgres}
template0 | {=c/postgres,postgres=CTc/postgres}
test | {postgres=CTc/postgres,dlq16050=c/postgres,user1=CTc/postgres}
(4 rows)

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
可以看到user1对test库的访问权限为user1=CTc/postgres

user1为被赋予权限的角色

CTc为权限

postgres为权限的赋予者

schema级权限
schema级权限包含usage和create两个

语法：

GRANT { { CREATE | USAGE } [, ...] | ALL [ PRIVILEGES ] }
ON SCHEMA schema_name [, ...]
TO role_specification [, ...] [ WITH GRANT OPTION ]
1.
2.
3.
usage
要访问schema中的对象，必须先拥有schema的usage权限。

postgres@HQ-SIT-kafka013:5432/test=# \c test postgres
You are now connected to database "test" as user "postgres".
postgres@HQ-SIT-kafka013:5432/test=# create schema schema1;
CREATE SCHEMA
Time: 3.255 ms

# 赋予usage权限
postgres@HQ-SIT-kafka013:5432/test=# GRANT USAGE ON schema schema1 to user1 ;
GRANT
Time: 2.646 ms

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
create
schema的create权限，允许用户在指定的schema中创建表、sequence、函数、存储过程、视图

赋予用户schema1的create权限

postgres@HQ-SIT-kafka013:5432/test=# GRANT CREATE on SCHEMA schema1 to user1 ;
GRANT
Time: 3.435 ms
1.
2.
3.
测试创建各类对象

postgres@HQ-SIT-kafka013:5432/test=# \c test user1
You are now connected to database "test" as user "user1".
user1@HQ-SIT-kafka013:5432/test=> \dn
List of schemas
Name | Owner
---------+----------
public | postgres
schema1 | postgres
user1 | user1
(3 rows)
# 创建表
user1@HQ-SIT-kafka013:5432/test=> CREATE TABLE schema1.t1(id int);
CREATE TABLE
Time: 9.818 ms
# 创建sequence
user1@HQ-SIT-kafka013:5432/test=> CREATE SEQUENCE schema1.seq1 ;
CREATE SEQUENCE
Time: 5.222 ms
# 创建函数
user1@HQ-SIT-kafka013:5432/test=> CREATE FUNCTION schema1.add(integer, integer) RETURNS integer
test-> AS 'select $1 + $2;'
test-> LANGUAGE SQL
test-> IMMUTABLE
test-> RETURNS NULL ON NULL INPUT;
CREATE FUNCTION
Time: 6.629 ms
# 创建存储过程
user1@HQ-SIT-kafka013:5432/test=> create table tbl(id int);
CREATE TABLE
Time: 6.261 ms
user1@HQ-SIT-kafka013:5432/test=> CREATE PROCEDURE schema1.insert_data(a integer, b integer)
LANGUAGE SQL
AS $$
INSERT INTO tbl VALUES (a);
INSERT INTO tbl VALUES (b);
$$;
CREATE PROCEDURE
Time: 4.602 ms
# 创建视图
user1@HQ-SIT-kafka013:5432/test=> CREATE VIEW schema1.v as select * from tbl;
CREATE VIEW
Time: 5.110 ms
user1@HQ-SIT-kafka013:5432/test=>

# 方法二

user1@HQ-SIT-kafka013:5432/test=> SELECT nspname,nspacl from pg_namespace
;
nspname | nspacl
--------------------+------------------------------------------
pg_toast | NULL
pg_catalog | {postgres=UC/postgres,=U/postgres}
information_schema | {postgres=UC/postgres,=U/postgres}
pg_temp_3 | NULL
pg_toast_temp_3 | NULL
public | {postgres=UC/postgres}
pg_temp_4 | NULL
pg_toast_temp_4 | NULL
user1 | NULL
schema1 | {postgres=UC/postgres,user1=UC/postgres}
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
schema1 的访问权限中有user1=UC/postgres

table级权限
表级权限包含SELECT,INSERT,DELTE,UPDATE,TRUNCATE,REFERENCES,TRIGGER

语法

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] table_name [, ...]
| ALL TABLES IN SCHEMA schema_name [, ...] }
TO role_specification [, ...] [ WITH GRANT OPTION ]
1.
2.
3.
4.
5.
读写权限
# public 下有t1,t2两个表，owner为dlq16050
postgres@HQ-SIT-kafka013:5432/postgres=# \c test
You are now connected to database "test" as user "postgres".
postgres@HQ-SIT-kafka013:5432/test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | t1 | table | dlq16050
public | t2 | table | dlq16050

# 默认情况下，user1没有这两个表的访问权限

postgres@HQ-SIT-kafka013:5432/test=# \c test user1
You are now connected to database "test" as user "user1".

user1@HQ-SIT-kafka013:5432/test=> select * from public.t1;
ERROR: permission denied for schema public
LINE 1: select * from public.t1;
^
Time: 1.779 ms

# 赋予t1表的SELECT,INSERT,DELTE,UPDATE,TRUNCATE给user1
postgres@HQ-SIT-kafka013:5432/test=# GRANT SELECT,INSERT,DELETE,UPDATE,TRUNCATE on PUBLIC.t1 to user1;
GRANT
Time: 3.837 ms

# 测试

user1@HQ-SIT-kafka013:5432/test=> select * from public.t1;
id
----
(0 rows)

Time: 2.536 ms
user1@HQ-SIT-kafka013:5432/test=> insert into public.t1 values(1);
INSERT 0 1
Time: 3.216 ms
user1@HQ-SIT-kafka013:5432/test=> update public.t1 set id=2 ;
UPDATE 1
Time: 2.938 ms
user1@HQ-SIT-kafka013:5432/test=> DELETE FROM public.t1 ;
DELETE 1
Time: 2.811 ms
user1@HQ-SIT-kafka013:5432/test=> TRUNCATE table public.t1;
TRUNCATE TABLE
Time: 5.637 ms

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
删除表与创建索引
除了 REFERENCES | TRIGGER这两个权限没有测试，我们会发现，缺少了drop 表和创建索引的权限

在postgresql数据库中，只有表的owner才能drop表，和在其上创建索引

user1@HQ-SIT-kafka013:5432/test=> DROP TABLE public.t1
test-> ;
ERROR: must be owner of table t1
Time: 1.091 ms
user1@HQ-SIT-kafka013:5432/test=> CREATE INDEX idx_t1_id on public.t1(id);
ERROR: must be owner of table t1
Time: 1.153 ms

1.
2.
3.
4.
5.
6.
7.
8.
9.
批量赋权
如何将schema下的所有表批量赋权于用户？

# 使用on all tables in schema
postgres@HQ-SIT-kafka013:5432/test=# GRANT SELECT,INSERT,DELETE,UPDATE,TRUNCATE on all tables in schema public to user1;
GRANT
Time: 3.233 ms

# 赋权后，user1拥有了t2的权限

user1@HQ-SIT-kafka013:5432/test=> SELECT * from public.t2
test-> ;
id
----
(0 rows)

Time: 0.927 ms

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
如果在public下新增一个表，权限会不会自动赋予user1呢？

postgres@HQ-SIT-kafka013:5432/test=# CREATE TABLE t3 (id int);
CREATE TABLE
Time: 5.620 ms

# user1是没有t3的权限的
user1@HQ-SIT-kafka013:5432/test=> SELECT * from public.t3
test-> ;
ERROR: permission denied for table t3
Time: 2.210 ms

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
on all tables in schema 与MySQL中的.* 赋权是不同的。只对现有对象生效。

默认权限
如何设置用户新建对象的默认权限？

ALTER DEFAULT PRIVILEGES
[ FOR { ROLE | USER } target_role [, ...] ]
[ IN SCHEMA schema_name [, ...] ]
abbreviated_grant_or_revoke
1.
2.
3.
4.
示例,将postgres用户未来在public schema下创建的所有表的select,insert,update,delete,truncate权限赋予user1

postgres@HQ-SIT-kafka013:5432/test=# alter default privileges for role postgres in schema public grant select,insert,update,delete,truncate on tables to user1 ;
ALTER DEFAULT PRIVILEGES
Time: 6.580 ms
# 创建新表
postgres@HQ-SIT-kafka013:5432/test=# CREATE TABLE public.t4 (id int);
CREATE TABLE
Time: 5.848 ms

# user1用户有新建表t4的权限

user1@HQ-SIT-kafka013:5432/test=> SELECT * from public.t4
test-> ;
id
----
(0 rows)

Time: 1.115 ms
# 但之前没有权限的表，仍然需要单独赋权
user1@HQ-SIT-kafka013:5432/test=> SELECT * from public.t3
;
ERROR: permission denied for table t3
Time: 0.702 ms

# 方法二

posted @ 2023-03-11 10:43 Cetus-Y 阅读(1986) 评论(0) 编辑收藏举报

刷新页面返回顶部

Cetus-Y

postgresql 库级表级权限说明

公告