Exercise 3: The Two-Layer Authentication Model and Its Classic Gotcha — Possible Solution ==================================================================== THE TWO-LAYER MODEL ------------------------------ Per this chapter, "Postgres's two-layer model — pg_hba.conf decides whether a connection attempt is even allowed, role privileges decide what it can do once connected — is a genuinely distinct architectural split." Layer one, pg_hba.conf, is checked FIRST and controls "who can attempt to connect, from where, and using what authentication method... checked before a role's own privileges are ever evaluated at all." Layer two is the role/privilege system covered earlier in this chapter — what a role is actually allowed to do once a connection has already been permitted by layer one. WHY THIS DIFFERS FROM MYSQL'S MODEL ------------------------------ Per this chapter, "MySQL instead folds the authentication method directly into the user account itself (CREATE USER 'x'@'host' IDENTIFIED BY ...), rather than using a separate control file." In MySQL, the host-matching and authentication method are part of the user account definition itself — a single mechanism. In Postgres, the two concerns are handled by two genuinely separate systems that both have to independently agree before a connection succeeds. THE CLASSIC GOTCHA ------------------------------ Per this chapter's own warn-box, "a role can be created correctly, with a correct password, and still fail to connect with an authentication error — because pg_hba.conf has no matching rule for the connecting host or database." Concretely: an administrator creates a new role with CREATE ROLE ... LOGIN PASSWORD '...', and the role and password are both entirely correct. But if pg_hba.conf doesn't contain a rule permitting a connection from that specific host, to that specific database, using an accepted authentication method, the connection attempt will still fail with an authentication error — even though nothing about the role itself is wrong. This is confusing precisely because the error looks like a role/password problem, when the actual missing piece is an entirely separate configuration file that was never updated. THE RELOAD-NOT-RESTART DETAIL ------------------------------ Per this chapter's own warn-box, "a change to pg_hba.conf takes effect on a configuration reload... not a full server restart." This matters practically: after fixing a pg_hba.conf gap, an administrator only needs to trigger a reload (not take the server down with a full restart) for the new rule to take effect. WHY THIS WORKS AS AN ANSWER ------------------------------ It explains both layers and their check order using the chapter's own wording, walks through the specific gotcha scenario the chapter describes, and includes the reload-vs-restart detail the chapter flags as a related, practically useful fact.