第03章：AI生成的 GRANT ALL PRIVILEGES，权限给得太大了

第03章：AI生成的 GRANT ALL PRIVILEGES，权限给得太大了

“你让 AI 帮你创建数据库用户，它给了你 GRANT ALL PRIVILEGES ON DATABASE。你的 API 服务用的是这个账号——一旦被 SQL 注入，攻击者可以 DROP 整个数据库。”

ℹ️ 版本说明：本章基于 PostgreSQL 18.4，参考最小权限原则（Principle of Least Privilege）。

3.1 AI默认会生成什么

你让 AI 帮你为应用创建数据库用户：

CREATE USER myapp WITH PASSWORD 'secret123';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myapp;

这个账号现在有什么权限？

  • ALL PRIVILEGES ON DATABASE 包含：CONNECT、CREATE、TEMP
  • 但不包含对表的权限（这是一个常见误解）
  • AI 通常接着给你：GRANT ALL ON ALL TABLES IN SCHEMA public TO myapp——这才真正给了所有表的读写删除权限

现在 myapp 账号可以：读、写、删除所有表、创建表、删除表，但不能 DROP DATABASE（这需要超级用户）。

对于一个只需要读写业务数据的 API 服务来说，这权限太大了。

3.2 AI通常遗漏的4个坑

⚠️ 坑1：API 账号不应该有 DDL 权限

API 服务只需要做 SELECT/INSERT/UPDATE/DELETE（DML），不需要 CREATE/DROP/ALTER（DDL）。

但 GRANT ALL 包含了所有权限，一旦 SQL 注入漏洞被利用：

-- 攻击者注入 SQL，通过 API 账号执行：
DROP TABLE users;
TRUNCATE orders;
CREATE USER attacker SUPERUSER;

最小权限方案：

-- 只给 DML 权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp;
-- 不给 CREATE、DROP、ALTER

⚠️ 坑2：没有区分读账号和写账号

对于有只读副本（Read Replica）的架构，读操作应该走只读副本，写操作走主库：

-- 读账号（只给 SELECT）
CREATE USER myapp_readonly WITH PASSWORD '...';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myapp_readonly;

-- 写账号（给 DML，不给 DDL）
CREATE USER myapp_writer WITH PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp_writer;

-- 迁移账号（给 DDL，仅在迁移时使用）
CREATE USER myapp_migration WITH PASSWORD '...';
GRANT ALL ON SCHEMA public TO myapp_migration;

⚠️ 坑3：密码明文在 SQL 文件里

AI 生成的 SQL 经常包含明文密码：

CREATE USER myapp WITH PASSWORD 'MyPassw0rd!';

这个 SQL 文件可能被提交到 Git 仓库，或者出现在 Bash 历史记录里。

更安全的方式：

# 方式1：通过环境变量传入（不在命令历史中暴露）
psql -c "CREATE USER myapp WITH PASSWORD '$DB_PASSWORD'"

# 方式2：SCRAM-SHA-256 认证（PostgreSQL 14+ 默认）
# 密码在传输和存储时都是加密的

⚠️ 坑4：忘记给序列（Sequences）权限

在 PostgreSQL 里，GRANT ALL ON ALL TABLES 不包含 Sequences（自增 ID 用的序列）。如果用户需要 INSERT，也需要对序列的 USAGE 权限：

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myapp;
-- 或者只给 USAGE（INSERT 时更新序列）
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO myapp;

如果少了这个，INSERT 操作会报错：

ERROR: permission denied for sequence users_id_seq

3.3 更好的提示词

提示词 P01：为应用服务生成最小权限账号

使用时机：为任何应用服务创建数据库账号

比默认多了什么：

  • 区分不同角色的权限
  • 不给 DDL 权限
  • 包含序列权限
帮我为 PostgreSQL 18.4 应用创建最小权限数据库账号，遵循最小权限原则。

应用架构：
- 主数据库（读写）+ 只读副本
- API 服务（需要读写业务数据）
- 数据分析服务（只读，可以跑复杂查询）
- 数据库迁移工具（需要 DDL 权限，仅在部署时使用）

数据库名：myapp_db
Schema：public
表：users, orders, products, sessions（所有业务表）

帮我创建以下账号（包含完整 SQL）：
1. myapp_api：
   - 只允许 SELECT、INSERT、UPDATE、DELETE
   - 不允许 CREATE、DROP、ALTER
   - 需要序列权限（用于自增 ID）
   - 连接数限制：最多20个并发连接

