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 配置,且要由实际创建对象的角色执行。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

授予表与序列权限

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通