A database session has been open for an hour. An administrator now removes that session's access to a table. Does the session lose access immediately? Explain when a database evaluates privileges relative to when the connection was authenticated.
answer
- Checked at execution, not cached at connect
- Revoke lands on the session's next statement, once committed
- Revoke can queue behind locks on the object
- Prepared/cached plans are re-checked — no bypass
- Disable ≠ disconnect; pooled sessions must reset role
basics
~20 sYes, effectively. Privileges are read from the catalog when a statement executes, not cached at connect time, so the session's next statement fails once the change commits. A statement already running finishes, and removing login rights or credentials does not disconnect anyone — you must terminate the session.
solid answer
~60 sAuthorization is evaluated **per statement, at execution**, against live catalog state and the session's current effective identity. So a privilege change reaches an hour-old session on its **next** statement, once the change has committed — no reconnect, no restart. A statement already executing when the change lands is not interrupted; the change also has to acquire its own lock on the object, so "immediately" in practice means "as soon as it can commit", which behind a long-running query can be a while. Two details a senior answer should include. First, **cached and prepared plans do not bypass the check** — the executor verifies privileges on the objects a plan touches every time it runs, so a session cannot hold onto access via a prepared statement. Second, **authentication is the opposite**: it happened once at connect, so revoking the login right, changing the password, or deleting the external account blocks only *new* connections. Existing sessions survive until explicitly terminated. Also mind the effective identity: role switching and definer's-rights routines change whose privileges are checked, which matters for pooled connections that must reset role between clients.
code
sql · 6 linesREVOKE CONNECT ON DATABASE appdb FROM analyst;
ALTER ROLE analyst NOLOGIN;
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'analyst' AND pid <> pg_backend_pid();go deeper
Say that privileges are checked each time a statement runs, so a change applies on the session's next statement without reconnecting.
Add the contrast with authentication happening once at connect, and note that prepared statements are re-checked rather than grandfathered.
Cover the operational reality: the revoke must commit and can block on locks, in-flight statements finish, disabling an account requires terminating sessions, and pooled connections must reset role.
Discuss where the enforcement point belongs — live catalog checks in the engine versus application-side caching — and how revocation latency and session eviction feed an incident-response contract.
## The core rule A relational engine performs the authorization check when a statement executes, using the catalog as it stands at that moment and the identity the session is currently acting as. There is no privilege snapshot taken at connect time. This has an obvious, and useful, consequence: privilege changes reach long-lived sessions without any reconnection. ## Why it is designed this way If privileges were cached per session, revoking access would be unreliable in exactly the situation where it matters. Connection pools hold sessions open for days; an incident-response revoke would then depend on pool recycling. Evaluating per statement makes the catalog the single live source of truth, and makes the check happen at the same place and time as execution, where the actual object list for the statement is known. ## What "immediately" really means Three qualifications: 1. **The change must commit.** Until then it is invisible to other sessions, like any other transactional change. 2. **The change needs a lock on the object.** A privilege change on a table conflicts with concurrent activity on that table, so a long-running query or an idle-in-transaction session holding a lock can make the revoke *queue* — and, worse, block everything behind it. When the revoke seems not to take effect, the usual answer is that it has not committed because it is waiting on a lock. 3. **An in-flight statement is not interrupted.** A query that already started and passed its checks runs to completion. If you need the session gone now, terminate the backend. ## Cached plans, prepared statements and views A frequent misconception is that a prepared statement or a cached plan "bakes in" permission. It does not: the executor re-checks privileges on the objects a plan reads or writes on every execution, so replaying a prepared statement after a revoke fails. What *is* fixed at creation time is the privilege context of **views and definer's-rights routines**: a view is normally executed with the view owner's privileges over its underlying tables, so a caller who cannot read the base table may still read it through the view. That is a deliberate feature, and it is also how people accidentally leave access open after revoking direct table privileges. ## Session identity vs current identity Two identities matter: - **Session identity** — the role that authenticated. It is the anchor for what the session is *permitted to become*. - **Current/effective identity** — whose privileges are checked right now. It changes when the session switches role, and inside definer's-rights routines it becomes the routine owner for the duration. This matters most with connection pooling. A common pattern is to authenticate once as a low-privileged pool owner and switch to a per-request role for each unit of work. If the pool hands a connection to the next client without resetting the role, that client inherits the previous role's privileges — a serious cross-tenant leak. Whatever mechanism sets the role must be paired with an unconditional reset on release. ## The asymmetry with authentication Authentication ran once, during the handshake. Therefore: - Revoking the login right, rotating the password, disabling the directory account or revoking the client certificate stops **new** connections only. - The existing session keeps working, subject to its per-statement privilege checks, until it disconnects or is terminated. So a correct account-disable runbook is two-step: block new authentication, then enumerate and terminate that role's live sessions. Skipping the second step is one of the most common real-world gaps between "we revoked access" and "access stopped". ## Diagnosing in practice - Session errors with `permission denied` right after a change → working as designed; the per-statement check saw the new state. - Session still succeeding after a revoke → check whether the revoke actually committed (blocked on a lock), whether access is arriving through a view or definer's-rights routine, whether it comes via role membership you did not revoke, or whether the privilege was granted to a public/everyone pseudo-role rather than to the account directly. - Session still connected after the account was disabled → expected; terminate it. ## What interviewers listen for The crisp statement that authorization is per statement while authentication is once per connection, plus at least one of: cached plans are still checked; the revoke can block on locks; disabling an account does not disconnect it; pooled sessions must reset role. Candidates who say "you have to reconnect for a GRANT to take effect" reveal they have never watched it happen.
- After revoking a privilege, a session still reads the table successfully. What are the likely explanations?Either the revoke has not committed — privilege changes take a lock on the object and can queue behind a long query or an idle-in-transaction session — or the access arrives by another path: membership in a role that still holds the privilege, a grant made to the public/everyone pseudo-role, or a view or definer's-rights routine that executes with its owner's privileges over the base table. Revoking one direct grant rarely closes all paths.
- How does connection pooling interact with per-statement privilege evaluation?Pools authenticate once and reuse the session across many logical clients, so the effective identity is what actually decides authorization. If the application switches role per request, it must reset it unconditionally on release, or the next client inherits the previous role's privileges. The upside of per-statement evaluation still holds: revoking a privilege affects pooled sessions on their next statement without recycling the pool.
saying these in an interview costs you the question
- Saying a session must reconnect before a GRANT or REVOKE takes effect
- Believing a prepared statement or cached plan preserves access after a revoke
- Assuming disabling a login or rotating a password terminates existing sessions
- Ignoring that a privilege change can block on locks and therefore appear to do nothing
- Forgetting to reset the role on pooled connections after switching identity