~/sajjad $ cat projects/database-security
← cd ../projects
Operations & compliance
Database security audit & remediation
3 migrations · zero downtime
Audited the full production schema — 55 tables — and found around 40 carrying blanket access policies. The anonymous API key had read, write and delete reach over staff PINs, pay rates, salaries, guest records, timesheets, takings, petty cash and the audit log.
How it shipped
A four-stage remediation, sequenced explicitly so it could go out mid-service without locking staff out: additive scoped policies first, then an app release routing queries through authenticated clients, then confirmation of device rollout, then removal of the permissive policies.
Three migrations delivered — payroll, operations and rota, and financial, stock and document tables — all built on SECURITY DEFINER helper functions.
Documented honestly
- An unrate-limited PIN endpoint.
- Twelve-hour JWTs with no refresh.
- Row-level security filters rows, not columns.
Remaining exposure was written up rather than quietly closed off, so the next piece of work starts from a real picture.
Schematics
