pgsql-36 用户权限与安全

2025-09-25 约 1817 字预计阅读 4 分钟

1. 概述

数据库安全是多层防线：谁能连（认证）→ 连上后是谁（角色）→ 能做什么（权限）→ 能看哪些行（RLS）→ 传输是否加密（SSL）。本篇逐层讲解。

对比 MySQL：MySQL 的用户是 'user'@'host' 形式、权限用 GRANT 管理；PostgreSQL 统一用**角色（ROLE）**概念——用户和用户组都是角色，认证则由独立的 pg_hba.conf 文件控制（MySQL 把主机限制放在用户定义里，PG 把它抽到单独的认证配置）。

2. 角色与用户

PostgreSQL 里"用户"和"组"统称角色。有 LOGIN 属性的角色即"用户"。

-- 创建登录用户
CREATE ROLE app_user WITH LOGIN PASSWORD 'secret';
CREATE USER report_user WITH PASSWORD 'xxx';   -- USER = ROLE + LOGIN 简写

-- 创建不可登录的组角色，用于聚合权限
CREATE ROLE readonly;                           -- 无 LOGIN，当"权限组"用
GRANT readonly TO report_user;                  -- 把组权限赋给用户(成员关系)

-- 常见属性
CREATE ROLE admin WITH LOGIN SUPERUSER;         -- 超级用户(慎用)
CREATE ROLE dba WITH LOGIN CREATEDB CREATEROLE; -- 可建库/建角色
ALTER ROLE app_user WITH CONNECTION LIMIT 50;   -- 限制并发连接
ALTER ROLE app_user VALID UNTIL '2027-01-01';   -- 密码有效期

3. 权限管理：GRANT / REVOKE

3.1 权限层级

权限分层级：数据库 → schema → 表/序列/函数 → 列。

-- 数据库级
GRANT CONNECT ON DATABASE mydb TO readonly;

-- schema 级（必须有 USAGE 才能访问其中对象）
GRANT USAGE ON SCHEMA public TO readonly;

-- 表级
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;         -- 只读
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;      -- 读写
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user; -- 序列(自增)

-- 列级（细粒度）
GRANT SELECT (id, name) ON users TO report_user;   -- 只能看这两列

-- 撤销
REVOKE INSERT ON orders FROM app_user;

3.2 默认权限（易踩坑）

GRANT ... ON ALL TABLES 只对现有表生效，之后新建的表不会自动授权。用 ALTER DEFAULT PRIVILEGES 解决：

-- 今后 app_user 新建的表，自动让 readonly 可查
ALTER DEFAULT PRIVILEGES FOR ROLE app_user IN SCHEMA public
    GRANT SELECT ON TABLES TO readonly;

这是新手最常踩的坑：赋了权，过阵子新建的表又访问不了。务必配默认权限。

3.3 PUBLIC 与 schema 安全

PUBLIC 是隐含的"所有角色"。历史上 public schema 默认对所有人开放 CREATE，PG 15 起已收紧默认权限。

REVOKE ALL ON SCHEMA public FROM PUBLIC;   -- 收回默认开放

4. 认证：pg_hba.conf

控制"谁能从哪里、用什么方式连接"。规则从上到下匹配，第一条命中即生效。

# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       all                       peer          # 本机socket用系统用户
host    all       all       127.0.0.1/32    scram-sha-256 # 本地TCP密码
host    mydb      app_user  10.0.0.0/24     scram-sha-256 # 网段内密码认证
host    replication replicator 10.0.0.0/24  scram-sha-256 # 复制连接
hostssl mydb      all       0.0.0.0/0       scram-sha-256 # 强制SSL

认证方式：scram-sha-256（推荐，比老的 md5 更安全）、peer（本机系统用户）、cert（客户端证书）、trust（无密码，绝不用于生产）。
改后需 SELECT pg_reload_conf(); 生效。

5. 行级安全（RLS）

RLS 让不同用户查同一张表时只能看到属于自己的行，多租户系统的利器。MySQL 无此原生能力。

-- 启用行级安全
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 策略：每个用户只能看到 owner 等于自己的行
CREATE POLICY user_orders ON orders
    FOR ALL
    USING (owner = current_user);

-- 多租户：按会话变量隔离租户
CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.tenant_id')::int);

启用后，即使用户有表的 SELECT 权限，也只能看到策略允许的行——在数据库层强制隔离，比应用层加 WHERE 更可靠（无法被绕过）。

6. 传输加密与其他安全实践

# postgresql.conf 启用 SSL
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

安全实践清单（最小权限原则）：

应用用专用低权限角色，绝不用 superuser 跑业务。
按职责建组角色（readonly/readwrite/admin），用户加入组，而非逐个授权。
用 ALTER DEFAULT PRIVILEGES 管好未来对象权限。
认证用 scram-sha-256，禁用 trust。
生产强制 SSL（hostssl）。
密码设有效期、连接数限制。
多租户/敏感数据用 RLS 在库层隔离。
敏感字段用 pgcrypto 加密（见扩展插件总览）。

7. 面试高频问答

Q1：PostgreSQL 的用户和角色是什么关系？ 统一为角色（ROLE），有 LOGIN 属性的角色就是"用户"，无 LOGIN 的常当"权限组"用；GRANT 组角色给用户实现权限继承。CREATE USER 是 CREATE ROLE … LOGIN 的简写。

Q2：GRANT ON ALL TABLES 后新建的表为什么没权限？ ALL TABLES 只对执行时已存在的表生效。需用 ALTER DEFAULT PRIVILEGES 设置默认权限，让今后新建对象自动授权。

Q3：pg_hba.conf 的作用？和权限有什么区别？ 它控制认证——谁能从哪个地址用什么方式连接，从上到下匹配第一条命中的规则。权限（GRANT）控制连上之后能做什么。两者是不同层次。

Q4：什么是行级安全(RLS)？ 在表上定义策略，让用户即使有 SELECT 权限也只能看到符合策略的行，常用于多租户隔离。在数据库层强制，无法被应用绕过。MySQL 没有原生 RLS。

Q5：数据库安全的最佳实践有哪些？ 最小权限（应用用专用低权限角色）、组角色管理、默认权限、scram-sha-256 认证禁 trust、强制 SSL、密码有效期与连接限制、RLS 隔离、敏感字段加密。

8. 下一步

学习数据迁移方案，掌握异构数据库迁移。

目录