LEARN. PRACTICE. GROW.Separate connection, schema and object
A service can authenticate and still receive permission denied when querying a table. In the lab, app_a has SELECT on service.old_data but lacks USAGE on the schema. The first query fails; granting USAGE permits reading the existing row. This contrast locates the missing dependency without granting object creation. The next step attempts CREATE TABLE and still fails. During an incident, collect the complete operation, effective identity and qualified object name. Successful connection, SELECT 1, or a query performed by the administrator does not demonstrate that the application path is authorized.
Trace every grant path
The team removes a direct grant and expects access to disappear. In the experiment, app_a can still read because it also belongs to reader with INHERIT TRUE. Revoking the direct grant removes that path only; it does not create a denial that overrides the others. Before declaring revocation complete, inspect memberships and PUBLIC grants as well as direct privileges. The correction must match the intention: removing an individual exception differs from removing all access. Test with the affected identity and retain a matrix of operations that should remain available to avoid breaking legitimate functions.
Distinguish inheritance from role switching
The experiment separates two configurations. app_a inherits reader privileges, but SET FALSE prevents SET ROLE reader. operator has membership in manual_reader with INHERIT FALSE and SET TRUE: reading initially fails, succeeds after switching and fails again after RESET ROLE. During the switch, session_user remains operator and current_user becomes manual_reader. Record both identities when investigating differences between sessions. A policy using current_user may behave differently after SET ROLE. In a pool, identity cleanup must be part of connection reuse; this lab does not implement or validate an actual pool.
Authorize the sequence used by INSERT
The application receives INSERT on the jobs table, but inserting only label still fails. The serial column obtains its identifier through a sequence, which has separate privileges. The experimental error identifies jobs_id_seq; granting USAGE on that sequence allows insertion to complete. Start the decision from the expression actually executed and the collected error. Do not generalize this outcome to every identifier-generation strategy or grant privileges on all sequences without an inventory. If the application also uses RETURNING, check read permissions on returned columns in a separate test. The INSERT executed here contains no RETURNING.
Treat projections as part of the contract
Support needs id and email from fictional contacts without access to the secret field. The column grant permits SELECT id,email and rejects SELECT *. A tool change that selects all columns can therefore break a query that was previously authorized correctly. Keep explicit projections and test statements emitted by the client, including filters and expressions. Do not automatically turn the error into justification for access to the entire table. The acceptance matrix should contain both the permitted query and the prohibited query; either one alone leaves half the requirement unproven.
Prepare a verifiable access change
In a fictional handover, the deployer can execute every query and proposes closing the change. The RUN team should repeat operations using runtime accounts, including expected denials and identity cleanup. Record SQLSTATE, identity, object and outcome without placing sensitive data in the report. Define reversal of changed grants and memberships while preserving the previous state record. The lab demonstrates local SQL authorization with trust authentication on a private socket; it does not demonstrate passwords, TLS, federated identity or isolation between operating-system users. These boundaries must remain explicit when the evidence supports release approval.
SELECT session_user, current_user;
SELECT has_schema_privilege(current_user, 'service', 'USAGE');
SELECT has_table_privilege(current_user, 'service.old_data', 'SELECT');
-- In the owned lab, connect as operator before switching:
SET ROLE manual_reader;
SELECT session_user, current_user;
RESET ROLEapp_a has SELECT on a table but can query it only after receiving USAGE on the service schema.
Common pitfalls
Granting ALL for one isolated error; testing only as owner; confusing membership with SET ROLE; ignoring sequences.
Related topics: Effective identity and least privilege · Change acceptance and recovery
One isolated grant does not describe effective access. Record identity, memberships, schema, object and dependencies.
Reference: Object and column privileges · PostgreSQL 18 reference semantics;18.6 current stable at review