PostgreSQL / 系统课时
授予表与序列权限
授予表与序列权限
本课为订单录入账户配置精确的对象权限。该账户可以插入和查询订单、只更新状态列,并能使用序列生成主键,但不能删除数据或修改客户名称。
学习目标
- 授予表级 SELECT 与 INSERT 权限
- 使用列级权限限制可更新字段
- 为依赖 nextval 的写入操作授予序列权限
表和序列是独立对象
表权限包括 SELECT、INSERT、UPDATE、DELETE 等。即使角色可以向表插入数据,如果主键默认值调用序列 nextval,而角色没有序列 USAGE 权限,插入仍会失败。
列级权限适合限制敏感字段修改。例如,只允许业务账户更新 status,而不允许修改 customer_name。表级 UPDATE 一旦授予,就会覆盖这种细粒度限制。
主示例:配置订单录入权限
下面的 SQL 需由超级用户在独立测试环境执行,且相关对象和角色名不能已存在。
sql
CREATE SCHEMA app_data;
CREATE SEQUENCE app_data.order_no_seq
START WITH 1001;
CREATE TABLE app_data.orders (
id bigint PRIMARY KEY
DEFAULT nextval('app_data.order_no_seq'),
customer_name text NOT NULL,
status text NOT NULL DEFAULT 'NEW'
);
ALTER SEQUENCE app_data.order_no_seq
OWNED BY app_data.orders.id;
CREATE ROLE order_clerk
LOGIN
PASSWORD 'ChangeMe_2026!';
REVOKE ALL ON SCHEMA app_data FROM PUBLIC;
REVOKE ALL ON TABLE app_data.orders FROM PUBLIC;
REVOKE ALL ON SEQUENCE app_data.order_no_seq FROM PUBLIC;
GRANT USAGE ON SCHEMA app_data TO order_clerk;
GRANT SELECT, INSERT ON TABLE app_data.orders TO order_clerk;
GRANT UPDATE (status) ON TABLE app_data.orders TO order_clerk;
GRANT USAGE, SELECT ON SEQUENCE app_data.order_no_seq TO order_clerk;
SET ROLE order_clerk;
INSERT INTO app_data.orders (customer_name)
VALUES ('Alice');
UPDATE app_data.orders
SET status = 'PAID'
WHERE id = 1001;
SELECT id, customer_name, status
FROM app_data.orders;
RESET ROLE;
SELECT
has_table_privilege(
'order_clerk',
'app_data.orders',
'SELECT'
) AS can_select,
has_column_privilege(
'order_clerk',
'app_data.orders',
'status',
'UPDATE'
) AS can_update_status,
has_column_privilege(
'order_clerk',
'app_data.orders',
'customer_name',
'UPDATE'
) AS can_update_customer,
has_sequence_privilege(
'order_clerk',
'app_data.order_no_seq',
'USAGE'
) AS can_use_sequence;预期结果
text
INSERT 0 1
UPDATE 1
id | customer_name | status
------+---------------+--------
1001 | Alice | PAID
can_select | can_update_status | can_update_customer | can_use_sequence
------------+-------------------+---------------------+-----------------
t | t | f | t结果表明 order_clerk 可以完成受控写入,但 customer_name 不可更新。由于没有 DELETE 权限,删除订单也会被拒绝。
常见授权遗漏
只授予表 INSERT 而忘记序列 USAGE,是使用 SERIAL、显式序列默认值时常见的失败原因。另一个错误是直接授予表级 UPDATE,导致所有列都能修改。
批量授权现有表可使用 GRANT ... ON ALL TABLES IN SCHEMA,但它不会自动覆盖未来新建对象。未来对象应结合 ALTER DEFAULT PRIVILEGES 配置,且要由实际创建对象的角色执行。
按自己的节奏,确认真正理解。