Prompt and scope
A team plans to upgrade from PostgreSQL 18 to PostgreSQL 19. Current clusters use RADIUS and some MD5 password authentication; clients include applications, operations scripts, and BI tools. Explain how you would inventory dependencies, choose replacements, validate clients, and define stop conditions.
PostgreSQL's official Beta notice describes a pre-release feature preview and advises against direct production use. The version 19 migration notes remove RADIUS support and emit a warning after successful MD5 authentication; RADIUS was only supported over UDP, which the project describes as unfixably insecure. The question tests an evidence-based authentication migration, without assuming every environment is affected.
What the interviewer evaluates
The interviewer wants you to separate protocol removal, password formats, and client capability; build a complete dependency inventory; and define least-privilege replacements, observable rollout, and rollback. A strong answer also states that Beta results are not final-version commitments and does not misrepresent an MD5 warning as an immediate connection ban.
Clarifying questions before answering
- What are the current and target PostgreSQL versions, and when is the final release expected?
- Which entry points use RADIUS, and which users or connections still use MD5?
- Do drivers, pools, and operations tools support SCRAM, certificates, or an identity proxy?
- How are the authentication server, network path, audit requirements, failover, and emergency accounts managed?
- What outage time, rollback window, and security owner are available?
30-second answer framework
“I would treat Beta as an isolated validation target. First I would inventory every pg_hba.conf rule, password format, client, and RADIUS dependency, then validate SCRAM, certificates, or a controlled identity proxy on a PostgreSQL 19 test cluster. I would keep old and new paths available for the same identity during replay of connections, failover, and audit scenarios, then migrate low-risk tenants first while watching authentication failures, latency, and audit gaps. Any login failure, privilege expansion, audit loss, or rollback failure stops the rollout, with the old cluster and authentication path preserved.”
Step-by-step deep answer
1. Build the dependency inventory
Export and review pg_hba.conf, role attributes, password storage, connection sources, client versions, pools, and automation scripts. Mark RADIUS, MD5, SCRAM, certificates, and external proxies separately; record the user, network, database, rule priority, and owner for every rule. Do not search only the application repository: temporary scripts and BI tools may also hold connection credentials.
2. Separate removal from warning
PostgreSQL 19's migration notes remove RADIUS; successful MD5 authentication produces a client warning controllable with md5passwordwarnings. A warning signals migration work; it does not mean current connections are already rejected. Replace RADIUS dependencies before the upgrade, and schedule password and rule migration for MD5 dependencies instead of treating the risks as identical.
3. Choose a replacement and constrain privileges
Evaluate SCRAM-SHA-256, client certificates, or an existing enterprise identity proxy, based on client support, key lifecycle, network boundaries, and audit requirements. Create short-lived least-privilege migration accounts, never shared superusers; separate old and new accounts and set expiry and ownership. The design must cover failover, read-only replicas, operations break-glass accounts, and offline recovery.
4. Validate the connection matrix in isolation
Recreate the production topology with de-identified data and separate credentials. Test the application, pool, migration tools, backup restore, monitoring, and failover one by one. Pin a driver version for each client and record authentication method, TLS, connection time, failure reason, and audit event. It is reasonable to keep old and new login paths for one role during testing, but test credentials must never reach production.
psql "host=pg19-test dbname=app user=app_scram sslmode=verify-full" -c 'select 1'
psql "host=pg19-test dbname=app user=app_cert sslmode=verify-full" -c 'select 1'5. Design the canary, gates, and rollback
Move low-risk tenants or non-critical jobs first. Set thresholds for authentication failures, connection latency, password-reset success, audit completeness, and failover success; stop on privilege expansion, login failure, audit gaps, or recovery failure. Keep a bootable PostgreSQL 18 copy, old rule versions, password-rotation records, and rollback scripts, and verify that old clients can recover within the rollback window.
6. Record Beta uncertainty
Beta behavior and interfaces may still change. Write conclusions as results for a stated client, authentication configuration, and test data set; record versions, configuration hashes, and known issues, then seek final approval after the final release. A successful test cluster does not prove compatibility for every client.
High-quality sample answer
I would freeze the PostgreSQL 18 authentication baseline and build a matrix of pg_hba.conf, roles, clients, and RADIUS dependencies by entry point and owner. Because PostgreSQL 19 removes RADIUS, I would validate SCRAM, certificates, or an enterprise identity proxy for affected identities in an isolated cluster, while handling remaining MD5 users separately; the successful-MD5 warning signals migration work but is not a blanket rejection. I would replay applications, pools, scripts, backup restore, and failover while observing failures, latency, audit, and privilege boundaries. Only after a low-risk canary passes every gate would I expand it; privilege expansion, audit loss, login failure, or rollback failure stops the rollout. Beta evidence remains input to final-version approval.
Common mistakes
- Treating RADIUS removal and the MD5 warning as the same event → one removes support, the other signals migration → inventory and plan them separately.
- Changing only
pg_hba.confwithout testing clients → drivers and pools may lack support → replay a complete client matrix. - Rotating every production password at once → the failure and rollback surface is too large → isolate, use temporary least-privilege accounts, and migrate in batches.
- Ignoring break-glass accounts → an outage may remove recovery access → prepare controlled, audited emergency access.
- Presenting Beta success as a final compatibility guarantee → pre-release behavior can change → record boundaries and re-approve the final version.
Follow-up questions and responses
When must the upgrade stop?
Stop on login failures, privilege expansion, audit gaps, critical client incompatibility, failover failure, or a rollback that cannot complete within the window.
Why not replace RADIUS with any password method?
Authentication strength, key lifecycle, network boundaries, and audit requirements differ. The replacement must satisfy the security policy and cover clients and failure scenarios.
Does an MD5 warning mean disconnect every MD5 user immediately?
No. The warning exposes a path that still needs migration. Identify clients, rotate credentials, validate SCRAM, certificates, or a proxy, and switch in batches.
How do you prove hidden clients were not missed?
Correlate connection logs, audit records, role usage, network sources, repository search, and operations interviews over a fixed window; monitor unknown sources and authentication failures before and after migration.
When can a Beta-tested change enter production?
After the final release, a new version and client-matrix validation, successful rollback and audit drills, and joint approval from security, database, and business owners, followed by an observable canary.