A service account was granted SELECT on a table but still gets 'permission denied' when it queries it. Which layers of privilege does a relational engine check for a simple table read, and how do those layers combine?
answer
- per object, nested containers
- schema usage opens the container
- additive within a layer, all-of across layers
- WHERE columns need SELECT too
- views checked against the owner
basics
~20 sPrivileges are checked per object touched, and they are containment-layered: the role needs access to the database, usage of the schema that holds the table, and SELECT on the table itself (plus any column-level restriction). Missing the schema-level privilege denies the query even though the table grant exists.
solid answer
~50 sPrivilege checking is per object, and objects nest. For a plain read the engine typically checks: the role may connect to the database; the role has usage on the schema/namespace that qualifies the name (without it the object cannot even be referenced); the role has SELECT on the table; and, if column-level privileges are in play, on each column the statement mentions -- including columns used only in WHERE or ORDER BY. The classic cause of this symptom is the missing schema-level privilege: the table grant is real but the container is closed. Other layers surface as the statement grows: a view is checked against the view's privileges while the underlying tables are checked against the view owner's, functions need EXECUTE, sequences behind auto-generated keys need their own privilege, and writes need INSERT/UPDATE/DELETE plus SELECT if the statement reads rows to filter or return them. Privileges are purely additive -- there is no deny that could be subtracting access here.
code
sql · 2 linesGRANT USAGE ON SCHEMA app TO reporting;
GRANT SELECT ON app.orders TO reporting;go deeper
Name the two layers -- schema usage and the table privilege -- and know that both are required.
Enumerate the full chain including columns, and explain additive-within-layer versus all-of-across-layers.
Add views and ownership chaining, functions, sequences, and a repeatable debugging order that starts from the effective session role.
Design so the layers are consistent by construction: schema-per-domain, grants issued in migrations, and a review query that reports effective access rather than individual grants.
## The mental model: per-object checks over a containment hierarchy A relational engine does not evaluate 'can this user run this query' as one decision. It resolves the statement into the set of objects it touches and checks a specific privilege on each one. Those objects nest -- cluster/instance, database, schema (namespace), table, column -- and access to an inner object is meaningless without the right to traverse the outer ones. For `SELECT * FROM app.orders` a typical engine verifies: 1. **Connect/use the database.** A session-level check, done at login. 2. **Usage on the schema `app`.** This is the privilege that lets a role *resolve names* inside the namespace. It grants nothing about the contents; it only opens the container. This layer is where 'I granted SELECT and it still fails' almost always lands. 3. **SELECT on `app.orders`.** The object privilege proper. 4. **Column privileges, if any.** If the role holds column-scoped SELECT rather than table-scoped, every column referenced anywhere in the statement must be covered -- projection list, predicates, ordering, grouping. ## Why the schema layer trips people Schema usage is easy to forget because in many setups everything lives in a default schema that already carries a broad grant, so table grants alone appear to work. The moment a team introduces per-domain schemas, or a hardening pass removes the broad default grant, previously sufficient table grants become insufficient. The error message is often the same generic 'permission denied for table', which points at the wrong layer. A useful diagnostic habit: ask 'can this role see the object at all?' before asking 'can it read the object?'. If name resolution fails or the object is invisible in the catalog listing the role can see, the container privilege is missing. ## Layers that appear as statements get richer - **Views.** Reading a view requires SELECT on the view. The underlying tables are usually checked against the *view owner's* privileges, not the caller's -- the ownership-chaining behaviour that makes views a practical way to expose a restricted projection. The consequence: granting a role SELECT on a view does not require granting it anything on the base tables, and revoking base-table access does not close the view. - **Functions and procedures.** Need EXECUTE. What happens inside depends on whether the routine runs with the caller's or the definer's privileges. - **Sequences / identity generators.** An INSERT that relies on a generated key may need a privilege on the sequence object as well as INSERT on the table. - **Foreign keys.** Some engines require a REFERENCES privilege to create a constraint pointing at another table. - **Mixed statements.** `UPDATE t SET a = 1 WHERE b = 2` needs UPDATE on the table (or column `a`) *and* SELECT on `b`, because reading the predicate column is a read. Likewise `DELETE ... WHERE` and `INSERT ... SELECT`. ## How the layers combine Within a layer, privileges are **additive**: the effective set is the union of what the role holds directly, what it inherits through role membership, and what is granted to PUBLIC. Standard SQL has no negative grant, so nothing subtracts. Across layers they are **conjunctive**: every layer the statement traverses must pass. Additive-within, all-of-across is the sentence worth memorising. ## Debugging sequence 1. Confirm the session's actual role -- a pooler or a `SET ROLE` may mean you are not the account you think you are. 2. Check the schema-level privilege for that role (and for the roles it inherits, and PUBLIC). 3. Check the table privilege. 4. If the object is a view, check who owns it and whether the owner still has access to the base tables. 5. Only then look at column-level grants and at objects referenced indirectly (sequences, functions).
- The role can read the table through a view but not directly. Is that a misconfiguration?No, that is the intended behaviour of ownership chaining. The view is checked against the caller, while the base tables are checked against the view's owner, so a view is the standard mechanism for exposing a filtered or projected slice without granting access to the underlying data. It becomes a problem only if the view's definition is broader than intended or the owner is over-privileged, since the caller inherits the owner's reach for anything the view exposes.
- An UPDATE fails for a role that holds UPDATE on the table. What else could be missing?Most likely SELECT, because the WHERE clause reads columns and reading is a distinct privilege from writing; the same applies to a RETURNING clause. If column-level privileges are used, the specific columns in the predicate and the assignment list each need their own coverage. A missing privilege on a sequence or a function used in a default expression can also surface as a failure on an otherwise valid write.
saying these in an interview costs you the question
- Assuming a table grant is sufficient regardless of the schema
- Thinking a failed query means a DENY exists somewhere -- standard SQL grants are additive only
- Believing the caller needs base-table privileges to read a view
- Forgetting that predicate and ORDER BY columns require read privileges
- Diagnosing with the wrong session role because a pooler or SET ROLE changed it