PostgreSQL中序列权限

序列的权限定义

官方文档中可以看到序列的所有权限为rwU，分别对应SELECT、UPDATE、USAGE；

对于序列，SELECT权限允许使用currval 函数

对于序列，UPDATE权限允许使用 nextval 和 setval 函数

对于序列，USAGE权限允许使用currval 和 nextval 函数

序列权限测试

序列seq_temp的owner为test用户，未给yhru用户赋权时，执行序列函数提示无权限

test_db=> \ds+ seq_temp
                        List of relations
 Schema |   Name   |   Type   | Owner |    Size    | Description
--------+----------+----------+-------+------------+-------------
 public | seq_temp | sequence | test  | 8192 bytes |
(1 row)

test_db=> \c - yhru
You are now connected to database "test_db" as user "yhru".
test_db=> select currval('seq_temp');
ERROR:  permission denied for sequence seq_temp
test_db=> select nextval('seq_temp');
ERROR:  permission denied for sequence seq_temp
test_db=> select setval('seq_temp',100);
ERROR:  permission denied for sequence seq_temp
test_db=>
复制

只赋权select时，可以执行currval函数，但是会报错，因为没有执行过nextval函数

test_db=> grant select on SEQUENCE seq_temp TO yhru ;
GRANT
test_db=> \dp+ seq_temp
                                Access privileges
 Schema |   Name   |   Type   | Access privileges | Column privileges | Policies
--------+----------+----------+-------------------+-------------------+----------
 public | seq_temp | sequence | test=rwU/test    +|                   |
        |          |          | yhru=r/test       |                   |
(1 row)

test_db=> \c - yhru
You are now connected to database "test_db" as user "yhru".
test_db=> select currval('seq_temp');
ERROR:  currval of sequence "seq_temp" is not yet defined in this session
test_db=>
复制

赋权select和update时，可以使用所有序列操作函数

test_db=> grant update on SEQUENCE seq_temp TO yhru ;
GRANT
test_db=> \c - yhru
You are now connected to database "test_db" as user "yhru".
test_db=> \dp+ seq_temp
                                Access privileges
 Schema |   Name   |   Type   | Access privileges | Column privileges | Policies
--------+----------+----------+-------------------+-------------------+----------
 public | seq_temp | sequence | test=rwU/test    +|                   |
        |          |          | yhru=rw/test      |                   |
(1 row)

test_db=> select setval('seq_temp',10);
 setval
--------
     10
(1 row)

test_db=> select currval('seq_temp');
 currval
---------
      10
(1 row)

test_db=> select nextval('seq_temp');
 nextval
---------
      11
(1 row)

test_db=>
复制

只赋权usage时，nextval和currval可以使用，setval不可以使用

test_db=> grant usage on SEQUENCE seq_temp TO yhru ;
GRANT
test_db=> \c - yhru
You are now connected to database "test_db" as user "yhru".
test_db=> \dp+ seq_temp
                                Access privileges
 Schema |   Name   |   Type   | Access privileges | Column privileges | Policies
--------+----------+----------+-------------------+-------------------+----------
 public | seq_temp | sequence | test=rwU/test    +|                   |
        |          |          | yhru=U/test       |                   |
(1 row)

test_db=> select nextval('seq_temp');
 nextval
---------
      12
(1 row)

test_db=> select currval('seq_temp');
 currval
---------
      12
(1 row)

test_db=> select setval('seq_temp',10);
ERROR:  permission denied for sequence seq_temp
test_db=>
复制

结论

如果需要有修改序列的权限可以直接赋权ALL

如果只是使用序列不需要修改可以赋权USAGE