# 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).