A database holds an organisation's most sensitive data, so securing it is not optional. PostgreSQL's security model has three pillars: authentication (proving who is connecting), authorization (controlling what they may do), and encryption (protecting data in transit and at rest). Getting all three right is what stands between a well-run system and a breach, and each is configured through distinct, well-defined mechanisms.
Authorization in PostgreSQL revolves around roles — a unified concept covering both users and groups — and the privileges granted to them. On top of table-level privileges sits Row-Level Security (RLS), which restricts which rows a role can see or change, enabling multi-tenant isolation enforced by the database itself rather than trusted to every query.
Encryption in transit is handled by SSL/TLS, ensuring data travelling between client and server cannot be read or tampered with on the wire, while authentication rules in pg_hba.conf decide who may connect and how they must prove it. This lesson covers roles and least privilege, RLS, and SSL — the core controls every production database must apply.
Analogy🏏Cricket
🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.