In a relational database, what is the difference between authentication and authorization, and at what point in a session is each one performed?
answer
- Authn once at connect; authz every statement
- Handshake yields identity, not privileges
- Connection refused vs permission denied
- Revoke does not disconnect — terminate the backend
- Host-based rules pick the method before any secret is checked
basics
~20 sAuthentication proves who is connecting. It happens once, during the connection handshake, using a password, an OS identity, a Kerberos ticket, or a client certificate. Authorization decides what that identity may do, and the server re-checks it on every statement afterwards.
solid answer
~50 s**Authentication** answers "who are you?". It runs once, during the connection handshake. The server first decides *which* method applies to this connection (typically from an ordered host-based rules file matching connection type, target database, requested user and source address), then runs it: password challenge-response such as SCRAM, an external directory (LDAP, Kerberos/GSSAPI), OS peer identity on a local socket, or a client certificate. On success a session exists, bound to a database role. **Authorization** answers "what may you do?". The server enforces it on *every* statement, against the current effective identity, using catalog privileges, role membership, object ownership and any row/column policies. The failure modes are the giveaway: an authentication failure means no session exists at all (the connection is refused or aborted); an authorization failure arrives as a `permission denied` error inside a perfectly healthy session. And authenticating successfully grants no access by itself — a role can log in fine and still be unable to read a single table.
code
text · 6 lines$ psql -h db -U reporting appdb
psql: error: connection to server failed: FATAL: password authentication failed for user "reporting"
$ psql -h db -U reporting appdb
appdb=> SELECT * FROM payments;
ERROR: permission denied for table paymentsgo deeper
Recall the definitions cleanly and give the timing: authentication once at connect, authorization on every statement. Being able to tell a connection error from a permission-denied error is enough at this level.
Add mechanics: host-based rules choose the method before credentials are checked, authentication yields only a role name, and privilege checks read live catalog state each statement.
Lead with operational consequences — revoke does not disconnect, grants take effect mid-session, and diagnose from the error class rather than guessing. Mention effective vs session identity.
Frame it as where identity lives versus where policy lives: identity can be federated to a directory, PKI or cloud IAM, while the privilege model stays in the engine so enforcement happens at the same place as execution.
## Two questions, asked at two different times Database access control splits into two decisions made at two different moments. 1. **Authentication (authn) — "who is this client?"** Asked once, while the connection is being established, before any SQL runs. 2. **Authorization (authz) — "is this identity allowed to do *this* thing?"** Asked repeatedly, by the server, for every statement the established session submits. Confusing the two is the single most common mistake in this area, and it produces real outages and real breaches. ## What happens during authentication A client opens a TCP connection or a local socket and sends a startup packet: the database it wants, the role name it claims, and connection options. The server does **not** immediately ask for a password. It first consults its **host-based access rules** — PostgreSQL's `pg_hba.conf` is the canonical example; MySQL bakes the host into the account identity (`'app'@'10.0.%'`); SQL Server has logins with endpoint rules. Those rules answer a prior question: *for a connection of this type, to this database, claiming this role, from this address, which authentication method do we demand — or do we refuse outright?* Only then does the chosen method run: - **Password / SCRAM** — challenge-response against a stored verifier. - **OS peer / trust** — the kernel tells the server the connecting Unix user; no secret at all. - **External directory** — LDAP bind, or Kerberos/GSSAPI ticket validation. - **Client certificate** — mutual TLS; the certificate subject is mapped to a database role. - **Cloud IAM tokens** — a short-lived signed token presented in the password field. The output of authentication is narrow: an authenticated **role name** for this session. Nothing more. It carries no permissions with it. ## What happens during authorization Once the session exists, every statement is checked at execution against catalog metadata: does the current identity hold the required privilege on each object touched, directly or through a role it is a member of; does it own the object; do row-level policies restrict which rows it sees. Two identities matter: the **session identity** (who authenticated) and the **current/effective identity** (who the server is acting as right now — it changes with role switching and with definer's-rights routines). Crucially, this evaluation is *not* cached at connect time. Grant a privilege now and an hour-old session can use it on its next statement; revoke one and that session loses it on its next statement. Authorization is live; authentication is a one-time gate. ## The asymmetry that trips people up Because authentication happens exactly once, **disabling an account does not disconnect it**. Revoke the login right, change the password, delete the directory account — the sessions already established keep running until you explicitly terminate their backends. Any real incident-response runbook has to say "revoke *and* kill the sessions". Conversely, because authorization is re-evaluated per statement, you don't need a reconnect or a restart to tighten permissions. ## Reading the errors - "password authentication failed", "no pg_hba.conf entry for host", "GSSAPI ticket expired", "access denied for user" → authentication. There is no session. Look at connection rules, credentials, source address, TLS. - "permission denied for table orders", "must be owner of relation", "SELECT command denied to user" → authorization. The session is fine. Look at grants, role membership, ownership, policies. Misdiagnosing this wastes hours: people rotate passwords to fix privilege errors, or hand out broad privileges to fix a connection-rules problem. ## Why the split exists Separating them lets identity be sourced externally (a corporate directory, a PKI, a cloud IAM system) while permissions stay in the database, expressed in its own privilege model and enforced by the same engine that executes the query. It also gives defence in depth: a stolen credential still only gets you whatever that role was granted, and an application bug that runs an unexpected statement still hits the per-statement check. ## What interviewers listen for A good answer states the timing difference (once vs per statement), notes that authentication yields identity and zero privileges, and offers at least one concrete consequence — usually that killing an account doesn't kill its sessions, or that permission changes take effect without reconnecting.
- An administrator revokes a role's login right and changes its password. Is that role's existing open session cut off?No. Authentication was performed once, at connect time, so an established session is unaffected by later credential or login-right changes. It keeps executing statements until it disconnects or someone explicitly terminates the backend process. Proper account disablement is therefore two steps: revoke the credential/login right so no new sessions can form, then terminate the existing sessions.
- A role authenticates successfully but every query returns permission denied. Where do you look?Purely at authorization: object privileges, role membership, ownership, default privileges on newly created objects, and any row-level policies. Authentication clearly worked, since a session exists and is returning SQL errors rather than a connection failure. A very common cause is that objects were created by a different owner and the privileges were never granted to the application role.
Authentication is the badge reader at the building entrance — it checks you once and lets you in. Authorization is the lock on each individual door inside: your badge is tested again at every door, and revoking a door permission works instantly without escorting you back outside.
saying these in an interview costs you the question
- Saying authentication and authorization are the same thing, or using authn/authz interchangeably
- Believing a successful login implies some baseline access to the schema
- Assuming a permission change requires reconnecting or restarting the database
- Assuming that disabling or dropping a credential immediately terminates existing sessions
- Treating a 'no entry for host' connection error as a missing GRANT