# name: securing-databases description: 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.

# Securing Databases

# When to use / when NOT to use

• 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.
• 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.

# Core rules

1. 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.
   • ✅ GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;
   • ❌ postgres://postgres:... in the application config
2. Parameterized queries are the ONLY injection defense. Escaping, sanitizing, or quoting by hand is always a finding.
   • ✅ cur.execute("SELECT * FROM users WHERE email = %s", (email,))
   • ❌ f"SELECT * FROM users WHERE email = '{email}'" — even for "trusted" input
   • Identifiers (table/column names) can't be parameters: allow-list them from a fixed set.
3. 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.
4. TLS on every connection that leaves the host. PostgreSQL: sslmode=verify-full in the DSN (not the default prefer, which downgrades silently).
5. 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.
6. Multi-tenant data gets row-level security. RLS with a tenant_id policy enforced in the database beats every "remember the WHERE clause" convention.
7. 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.
8. Encrypt sensitive data at rest. Default: full-disk/volume encryption; column-level (pgcrypto) only for fields needing protection from DB admins themselves.

# Workflow

1. Inventory: current roles and grants, where credentials are stored, how connections reach the DB (network path, TLS), what data is sensitive.
2. 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).
3. Apply RLS/audit/encryption (rules 6–8) as the data model requires.
4. 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.

# Edge cases & failure modes

• ORM in use → ORMs parameterize by default, but raw()/text()/WHERE string fragments reintroduce injection; audit those call sites specifically.
• Legacy app owns everything as one role → migrate incrementally: create the new roles, move the app connection first, keep owner for migrations only.
• RLS and the owner role → table owners and superusers BYPASS RLS unless the policy role is FORCEd; test RLS as the app role, never as owner.
• Secret already committed to git → rotate immediately; treat history rewriting as cleanup, not remediation.
• Managed databases → provider handles disk encryption and network; rules 1–4, 6–7 remain fully yours.

# References

Grants, RLS policies, TLS DSNs, and audit setup: see references/patterns.md