spb/ultra-sharp-agent-skills Public

1---2name: securing-databases3description: Hardens database security through least-privilege roles, injection-proof query patterns, secret management, TLS, encryption, and audit logging. Use when the user asks to secure a database, create database users/roles/grants, prevent SQL injection, review database credentials or connection strings, enable TLS for database connections, set up row-level security for multi-tenant data, or audit database access. Do not use for application-level authentication (sessions, JWT, OAuth) or general network firewall configuration beyond database exposure.4---56<!--7Author: Simon-Pierre Boucher8Contact: contact@spboucher.ai9-->1011# Securing Databases1213## When to use / when NOT to use14- **Use for:** roles and grants, SQL-injection defense, credential/secret handling, connection TLS, at-rest encryption, row-level security (RLS), audit logging, database network exposure.15- **Do NOT use for:** app-level auth (sessions/JWT/OAuth), OS hardening, or firewall design beyond keeping the DB port private. Backup encryption lives in `backing-up-databases`.1617## Core rules18191. **Least privilege, three roles minimum.** Owner (runs migrations, owns objects) ≠ app role (DML only) ≠ read-only role (analytics/humans). The app NEVER connects as owner or superuser.20   - ✅ `GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;`21   - ❌ `postgres://postgres:...` in the application config222. **Parameterized queries are the ONLY injection defense.** Escaping, sanitizing, or quoting by hand is always a finding.23   - ✅ `cur.execute("SELECT * FROM users WHERE email = %s", (email,))`24   - ❌ `f"SELECT * FROM users WHERE email = '{email}'"` — even for "trusted" input25   - Identifiers (table/column names) can't be parameters: allow-list them from a fixed set.263. **Secrets live in the environment or a secret manager — never in code, VCS, or logs.** Rotate any credential that ever hit a repository; deletion does not un-leak it.274. **TLS on every connection that leaves the host.** PostgreSQL: `sslmode=verify-full` in the DSN (not the default `prefer`, which downgrades silently).285. **The database port is never public.** Private network/VPC only; humans reach it via bastion or SSH tunnel; app reaches it via internal network. `0.0.0.0` bindings and `pg_hba.conf` `host all all 0.0.0.0/0` entries are findings.296. **Multi-tenant data gets row-level security.** RLS with a `tenant_id` policy enforced in the database beats every "remember the WHERE clause" convention.307. **Privileged access is logged.** At minimum: log DDL and role changes (`log_statement = 'ddl'`), plus `pgaudit` (or the engine's equivalent) when compliance requires read auditing.318. **Encrypt sensitive data at rest.** Default: full-disk/volume encryption; column-level (`pgcrypto`) only for fields needing protection from DB admins themselves.3233## Workflow34351. Inventory: current roles and grants, where credentials are stored, how connections reach the DB (network path, TLS), what data is sensitive.362. Fix the highest-severity gaps in this order: public exposure (rule 5) → superuser app connections (rule 1) → injection patterns (rule 2) → plaintext secrets (rule 3) → TLS (rule 4).373. Apply RLS/audit/encryption (rules 6–8) as the data model requires.384. **Validate:** connect as the app role and confirm a privileged action FAILS (`CREATE TABLE`, `DROP TABLE`, reading another tenant's rows under RLS). Grep the codebase for string-built SQL (`f"SELECT`, `"+ sql`, `format(` near queries) and report every hit. The task is not done until both checks run.3940## Edge cases & failure modes41- **ORM in use** → ORMs parameterize by default, but `raw()`/`text()`/`WHERE` string fragments reintroduce injection; audit those call sites specifically.42- **Legacy app owns everything as one role** → migrate incrementally: create the new roles, move the app connection first, keep owner for migrations only.43- **RLS and the owner role** → table owners and superusers BYPASS RLS unless the policy role is `FORCE`d; test RLS as the app role, never as owner.44- **Secret already committed to git** → rotate immediately; treat history rewriting as cleanup, not remediation.45- **Managed databases** → provider handles disk encryption and network; rules 1–4, 6–7 remain fully yours.4647## References48Grants, RLS policies, TLS DSNs, and audit setup: see [references/patterns.md](references/patterns.md)49