结论:

usage:  对应 nextval 执行权限
select: 对应 select * from <sequence_name>; 执行权限
update: 对应 setval 执行权限
复制

查看 SEQUENCE 相关的权限

GRANT { { USAGE | SELECT | UPDATE }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { SEQUENCE sequence_name [, ...]
         | ALL SEQUENCES IN SCHEMA schema_name [, ...] }
    TO role_specification [, ...] [ WITH GRANT OPTION ]

复制

实例

psql
(postgres)test3=# create sequence test_id_seq;
CREATE SEQUENCE
(postgres)test3=# create user test with password 'test';
CREATE ROLE
(postgres)test3=# \du test 
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 test   |            | {}

(postgres)test3=# \c - test 
You are now connected to database "test3" as user "test".
(postgres)test3=> \dp+ test_id_seq
                                 Access privileges
 Schema |    Name     |   Type   | Access privileges | Column privileges | Policies 
--------+-------------+----------+-------------------+-------------------+----------
 public | test_id_seq | sequence |                   |                   | 
(1 row)

-- 默认 新建的用户对序列无 usage/select/update 权限
(postgres)test3=> select * from test_id_seq; 
ERROR:  permission denied for sequence test_id_seq
(postgres)test3=> 
(postgres)test3=> \c - postgres 
You are now connected to database "test3" as user "postgres".
(postgres)test3=# grant select on SEQUENCE test_id_seq to test ;
GRANT
(postgres)test3=# \dp+ test_id_seq
                                  Access privileges
 Schema |    Name     |   Type   |  Access privileges  | Column privileges | Policies 
--------+-------------+----------+---------------------+-------------------+----------
 public | test_id_seq | sequence | postgres=rwU/postgres+|                   | 
        |             |          | test=r/postgres   |                   | 
(1 row)
-- select 对应的 select * from  <sequence_name>; 
(postgres)test3=# 
(postgres)test3=# \c - test 
You are now connected to database "test3" as user "test".
(postgres)test3=> select * from test_id_seq ;
 last_value | log_cnt | is_called 
------------+---------+-----------
         75 |       8 | t
(1 row)

(postgres)test3=> select nextval('test_id_seq');
ERROR:  permission denied for sequence test_id_seq
(postgres)test3=> 
(postgres)test3=> 
(postgres)test3=> select setval('test_id_seq',10,false);
ERROR:  permission denied for sequence test_id_seq
(postgres)test3=> 

-- usage 对应的是 nextval 执行权限
(postgres)test3=> \c - postgres
You are now connected to database "test3" as user "postgres".
(postgres)test3=# grant usage on SEQUENCE test_id_seq to test ;
GRANT
(postgres)test3=# \c - test 
You are now connected to database "test3" as user "test".
(postgres)test3=> select setval('test_id_seq',10,false);
ERROR:  permission denied for sequence test_id_seq
(postgres)test3=> select nextval('test_id_seq');
 nextval 
---------
      76
(1 row)

(postgres)test3=> select nextval('test_id_seq');
 nextval 
---------
      77
(1 row)


-- update 对应的是 setval 权限
(postgres)test3=> \c - postgres 
You are now connected to database "test3" as user "postgres".
(postgres)test3=# grant update on SEQUENCE test_id_seq  to test ;
GRANT
(postgres)test3=# \c - test 
You are now connected to database "test3" as user "test".
(postgres)test3=> select * from test_id_seq;
 last_value | log_cnt | is_called 
------------+---------+-----------
         77 |      31 | t
(1 row)

(postgres)test3=> select nextval('test_id_seq');
 nextval 
---------
      78
(1 row)

(postgres)test3=> select nextval('test_id_seq');
 nextval 
---------
      79
(1 row)

(postgres)test3=> select setval('test_id_seq',100,false);
 setval 
--------
    100
(1 row)

(postgres)test3=> select nextval('test_id_seq');
 nextval 
---------
     100
(1 row)
(postgres)test3=> exit
复制