Chapter 32 — Exercises

Security exercises against mercado. Some create roles/policies (use a scratch DB or clean up after). (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Injection

A1 (32.1) Show a vulnerable string-concatenated query and its parameterized fix. Explain why the fix is structurally safe, not just safer. (answer in Appendix)

32.2 Why is "validate the input" defense-in-depth but not a substitute for parameterization?

32.3 ⭐ Parameters protect values. How do you safely handle a case where the column to sort by comes from user input (an allowlist, not a parameter)?

Group B — Roles & least privilege

32.4 Create an app_rw role with SELECT/INSERT/UPDATE/DELETE on the Mercado tables and an app_ro role with only SELECT. (answer in Appendix)

32.5 Why should the application never connect as the postgres superuser? What does least privilege contain?

32.6 Grant SELECT on only specific columns of products (e.g., name, price) to an analyst role. (answer in Appendix)

32.7 ⭐ Design roles for: the web app, a reporting tool, a data-import job, and a DBA. What does each need (and not need)?

Group C — RLS & hiding data

32.8 Enable RLS on a table and write a policy that restricts rows to the current tenant. Why is this safer than filtering in app code? (answer in Appendix)

32.9 Create a view that exposes only non-sensitive columns of employees (hide salary) and grant access to the view, not the base table.

32.10 ⭐ How does RLS protect a multi-tenant app even if a developer forgets WHERE tenant_id = ...?

Group D — Encryption & passwords

32.11 Why require TLS (sslmode) for connections? What's exposed without it? (answer in Appendix)

32.12 Why must passwords be hashed (bcrypt/argon2), never encrypted or stored plaintext?

32.13 ⭐ Difference between encryption at rest (disk/volume) and column-level encryption — when would you add the latter?

Group E — Backups, audit, privacy

32.14 Why are tested backups/PITR a security control, not just an ops task? (Think ransomware, accidental DROP.) (answer in Appendix)

32.15 Identify the PII in Mercado's schema. For one piece, describe access control and how you'd honor a deletion request.

32.16 ⭐ What does the GDPR "right to erasure" complicate (foreign keys, backups, audit logs), and how would you design for it?

Group F — Progressive project

32.17 Audit your project's queries for parameterization; create least-privilege roles.

32.18 Add a view or RLS policy to hide/isolate sensitive data; require TLS; confirm passwords are hashed.

32.19 ⭐ Map your PII, its access, and your erasure approach.

Self-check. If every query is parameterized, the app runs as a limited role, sensitive data is isolated (RLS/views), TLS is required, passwords are hashed, and backups are tested — you're practicing defense in depth. Next: beyond relational.