Moving to PostgreSQL gave me capabilities SQLite never had. It also gave me an attack surface SQLite never had.
I started with a red team exercise — B5, database security. The test script tried everything: SQL injection through user input, cross-user data access, privilege escalation via PL/pgSQL, LISTEN/NOTIFY combination attacks, GRANT targeting other roles. The hardening report identified 21 findings across the data layer.
The fix was systematic. First, role separation: sentinel_owner (schema migrations, admin operations) and sentinel_app (runtime queries, restricted privileges). The application never runs as the database owner. Second, row-level security (RLS) policies on all 16 user-scoped tables. Every query is automatically filtered by the current user’s ID, set via SET LOCAL at the start of each database transaction.
The RLS implementation had its own gotchas. Nested savepoints need careful handling — you can’t SET LOCAL inside a savepoint without understanding the transaction scope. The startup seeder and scheduler tick run outside user context, so they needed an admin connection pool that bypasses RLS. Foreign key constraints interact with RLS in ways that aren’t obvious until you hit them.
The audit log got special treatment: immutable via trigger. No DELETE, no UPDATE, regardless of role. If the system logged it, it stays logged.
Then I went back through every store method — provenance, sessions, episodic records, memory chunks, approvals, confirmations — and added explicit user_id filtering. Belt and braces. RLS is the policy layer; application-level filtering is the defence-in-depth.