Chapter Fourteen · Running Postgres in Production
Running Postgres in Production
Cartwheel's pg_hba.conf still trusts anything on the private network, the application connects as the role that owns the schema, and the analytics team authenticates as cartwheel_admin because of a Slack message from 2024. Six topics close all three, move the cluster from 17 to 18, and end by asking what a managed service would have done instead.
The last chapter ended on a Tuesday night with pg-replica-b promoted, a customer's order completing thirty-one seconds after the writes were paused, and a figure for what an unplanned failure at the same moment would have cost. Cartwheel's database is now durable, recoverable and survivable. It is also, by any reading of its own configuration, open to anyone who can reach port 5432 from the office network, and its major-version upgrade is a date somebody has to own rather than an incident to be surprised by.
Three findings sit at the top of Nadia's list, and none of them is a bug in Postgres. The pg_hba.conf on pg-primary still carries a trust line from the original 2019 install, above every careful rule added since, so the whole of 10.0.0.0/8 can authenticate as any role it names. The deployment secret that app-01 and app-02 use belongs to cartwheel_migrator, the role that owns the schema, which means an injection in the checkout path reaches DROP TABLE rather than a permission error. And the dashboard has connected as cartwheel_admin since somebody pasted that password into a Slack channel two years ago, so nothing in pg_stat_activity can tell a scheduled report from a person exploring production.
The six topics fix those in order, roles and ownership first because the authentication work and the row-level security work both depend on that separation being real, then move the cluster from 17 to 18 with the downtime priced in advance, and take an honest inventory of the extensions the design depends on. The last one is the last page of the book. It asks what a managed provider would have done for Nadia across all fourteen chapters, and what it would have taken away, which turns out to be the cleanest way to say what this course was actually about.
pg_hba.confthe first match wins, and it is from 2019psql sessionTopics in This Chapter
REVOKE can take back. Default privileges that cover tomorrow's tables, the predefined roles that replace superuser, what PUBLIC still holds, and the two statements that let a role finally be dropped.sslmode=require does not verify, and why Postgres has no transparent data encryption.psql session at 03:00. USING against WITH CHECK, why the owner bypasses its own policies, and what the predicate costs in every plan.pg_upgrade transfer modes and what each destroys, the statistics 18 finally carries across, and the pre-flight list that decides how the night goes.