# Database Security Patterns — Recipes ## Contents - Role setup (owner / app / read-only) - Default-deny grants for new tables - Parameterized queries by language - Safe dynamic identifiers - Row-level security for multi-tenancy - TLS connection strings - Audit logging - Gotchas ## Role setup (owner / app / read-only) ```sql -- PostgreSQL. Run as an admin role once per database. CREATE ROLE app_owner NOLOGIN; -- owns schema, runs migrations CREATE ROLE app_rw LOGIN PASSWORD :'pw_rw'; -- the application CREATE ROLE app_ro LOGIN PASSWORD :'pw_ro'; -- analytics / humans CREATE SCHEMA app AUTHORIZATION app_owner; GRANT USAGE ON SCHEMA app TO app_rw, app_ro; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw; GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro; ``` Migrations connect as a LOGIN role that is `SET ROLE app_owner` (or a login owner role); the app connects as `app_rw` only. ## Default-deny grants for new tables Grants above cover EXISTING tables only. Make future tables inherit: ```sql ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw; ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT SELECT ON TABLES TO app_ro; ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app GRANT USAGE, SELECT ON SEQUENCES TO app_rw; -- and revoke the PUBLIC default on the database itself: REVOKE ALL ON DATABASE appdb FROM PUBLIC; ``` ## Parameterized queries by language ```python # psycopg (Python) cur.execute("SELECT * FROM users WHERE email = %s AND status = %s", (email, status)) ``` ```javascript // node-postgres await pool.query("SELECT * FROM users WHERE email = $1", [email]); ``` ```python # SQLAlchemy raw text — parameters, never f-strings conn.execute(text("SELECT * FROM users WHERE email = :email"), {"email": email}) ``` Injection audit greps (each hit needs review): ```bash grep -rnE 'f"(SELECT|INSERT|UPDATE|DELETE)' --include='*.py' . grep -rnE '"\s*\+\s*\w+\s*\+?\s*"?.*(WHERE|FROM|VALUES)' --include='*.js' . grep -rn '\.format(.*SELECT' --include='*.py' . ``` ## Safe dynamic identifiers Parameters cannot bind table/column names. Allow-list, never interpolate input: ```python SORTABLE = {"created_at", "total", "status"} # fixed set, not user-defined if sort_col not in SORTABLE: raise ValueError(f"unsortable column: {sort_col}") cur.execute(f"SELECT * FROM orders ORDER BY {sort_col} LIMIT %s", (limit,)) ``` psycopg also offers `sql.Identifier()` for quoting — still allow-list first. ## Row-level security for multi-tenancy ```sql ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY; ALTER TABLE app.orders FORCE ROW LEVEL SECURITY; -- applies to owner too CREATE POLICY tenant_isolation ON app.orders USING (tenant_id = current_setting('app.tenant_id')::uuid); ``` Per-request, after taking a connection from the pool: ```sql SET app.tenant_id = '4fa2...'; -- SET LOCAL inside a transaction is safer ``` Use `SET LOCAL` + transaction per request so a pooled connection can never leak the previous request's tenant. ## TLS connection strings ```bash # verify-full = encrypt AND verify hostname against the server cert postgres://app_rw:pw@db.internal:5432/appdb?sslmode=verify-full&sslrootcert=/etc/ssl/rds-ca.pem # mysql client equivalent mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/ca.pem ... ``` `sslmode=require` encrypts but does NOT verify the server — MITM-able; use `verify-full` for anything crossing a network you don't own. ## Audit logging ```ini # postgresql.conf — minimum viable audit log_statement = 'ddl' # every schema/role change log_connections = on log_disconnections = on ``` ```sql -- pgaudit for compliance-grade auditing CREATE EXTENSION pgaudit; ALTER SYSTEM SET pgaudit.log = 'ddl, role, write'; SELECT pg_reload_conf(); ``` Ship logs off-host (the attacker who owns the DB host owns its logs). ## Gotchas - **Superusers and table owners bypass RLS** unless `FORCE ROW LEVEL SECURITY` is set — and superusers bypass it regardless. Test policies as `app_rw`. - **`GRANT ... ON ALL TABLES` is a snapshot**, not a subscription — without `ALTER DEFAULT PRIVILEGES`, every migration-created table is silently inaccessible (or worse, PUBLIC-readable). - **`sslmode=prefer` (the default) silently falls back to plaintext.** - **Connection pools + `SET app.tenant_id`** leak across requests unless you use `SET LOCAL` in a transaction or reset on checkout. - **`.pgpass`, shell history, and process lists** (`psql -c` with inline passwords, `ps` showing DSNs) are the classic secret leaks alongside VCS. - **`pg_hba.conf` `trust` entries** mean password-less login for anyone who can reach the socket — audit for them explicitly. - **Column encryption with `pgcrypto`** kills indexes on that column (equality possible via deterministic digest column; range queries are gone).