PostgreSQLNotes

第 28 章：安全治理

zjc 于 2026-01-28 发布

这是《PostgreSQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 数据库安全包括账号权限、网络认证、数据加密、审计、SQL 注入防护和敏感数据治理。

28.1 角色与权限

CREATE ROLE app_user LOGIN PASSWORD 'strong-password';
CREATE ROLE analyst_ro NOLOGIN;

GRANT CONNECT ON DATABASE shop TO app_user, analyst_ro;
GRANT USAGE ON SCHEMA app TO app_user, analyst_ro;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO analyst_ro;

默认权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA app
GRANT SELECT ON TABLES TO analyst_ro;

28.2 行级安全

ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON tenants
USING (tenant_id = current_setting('app.current_tenant')::bigint);

设置会话变量：

SET app.current_tenant = '1001';

RLS 由数据库强制隔离，适合多租户，但测试必须覆盖绕过场景和超级用户行为。

28.3 认证与 TLS

hostssl shop app_user 10.0.0.0/8 scram-sha-256

密码加密：

SHOW password_encryption;

建议：

  1. 使用 SCRAM-SHA-256；
  2. 密钥集中管理；
  3. 定期轮转；
  4. 最小网络暴露；
  5. 不允许任意公网访问。

28.4 SQL 注入

错误：

SELECT * FROM users WHERE email = '$email';

正确使用绑定参数：

PREPARE get_user(text) AS
SELECT * FROM users WHERE email = $1;

应用层使用 ORM 或驱动参数绑定，动态表名和排序字段必须白名单校验。

28.5 审计

可用扩展或日志方案：

log_connections = on
log_disconnections = on
log_statement = 'ddl'
log_min_duration_statement = 500

核心审计需求：

  1. 登录成功失败；
  2. 权限变更；
  3. DDL；
  4. 敏感表访问；
  5. 管理员操作；
  6. 连接来源。

28.6 敏感数据

CREATE EXTENSION IF NOT EXISTS pgcrypto;

SELECT crypt('password', gen_salt('bf', 12));

原则：

  1. 密码只存哈希；
  2. 手机号和证件号脱敏展示；
  3. 需要检索的敏感值可存 HMAC；
  4. 密钥不进数据库连接串；
  5. 备份也要加密。

28.7 Schema 安全

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

避免普通用户在 public 创建恶意对象，并控制 search_path：

ALTER ROLE app_user SET search_path = app, public;

本章小结

安全治理要坚持最小权限、强认证、网络收敛、参数绑定和审计留痕。多租户可使用 RLS，敏感数据要考虑展示、存储、检索和备份四个层面。

思考题

  1. 为什么应用账号要与管理员分离？
  2. RLS 的适用条件是什么？
  3. 如何防止 SQL 注入？
  4. 敏感字段如何安全检索？
  5. 审计日志至少保留哪些事件？