2. myapp_readonly：
   - 只允许 SELECT
   - 无法修改任何数据
   
3. myapp_migration（仅限迁移时使用）：
   - 需要 DDL 权限
   - 如何在迁移完成后降低权限/删除账号？

同时告诉我：
- 如何不在命令行历史里暴露密码（用环境变量）？
- 如何验证账号的实际权限：\dp tablename 怎么读？
- 新建表或视图后，需要重新 GRANT 吗？（DEFAULT PRIVILEGES 的用法）

基于 PostgreSQL 18.4。

提示词 P02：审计现有账号权限

使用时机：检查现有数据库账号是否权限过大

比默认多了什么：

  • 列出所有账号权限
  • 识别过大权限
  • 收缩权限的 SQL
帮我写审计 PostgreSQL 18.4 数据库账号权限的 SQL 查询。

我想检查：
1. 列出所有用户和角色：
   - SELECT * FROM pg_roles 的关键字段解读
   - 哪些账号是 SUPERUSER？（这应该尽量少）

2. 列出某个用户对所有表的权限：
   - 查询 information_schema.role_table_grants
   - 找出有 DELETE 或 TRUNCATE 权限的账号

3. 找出超出最小权限的账号：
   - API 账号（应该只有 DML，没有 DDL）
   - 是否有账号有 SUPERUSER 权限但不应该有？

4. 权限收缩操作：
   - 如何撤销特定权限：REVOKE DELETE ON ALL TABLES FROM myapp
   - 撤销权限时有哪些注意事项（是否会影响当前连接）？

给我可以直接运行的 SQL 查询。

基于 PostgreSQL 18.4。

提示词 P03：行级安全（Row Level Security）

使用时机：多租户场景，不同用户只能看到自己的数据

比默认多了什么：

  • RLS（Row Level Security）配置
  • 防止数据泄露的多租户隔离
帮我在 PostgreSQL 18.4 中实现行级安全（Row Level Security），用于多租户 SaaS 应用。

场景：
- 每个租户有独立的 tenant_id
- 同一个数据库，orders 表存了所有租户的数据
- API 应用账号（myapp_api）不应该能读到其他租户的数据

实现要求：
1. 开启 orders 表的 RLS：
   ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
   
2. 创建策略：当前连接的 tenant_id（通过 session 变量传入）只能看到自己的数据

3. 应用层如何传入当前租户 ID：
   - 在每次数据库连接后执行 SET app.current_tenant = '123'
   - PostgreSQL 如何读取这个变量：current_setting('app.current_tenant')
   
4. 管理账号绕过 RLS（BYPASS RLS）

5. RLS 的性能影响：
   - 每个查询都会加额外的 WHERE 条件，对索引的影响
   - 如何验证 RLS 策略在生效（EXPLAIN 里看不到策略，如何调试）

给我完整的 SQL 代码。

基于 PostgreSQL 18.4。

3.4 验收清单

检查项 验证方法 AI辅助
API 账号无 DDL 权限 用 API 账号执行 CREATE TABLE，应该失败 用 P01 重建权限
API 账号无 DROP 权限 用 API 账号执行 DROP TABLE，应该失败 让 AI 生成 REVOKE 语句
有序列权限 INSERT 操作不报 sequence permission denied 让 AI 添加 GRANT USAGE ON SEQUENCES
密码不在 Git 里 git grep 'PASSWORD' 无结果 让 AI 改用环境变量方案
超级用户最少 SELECT * FROM pg_roles WHERE rolsuper=true 只有 postgres 让 AI 检查 superuser 列表
DEFAULT PRIVILEGES 设置 新建表自动继承权限 让 AI 添加 ALTER DEFAULT PRIVILEGES

3.5 本章小结

如果你只记一件事：把 API 服务的数据库账号权限从 GRANT ALL 改为 GRANT SELECT, INSERT, UPDATE, DELETE，去掉 DDL 权限。这一个改动让 SQL 注入攻击的最坏结果从"删光所有表"变成"只能改业务数据"。

数据库账号的三个分级：

  1. 应用账号（DML only）：SELECT/INSERT/UPDATE/DELETE，API 服务用这个，不能改表结构
  2. 只读账号（SELECT only）：数据分析、报表服务，不能写任何数据
  3. 迁移账号（DDL）：只在数据库变更时使用，用完锁回或删除

→ 第4章：AI配置了 PostgreSQL 但没有连接池，高并发下连接数爆炸