spb/ultra-sharp-agent-skills Public

1<!--2Author: Simon-Pierre Boucher3Contact: contact@spboucher.ai4-->56# Database Security Patterns — Recipes78## Contents9- Role setup (owner / app / read-only)10- Default-deny grants for new tables11- Parameterized queries by language12- Safe dynamic identifiers13- Row-level security for multi-tenancy14- TLS connection strings15- Audit logging16- Gotchas1718## Role setup (owner / app / read-only)1920```sql21-- PostgreSQL. Run as an admin role once per database.22CREATE ROLE app_owner NOLOGIN;                      -- owns schema, runs migrations23CREATE ROLE app_rw    LOGIN PASSWORD :'pw_rw';      -- the application24CREATE ROLE app_ro    LOGIN PASSWORD :'pw_ro';      -- analytics / humans2526CREATE SCHEMA app AUTHORIZATION app_owner;2728GRANT USAGE ON SCHEMA app TO app_rw, app_ro;29GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_rw;30GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA app TO app_rw;31GRANT SELECT ON ALL TABLES IN SCHEMA app TO app_ro;32```3334Migrations connect as a LOGIN role that is `SET ROLE app_owner` (or a login35owner role); the app connects as `app_rw` only.3637## Default-deny grants for new tables3839Grants above cover EXISTING tables only. Make future tables inherit:4041```sql42ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app43  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;44ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app45  GRANT SELECT ON TABLES TO app_ro;46ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app47  GRANT USAGE, SELECT ON SEQUENCES TO app_rw;48-- and revoke the PUBLIC default on the database itself:49REVOKE ALL ON DATABASE appdb FROM PUBLIC;50```5152## Parameterized queries by language5354```python55# psycopg (Python)56cur.execute("SELECT * FROM users WHERE email = %s AND status = %s",57            (email, status))58```5960```javascript61// node-postgres62await pool.query("SELECT * FROM users WHERE email = $1", [email]);63```6465```python66# SQLAlchemy raw text — parameters, never f-strings67conn.execute(text("SELECT * FROM users WHERE email = :email"), {"email": email})68```6970Injection audit greps (each hit needs review):7172```bash73grep -rnE 'f"(SELECT|INSERT|UPDATE|DELETE)' --include='*.py' .74grep -rnE '"\s*\+\s*\w+\s*\+?\s*"?.*(WHERE|FROM|VALUES)' --include='*.js' .75grep -rn '\.format(.*SELECT' --include='*.py' .76```7778## Safe dynamic identifiers7980Parameters cannot bind table/column names. Allow-list, never interpolate input:8182```python83SORTABLE = {"created_at", "total", "status"}   # fixed set, not user-defined84if sort_col not in SORTABLE:85    raise ValueError(f"unsortable column: {sort_col}")86cur.execute(f"SELECT * FROM orders ORDER BY {sort_col} LIMIT %s", (limit,))87```8889psycopg also offers `sql.Identifier()` for quoting — still allow-list first.9091## Row-level security for multi-tenancy9293```sql94ALTER TABLE app.orders ENABLE ROW LEVEL SECURITY;95ALTER TABLE app.orders FORCE ROW LEVEL SECURITY;   -- applies to owner too9697CREATE POLICY tenant_isolation ON app.orders98  USING (tenant_id = current_setting('app.tenant_id')::uuid);99```100101Per-request, after taking a connection from the pool:102103```sql104SET app.tenant_id = '4fa2...';   -- SET LOCAL inside a transaction is safer105```106107Use `SET LOCAL` + transaction per request so a pooled connection can never108leak the previous request's tenant.109110## TLS connection strings111112```bash113# verify-full = encrypt AND verify hostname against the server cert114postgres://app_rw:pw@db.internal:5432/appdb?sslmode=verify-full&sslrootcert=/etc/ssl/rds-ca.pem115116# mysql client equivalent117mysql --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/ca.pem ...118```119120`sslmode=require` encrypts but does NOT verify the server — MITM-able; use121`verify-full` for anything crossing a network you don't own.122123## Audit logging124125```ini126# postgresql.conf — minimum viable audit127log_statement = 'ddl'                 # every schema/role change128log_connections = on129log_disconnections = on130```131132```sql133-- pgaudit for compliance-grade auditing134CREATE EXTENSION pgaudit;135ALTER SYSTEM SET pgaudit.log = 'ddl, role, write';136SELECT pg_reload_conf();137```138139Ship logs off-host (the attacker who owns the DB host owns its logs).140141## Gotchas142143- **Superusers and table owners bypass RLS** unless `FORCE ROW LEVEL SECURITY`144  is set — and superusers bypass it regardless. Test policies as `app_rw`.145- **`GRANT ... ON ALL TABLES` is a snapshot**, not a subscription — without146  `ALTER DEFAULT PRIVILEGES`, every migration-created table is silently147  inaccessible (or worse, PUBLIC-readable).148- **`sslmode=prefer` (the default) silently falls back to plaintext.**149- **Connection pools + `SET app.tenant_id`** leak across requests unless you150  use `SET LOCAL` in a transaction or reset on checkout.151- **`.pgpass`, shell history, and process lists** (`psql -c` with inline152  passwords, `ps` showing DSNs) are the classic secret leaks alongside VCS.153- **`pg_hba.conf` `trust` entries** mean password-less login for anyone who154  can reach the socket — audit for them explicitly.155- **Column encryption with `pgcrypto`** kills indexes on that column156  (equality possible via deterministic digest column; range queries are gone).157