Chapter 32 — Key Takeaways

The big idea

Database security is defense in depth — many layers, each assuming the others might fail. No single setting makes you secure; the combination makes compromise hard and contained.

The layers

  1. Parameterize queries → no SQL injection. Never concatenate input into SQL; parameterization makes injection structurally impossible (Ch. 29). The #1 application defense.
  2. Least privilege → contained breach. Roles + GRANT/REVOKE; the app connects as a limited role, never superuser; separate read-only/read-write; grant the minimum (even per-column). Contains every breach. (Case Study 1: superuser app = total compromise vs. limited role = contained.)
  3. RLS + views → row/column isolation in the database. Row-Level Security auto-filters every query (multi-tenancy) so a forgotten WHERE can't leak (Case Study 2); views/column grants hide sensitive columns.
  4. Encryption → in transit (TLS) and at rest (disk/column). Require TLS; encrypt stolen-disk risk away. Passwords are hashed (bcrypt/argon2), never encrypted/plaintext.
  5. Tested backups / PITR → recoverable loss. Ransomware, accidental DROP — recoverability is a security control (Ch. 28, 38).
  6. Audit logging → detect & prove misuse (pgAudit, audit triggers — Ch. 21).
  7. Privacy by design → lawful PII. GDPR/CCPA: data minimization, access control, right to erasure, breach planning — design constraints, not afterthoughts.

The two big lessons

  • Parameterize and least-privilege: prevention + containment are different layers; do both. (Case Study 1.)
  • Enforce isolation in the database, not app code: RLS makes "forgot the WHERE" impossible. (Case Study 2.)

Common mistakes

String-concatenated SQL; app-as-superuser; no TLS; passwords encrypted/plaintext (hash them!); relying on app code to filter tenant data; untested backups; hoarding unneeded PII.

You can now…

  • ☐ Prevent injection (parameterize) and explain why it's structural.
  • ☐ Apply least privilege with roles/grants; never run apps as superuser.
  • ☐ Use RLS and views/column grants for row/column isolation.
  • ☐ Apply TLS, encryption at rest, and correct password hashing.
  • ☐ Treat backups/PITR and audit logging as security; design for privacy/erasure.

Looking ahead — Part VI

Application Integration complete. Chapter 33 — NoSQL opens Part VI (Beyond Relational): document, key-value, column-family, and graph databases — when relational isn't the answer, and (theme #4) when PostgreSQL still is.

One sentence to carry forward: Security is layers — parameterize to prevent injection, least-privilege to contain breaches, RLS to enforce isolation in the database, encrypt and hash, and keep tested backups — because any one layer can fail.