PostgreSQL 权限详解

一、角色（Role）管理

PostgreSQL 使用 角色 统一管理用户和组，角色可以拥有登录权限（即用户）或仅用于权限分组（即组）。

1. 创建角色

-- 创建普通角色（无登录权限）
CREATE ROLE group_developer;

-- 创建登录角色（用户）并设置密码
CREATE ROLE app_user WITH LOGIN PASSWORD 'password';

-- 创建超级用户（拥有所有权限）
CREATE ROLE super_user WITH SUPERUSER;

2. 角色属性

• LOGIN：允许角色登录数据库（即用户）。
• SUPERUSER：超级用户可绕过所有权限检查，创建 / 删除数据库、角色等。
• CREATEDB：允许创建数据库。
• CREATEROLE：允许创建和修改其他角色。
• INHERIT：默认继承父角色的权限（可显式关闭）。
• VALID UNTIL：角色有效期（如 VALID UNTIL '2024-12-31'）。

3. 角色权限继承

-- 将角色添加到组（继承组权限）
GRANT group_developer TO app_user;

-- 撤销角色继承
REVOKE group_developer FROM app_user;

二、数据库对象权限

PostgreSQL 的权限按对象类型划分，支持以下对象的权限管理：

1. 数据库（Database）权限

• CONNECT：允许连接到数据库。
• CREATE：允许在数据库中创建模式（Schema）。
• TEMPORARY（简写 TEMP）：允许创建临时表。
• OWNERSHIP：对象所有者权限（自动拥有所有权限）。

-- 授予连接数据库权限
GRANT CONNECT ON DATABASE mydb TO app_user;

-- 允许用户在数据库中创建模式
GRANT CREATE ON DATABASE mydb TO app_user;

2. 模式（Schema）权限

• USAGE：允许访问模式中的对象（如查询表），但不能创建新对象。
• CREATE：允许在模式中创建新对象（表、视图等）。

-- 授予模式访问权限
GRANT USAGE ON SCHEMA public TO app_user;

-- 允许在模式中创建对象
GRANT CREATE ON SCHEMA public TO app_user;

3. 表 / 视图（Table/View）权限

• SELECT：读取表数据。
• INSERT：插入新数据。
• UPDATE：更新数据（可指定列，如 UPDATE (col1, col2)）。
• DELETE：删除数据。
• TRUNCATE：清空表数据（需谨慎，比 DELETE 更高效）。
• REFERENCES：允许在该表上创建外键约束（针对列）。
• TRIGGER：允许在表上创建触发器。
• EXECUTE：对函数表（物化视图）的执行权限。

-- 授予表读写权限
GRANT SELECT, INSERT, UPDATE, DELETE ON table employees TO app_user;

-- 仅允许更新特定列
GRANT UPDATE (salary) ON table employees TO app_user;

-- 允许在表上创建外键
GRANT REFERENCES (id) ON table employees TO app_user;

4. 序列（Sequence）权限

• USAGE：允许使用序列的 NEXTVAL 和 CURRVAL 函数。
• CREATE：允许修改序列（如重置值）。
• SELECT：允许查询序列的当前值（CURRVAL）。

GRANT USAGE, SELECT ON SEQUENCE user_id_seq TO app_user;

5. 函数（Function）权限

• EXECUTE：允许调用函数。

GRANT EXECUTE ON FUNCTION calculate_salary() TO app_user;

6. 索引（Index）权限

• 索引权限与表权限绑定，创建索引的用户自动拥有索引权限，且无法单独授予索引权限（通过表权限间接控制）。

7. 外部表（Foreign Table）权限

• 与普通表类似，额外支持 TRUSTED 权限（允许外部数据包装器绕过验证）。

三、权限操作语法

1. 授予权限（GRANT）

-- 授予对象权限给角色
GRANT {权限列表} ON {对象} TO {角色 | PUBLIC};

-- 示例：授予表 employees 的 SELECT 权限给角色 app_user
GRANT SELECT ON employees TO app_user;

-- 授予模式 public 的所有权限给角色 group_developer
GRANT ALL PRIVILEGES ON SCHEMA public TO group_developer;

-- 授予权限时包含子对象（需 PostgreSQL 10+）
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;

2. 撤销权限（REVOKE）

REVOKE {权限列表} ON {对象} FROM {角色 | PUBLIC};

-- 示例：撤销 app_user 的 INSERT 权限
REVOKE INSERT ON employees FROM app_user;

-- 撤销所有权限
REVOKE ALL PRIVILEGES ON employees FROM app_user;

3. 查看权限

• \z（psql 命令）：查看表 / 视图权限。

  psql -d mydb -c "\z employees"
  

• 系统视图：

  -- 查看表权限
  SELECT * FROM information_schema.table_privileges WHERE table_name = 'employees';
  
  -- 查看角色属性
  SELECT * FROM pg_roles WHERE rolname = 'app_user';
  

四、特殊权限与默认设置

1. PUBLIC 角色

• PUBLIC 代表所有角色，对 PUBLIC 授权等同于对所有用户授权。

  -- 允许所有用户查询 public 模式下的表
  GRANT SELECT ON ALL TABLES IN SCHEMA public TO PUBLIC;
  

2. 所有权（Ownership）

• 对象创建者自动成为所有者，可通过 ALTER OWNER 更改：

  ALTER TABLE employees OWNER TO group_developer;
  

3. 默认权限（Default Privileges）

• 设置新创建对象的默认权限（需在会话或数据库级别配置）：

  -- 对未来创建的表，授予 group_developer 读写权限
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO group_developer;
  

五、权限检查流程

1. 超级用户：绕过所有权限检查，可访问任何对象。
2. 普通角色：
   • 检查是否拥有对象的对应权限（如 SELECT）。
   • 若角色通过继承（GRANT role TO role）获得权限，需确保父角色拥有该权限。
   • 外键约束检查：创建外键时，需拥有引用表的 REFERENCES 权限。

六、最佳实践

1. 最小权限原则：仅授予角色必要的权限（如只读角色不授予 INSERT）。
2. 使用角色组：通过创建组角色（如 dev_group、report_group）批量管理权限，避免重复授权。
3. 分离开发与生产环境：生产环境禁用 CREATE、TRUNCATE 等高危权限。
4. 定期审查权限：通过 \z 或系统视图检查异常权限配置。
5. 避免 PUBLIC 权限：谨慎对 PUBLIC 授权，防止未授权访问。