database-security
Compare original and translation side by side
🇺🇸
Original
English🇨🇳
Translation
ChineseSecurity Audit
安全审计
Database security auditor specialized in Row Level Security (RLS) enforcement, Zero-Trust database architecture, and forensic audit trails. Focuses on Supabase, Postgres, and Convex data layer security. For general application security (OWASP Top 10, auth patterns, security headers, input validation), use the skill instead.
security专注于行级安全（RLS）实施、零信任数据库架构和取证审计追踪的数据库安全审计工具。重点关注Supabase、Postgres和Convex数据层安全。如果是通用应用安全需求（如OWASP Top 10、认证模式、安全标头、输入验证），请使用技能。
securityQuick Reference
快速参考
| Need | Approach |
|---|---|
| RLS enforcement | Enable on every public table; separate policies per operation |
| RLS performance | Index RLS columns; wrap auth.uid() in (select ...) subselect |
| Zero-Trust DB | Micro-segmentation, identity propagation, TLS enforcement |
| Supabase auth in RLS | Use (select auth.uid()) and auth.jwt(); never auth.role() |
| Convex auth guards | Call ctx.auth.getUserIdentity() in every public function |
| JIT access | Time-bound grants that expire automatically |
| Audit trails | Database triggers with immutable audit_log table |
| PGAudit | Extension for statement-level and object-level SQL auditing |
| Service role safety | Never use service_role key in client-side code |
| Views and RLS | Use security_invoker = true (Postgres 15+) to enforce RLS |
| Schema segmentation | Separate public, private, and audit schemas |
| Database compliance | RLS + audit logging + encryption satisfies multiple frameworks |
| 需求 | 实现方法 |
|---|---|
| RLS实施 | 在所有公开表上启用；为每个操作设置独立策略 |
| RLS性能优化 | 为RLS相关列建立索引；将auth.uid()包裹在(select ...)子查询中 |
| 零信任数据库 | 微分段、身份传播、强制启用TLS |
| Supabase RLS中的认证 | 使用(select auth.uid())和auth.jwt(); 切勿使用auth.role() |
| Convex认证防护 | 在所有公开函数中调用ctx.auth.getUserIdentity() |
| 即时（JIT）访问控制 | 自动过期的限时权限授予 |
| 审计追踪 | 搭配不可变audit_log表的数据库触发器 |
| PGAudit配置 | 用于语句级和对象级SQL审计的扩展 |
| 服务角色安全 | 切勿在客户端代码中使用service_role密钥 |
| 视图与RLS | 使用security_invoker = true（Postgres 15+）来强制执行RLS |
| 架构分段 | 分离public、private和audit架构 |
| 数据库合规性 | RLS + 审计日志 + 加密可满足多种合规框架要求 |
Audit Protocol
审计流程
Follow this sequence when performing a database security audit:
- Attack Surface Mapping: Identify all entry points to the data layer (public APIs, internal dashboards, AI agents, cron jobs)
- RLS Coverage Check: Query pg_tables to verify every public schema table has RLS enabled and appropriate policies
- Policy Review: Check for logical bypasses, missing WITH CHECK clauses, overly permissive FOR ALL policies
- Service Role Audit: Search client code for service_role key exposure; verify it only appears in server-side code
- Function Audit: Check for security definer functions in exposed schemas and Convex functions missing auth guards
- Access Simulation: Execute queries as anon and authenticated roles to verify enforcement
- View Audit: Verify views use security_invoker = true or are not in exposed schemas
- Audit Trail Verification: Confirm triggers or PGAudit capture all security-relevant operations
- Compliance Validation: Map database controls against applicable regulatory frameworks
执行数据库安全审计时，请遵循以下步骤：
- 攻击面映射：识别数据层的所有入口点（公开API、内部仪表板、AI Agent、定时任务）
- RLS覆盖检查：查询pg_tables以验证所有public架构表均已启用RLS并配置了合适的策略
- 策略审查：检查是否存在逻辑绕过、缺失WITH CHECK子句、权限过度宽松的FOR ALL策略
- 服务角色审计：在客户端代码中搜索service_role密钥泄露情况；验证其仅出现在服务端代码中
- 函数审计：检查暴露架构中的security definer函数以及缺失认证防护的Convex函数
- 访问模拟：以anon和已认证角色执行查询，验证策略是否生效
- 视图审计：验证视图是否设置了security_invoker = true，或未处于暴露架构中
- 审计追踪验证：确认触发器或PGAudit捕获了所有与安全相关的操作
- 合规性验证：将数据库控制措施与适用的监管框架进行映射
Security Principles
安全原则
| Principle | Database Application |
|---|---|
| Defense in Depth | RLS + application checks + schema segmentation |
| Least Privilege | Minimal GRANT per role; anon gets near-zero access |
| Zero Trust | Verify identity at DB level even for internal requests |
| Secure by Default | RLS enabled on creation; default-deny when no policy |
| Fail Securely | Postgres default-deny on RLS; generic error responses |
| Assume Breach | Design assuming attacker has a valid JWT |
| 原则 | 数据库应用实践 |
|---|---|
| 纵深防御 | RLS + 应用层检查 + 架构分段 |
| 最小权限 | 为每个角色授予最小权限；anon角色仅拥有极少量访问权限 |
| 零信任 | 即使是内部请求，也要在数据库层面验证身份 |
| 默认安全 | 创建表时默认启用RLS；无策略时默认拒绝访问 |
| 安全失效 | Postgres在RLS下默认拒绝访问；返回通用错误响应 |
| 假设已泄露 | 基于攻击者已获取有效JWT的前提进行设计 |
Anti-Patterns
反模式
| Anti-Pattern | Risk |
|---|---|
| Security by obscurity (UUIDs only) | Attackers enumerate IDs via IDOR |
| Anon role with SELECT on sensitive tables | Public data exposure via Supabase API |
| RLS columns without indexes | Production performance degradation (100x+) |
| Frontend-only permission checks | Attackers bypass via direct API calls |
| Standing admin privileges | Excessive blast radius if compromised |
| service_role key in client-side code | Bypasses all RLS policies completely |
| FOR ALL policies instead of per-operation | Unintended write access through broad rule |
| Security definer functions in public schema | Functions callable from API, bypass RLS |
| Views without security_invoker | Views bypass RLS silently |
| 反模式 | 风险 |
|---|---|
| 通过模糊性实现安全（仅依赖UUID） | 攻击者可通过IDOR枚举ID |
| anon角色拥有敏感表的SELECT权限 | 敏感数据通过Supabase API公开暴露 |
| RLS相关列未建立索引 | 生产环境性能大幅下降（100倍以上） |
| 仅在前端进行权限检查 | 攻击者可通过直接调用API绕过检查 |
| 长期管理员权限 | 若权限泄露，影响范围极大 |
| 客户端代码中使用service_role密钥 | 完全绕过所有RLS策略 |
| 使用FOR ALL策略而非分操作策略 | 宽泛的规则可能导致意外的写入权限 |
| 公开架构中的security definer函数 | 函数可通过API调用，绕过RLS |
| 未设置security_invoker的视图 | 视图会静默绕过RLS |
Common Mistakes
常见错误
| Mistake | Correct Pattern |
|---|---|
| Using auth.uid() = user_id without wrapping in (select ...) | Use (select auth.uid()) = user_id so Postgres caches the result via initPlan |
| Using FOR ALL instead of separate per-operation policies | Create separate SELECT, INSERT, UPDATE, DELETE policies for clarity and safety |
| Leaving anon role with SELECT on sensitive tables | Restrict anon access; require authenticated role for sensitive data |
| Relying on UUIDs as the only access control | Enforce RLS policies and explicit auth checks alongside unique identifiers |
| No index on columns used in RLS USING clauses | Add B-tree indexes on all columns referenced in RLS policy expressions |
| Convex function missing ctx.auth.getUserIdentity() call | Every public query and mutation must validate identity before accessing data |
| Using service_role key in client-side code | Use anon key client-side; service_role only in server-side functions |
| Views bypassing RLS without security_invoker | Set security_invoker = true on views in Postgres 15+ |
| Security definer functions in exposed schemas | Place security definer functions in non-exposed schemas with search_path = '' |
| No audit logging for security-relevant database events | Use triggers and PGAudit to capture all data access and modifications |
| 错误 | 正确实践 |
|---|---|
| 未用(select ...)包裹就使用auth.uid() = user_id | 使用(select auth.uid()) = user_id，以便Postgres通过initPlan缓存结果 |
| 使用FOR ALL策略而非独立的分操作策略 | 为SELECT、INSERT、UPDATE、DELETE分别创建独立策略，确保清晰性和安全性 |
| 允许anon角色拥有敏感表的SELECT权限 | 限制anon角色访问；敏感数据需已认证角色才能访问 |
| 仅依赖UUID作为唯一访问控制手段 | 在使用唯一标识符的同时，强制执行RLS策略和显式认证检查 |
| RLS USING子句中引用的列未建立索引 | 为RLS策略表达式中引用的所有列添加B树索引 |
| Convex函数未调用ctx.auth.getUserIdentity() | 所有公开查询和变更操作必须在访问数据前验证身份 |
| 客户端代码中使用service_role密钥 | 客户端使用anon密钥；service_role仅在服务端函数中使用 |
| 视图未设置security_invoker导致绕过RLS | 在Postgres 15+版本的视图上设置security_invoker = true |
| 暴露架构中的security definer函数 | 将security definer函数放置在非暴露架构中，并设置search_path = '' |
| 未对安全相关数据库事件进行审计日志记录 | 使用触发器和PGAudit捕获所有数据访问和修改操作 |
Relationship to Security Skill
与Security技能的关系
The skill covers general application security: OWASP Top 10, authentication patterns, input validation, security headers, and compliance overviews. This skill complements it by focusing on database-layer concerns: RLS policy design and performance, Supabase/Postgres-specific patterns, Convex auth guards, PGAudit configuration, and database-specific compliance implementations (SQL functions for GDPR erasure, HIPAA PHI audit triggers, etc.).
application-securitydatabase-securityapplication-securitydatabase-securityDelegation
任务委派
- Verify RLS enforcement with access simulations: Use agent to run anonymous and authenticated queries against every public table
Task - Audit Convex functions for missing auth guards: Use agent to scan all query and mutation handlers for getUserIdentity calls
Explore - Design zero-trust database architecture: Use agent to map schemas, access policies, JIT grants, and audit log design
Plan - Generate database compliance evidence: Use agent to run audit queries and produce compliance reports
Task
- 通过访问模拟验证RLS实施：使用Agent对所有公开表执行匿名和已认证查询
Task - 审计Convex函数是否缺失认证防护：使用Agent扫描所有查询和变更处理程序，检查是否存在getUserIdentity调用
Explore - 设计零信任数据库架构：使用Agent映射架构、访问策略、JIT权限授予和审计日志设计
Plan - 生成数据库合规性证据：使用Agent运行审计查询并生成合规报告
Task
References
参考资料
- rls-performance.md -- RLS policy performance, initPlan caching, stable functions, separate policies, EXPLAIN benchmarking
- zero-trust-database.md -- Micro-segmentation, identity propagation, connection security, JIT access controls
- audit-logging.md -- Trigger-based auditing, PGAudit extension and log classes, log integrity, tamper-proof storage
- convex-security.md -- Identity validation, manual RLS in functions, granular functions, role-based access via JWT claims
- threat-modeling.md -- STRIDE applied to database access, RLS bypass vectors, data layer trust boundaries
- application-security.md -- Service role management, schema exposure, security definer functions, views and RLS
- compliance-frameworks.md -- Database-specific GDPR, HIPAA, SOC2, PCI-DSS requirements and SQL implementations
- rls-performance.md -- RLS策略性能、initPlan缓存、稳定函数、独立策略、EXPLAIN基准测试
- zero-trust-database.md -- 微分段、身份传播、连接安全、JIT访问控制
- audit-logging.md -- 基于触发器的审计、PGAudit扩展与日志类别、日志完整性、防篡改存储
- convex-security.md -- 身份验证、函数中的手动RLS、细粒度函数、基于JWT声明的角色访问控制
- threat-modeling.md -- STRIDE模型在数据库访问中的应用、RLS绕过向量、数据层信任边界
- application-security.md -- 服务角色管理、架构暴露、security definer函数、视图与RLS
- compliance-frameworks.md -- 数据库特定的GDPR、HIPAA、SOC2、PCI-DSS要求及SQL实现