PostgreSQL 生产安全指南：权限体系、行级安全策略与 SSL 硬实战

-- 1. 应用层角色（禁止直接登录）
CREATE ROLE app_read NOLOGIN;         -- 只读查询
CREATE ROLE app_write NOLOGIN;        -- 读写
CREATE ROLE app_admin NOLOGIN;        -- DDL + DCL

-- 2. 业务功能角色（禁止直接登录，用于逻辑分组）
CREATE ROLE analytics NOLOGIN;        -- 数据分析
CREATE ROLE scheduler NOLOGIN;        -- 定时任务

-- 3. 登录用户（供应用程序使用）
CREATE USER app_backend WITH PASSWORD 'complex_password_123!' LOGIN;
CREATE USER app_worker  WITH PASSWORD 'another_password_456!' LOGIN;

-- 4. 角色继承
GRANT app_read TO app_backend;
GRANT app_write TO app_worker;
GRANT analytics TO app_backend;

-- Schema 级权限
GRANT USAGE ON SCHEMA app TO app_read;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app TO app_write;

-- 表级细粒度权限（拒绝 SELECT on password_hash）
GRANT SELECT(id, email, name, role) ON users TO app_read;
-- app_read 无法 SELECT password_hash 列

-- 行级更新权限（仅允许更新自己的记录，需配合 RLS）
-- 见下节

-- 限制用户对系统表的访问
REVOKE ALL ON DATABASE myapp FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- 清除不必要的默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA app REVOKE ALL ON TABLES FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA app GRANT SELECT ON TABLES TO app_read;

-- 启用 RLS（默认对表拥有者绕过，对普通用户生效）
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 创建策略：用户只能看到自己的订单
CREATE POLICY user_order_isolation ON orders
  FOR ALL
  TO app_backend
  USING (user_id = CURRENT_USER_ID());

-- 策略：管理员能看到所有
CREATE POLICY admin_all_orders ON orders
  FOR SELECT
  TO app_admin
  USING (TRUE);

-- 为每个请求设置 session 变量
SET app.current_user_id = '123';

-- RLS 策略读取 session 变量
CREATE POLICY user_order_isolation ON orders
  FOR ALL
  TO app_backend
  USING (
    user_id = NULLIF(current_setting('app.current_user_id', true), '')::INT
  );

prisma.$use(async (params, next) => {
  await prisma.$executeRaw`SET app.current_user_id = '${userId}'`;
  const result = await next(params);
  await prisma.$executeRaw`SET app.current_user_id = ''`;
  return result;
});

# postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'
ssl_ca_file = '/etc/ssl/certs/ca.crt'        # 客户端证书认证时必需

# 强制 SSL（拒绝明文连接）
ssl_min_protocol_version = 'TLSv1.2'
ssl_ciphers = 'HIGH:!aNULL:!MD5'

# 在 pg_hba.conf 中强制 SSL
hostssl myapp app_backend 10.0.1.0/24 scram-sha-256

# pg_hba.conf — 用客户端证书替代密码
hostssl myapp app_backend 10.0.1.0/24 cert

# 客户端连接字符串
DATABASE_URL="postgresql://app_backend@db.example.com:5432/myapp?sslmode=verify-full&sslcert=/path/to/client.crt&sslkey=/path/to/client.key&sslrootcert=/path/to/ca.crt"

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pgaudit'
pgaudit.log = 'write, ddl'         -- 审计写操作 + DDL
pgaudit.log_catalog = off          -- 不审计系统目录
pgaudit.log_parameter = on         -- 审计时记录参数值
pgaudit.log_relation = on          -- 对象级审计
pgaudit.role = 'auditor'           -- 对 auditor 角色审计

-- 安装 pgaudit 扩展
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- 审计敏感表的所有操作
CREATE AUDIT POLICY access_sensitive_table ON users
  FOR ALL
  EXECUTE FUNCTION pgaudit_ddl_command_end();

-- 查看审计日志（经过 log_destination 输出到文件后解析）
-- 典型输出：
-- 2025-08-01 10:00:00 UTC LOG:  AUDIT: SESSION,1,1,WRITE,INSERT,TABLE,public.users,,"INSERT INTO users ... (bob)"

# 1. 查看当前版本
SELECT version(); -- PostgreSQL 17.4

# 2. 检查是否有安全更新
# 访问 https://www.postgresql.org/docs/current/release.html

# 3. 升级（小版本原地升级）
sudo apt update && sudo apt install postgresql-17  # Debian/Ubuntu
brew upgrade postgresql@17                          # macOS

# 4. 重启
sudo systemctl restart postgresql

# 5. 验证
SELECT version();