SQL / SECURITY, ROUTINES, AND DIALECTS
GRANT and REVOKE for handing out access
Hand out and take back precise privileges with GRANT and REVOKE, verify what a role really has, and explain why a revoke can change nothing.
What you will learn
- Grant table, column, and schema privileges to a role with the narrowest privilege list
- Verify real access with has_table_privilege and information_schema.role_table_grants
- Explain why a REVOKE leaves access intact when PUBLIC or another grantor granted it
- Delegate with WITH GRANT OPTION and clean up dependent grants using REVOKE ... CASCADE
Understanding GRANT and REVOKE for handing out access
A privilege is not a property of a role, it is an entry on the object: every table, view, sequence, and function carries an access control list, and GRANT appends to it. In PostgreSQL you can read those entries directly — psql's \dp shows an invoices row as reporting_ro=r/postgres, meaning "reporting_ro holds SELECT, granted by postgres". A fresh role starts with nothing on your tables except whatever was granted to PUBLIC, and its effective access is the union of every entry naming it, naming a role it belongs to, or naming PUBLIC.
Because each entry records its grantor, REVOKE is narrower than most people expect: it deletes the entries the revoking role created, not the privilege as an abstract fact. If two roles each granted SELECT on the same table, one REVOKE leaves the other entry standing and the grantee keeps reading. WITH GRANT OPTION extends the same model by letting the grantee write entries of its own, which become dependents — revoking the parent under the default RESTRICT fails with "dependent privileges exist", and CASCADE removes the chain it produced.
Grants are resolved when they run, against the objects that exist at that moment. GRANT SELECT ON ALL TABLES IN SCHEMA app is a loop over today's tables, not a standing rule, so the table your next migration creates stays unreachable until you grant again or install ALTER DEFAULT PRIVILEGES for the creating role. Reach is also layered: a table privilege is useless without USAGE on the schema holding the table, and it can be sharpened below the table with a column list, which SELECT, INSERT, UPDATE, and REFERENCES accept.
CREATE ROLE reporting_ro;
CREATE TABLE invoices (id integer PRIMARY KEY, customer text, total numeric);
GRANT USAGE ON SCHEMA public TO reporting_ro;
GRANT SELECT, INSERT ON invoices TO reporting_ro;
SELECT privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'reporting_ro' AND table_name = 'invoices'
ORDER BY privilege_type;
REVOKE INSERT ON invoices FROM reporting_ro;
SELECT has_table_privilege('reporting_ro', 'invoices', 'SELECT') AS can_select,
has_table_privilege('reporting_ro', 'invoices', 'INSERT') AS can_insert;GRANT and REVOKE edit per-object lists of (grantee, privilege, grantor) entries, and a role's access is the union of every entry that applies to it, so a REVOKE only removes the entries you granted.
Worked examples
Delegating with WITH GRANT OPTION
Shows how a grantee can create further grants and why removing the parent grant needs CASCADE.
CREATE ROLE analytics_lead;
CREATE ROLE intern;
CREATE TABLE sales (region text, amount numeric);
GRANT SELECT ON sales TO analytics_lead WITH GRANT OPTION;
SET ROLE analytics_lead;
GRANT SELECT ON sales TO intern;
RESET ROLE;
SELECT has_table_privilege('intern', 'sales', 'SELECT') AS before_revoke;
REVOKE SELECT ON sales FROM analytics_lead CASCADE;
SELECT has_table_privilege('intern', 'sales', 'SELECT') AS after_revoke;Example explained
Line 1WITH GRANT OPTION marks the entry grantable, which is the only thing that lets analytics_lead re-grant SELECT.
Line 2Under SET ROLE analytics_lead, the second entry is stored with analytics_lead as grantor, not the table owner.
Line 3before_revoke is t because intern's access hangs entirely off the entry analytics_lead created.
Line 4Without CASCADE the REVOKE would abort with "dependent privileges exist"; CASCADE deletes the derived entry too, so after_revoke is f.
Column-level SELECT
Demonstrates that a column grant is stored per column and never sums to table-level SELECT.
CREATE ROLE support_agent;
CREATE TABLE employees (id integer, name text, salary numeric);
GRANT SELECT (id, name) ON employees TO support_agent;
SELECT has_column_privilege('support_agent', 'employees', 'name', 'SELECT') AS name_col,
has_column_privilege('support_agent', 'employees', 'salary', 'SELECT') AS salary_col,
has_table_privilege('support_agent', 'employees', 'SELECT') AS whole_table;Example explained
Line 1GRANT SELECT (id, name) writes privileges onto those two columns instead of onto the table as a whole.
Line 2has_column_privilege returns t for name and f for salary because each column keeps its own list of entries.
Line 3whole_table is f, so SELECT * FROM employees is refused while SELECT id, name FROM employees works.
Line 4The refusal names the table rather than the offending column, which is why the cause is easy to misread.
Grants to PUBLIC outlive a role revoke
Shows a REVOKE that reports success while changing nothing, because the privilege came from PUBLIC.
CREATE ROLE tenant_app;
CREATE TABLE audit_log (id integer, message text);
GRANT SELECT ON audit_log TO PUBLIC;
SELECT has_table_privilege('tenant_app', 'audit_log', 'SELECT') AS via_public;
REVOKE SELECT ON audit_log FROM tenant_app;
SELECT has_table_privilege('tenant_app', 'audit_log', 'SELECT') AS after_role_revoke;
REVOKE SELECT ON audit_log FROM PUBLIC;
SELECT has_table_privilege('tenant_app', 'audit_log', 'SELECT') AS after_public_revoke;Example explained
Line 1GRANT SELECT ON audit_log TO PUBLIC stores a single entry that applies to every role in the cluster, including ones created later.
Line 2via_public is t even though tenant_app was never named in a GRANT, because the union includes the PUBLIC entry.
Line 3The first REVOKE looks for an entry granted to tenant_app, finds none, deletes nothing, and still prints REVOKE.
Line 4Only REVOKE ... FROM PUBLIC removes the entry that was actually supplying the privilege, which flips the answer to f.
Important notes
A REVOKE that matches no entry still reports success, so has_table_privilege is the only real proof that access is gone.
Owners and superusers bypass these checks entirely, so revoking from a table's owner is meaningless — it can grant the privilege straight back.
Common mistakes
Granting SELECT on the tables but never USAGE on the schema, so every query fails with permission denied for schema and the real cause gets misdiagnosed as a missing table privilege.
Treating GRANT SELECT ON ALL TABLES IN SCHEMA as a permanent rule: it expands once over existing tables, so the next migration's table is invisible to the reporting role and dashboards break after each deploy.
Revoking from the role when the privilege actually came from PUBLIC: the statement reports REVOKE, nothing changes, and the table stays readable by every role in the cluster.
Try it yourself
Change, predict, then run
In a browser Postgres editor, create a role auditor and a table payments (id integer, card_number text, amount numeric), then grant SELECT on only (id, amount). Prove with has_column_privilege and has_table_privilege that card_number is unreadable and that table-level SELECT is still false.
Open the SQL workspaceCheck your understanding
Roles dev_a and dev_b have each run GRANT SELECT ON orders TO analyst. Acting as dev_a you run REVOKE SELECT ON orders FROM analyst. What is the result?
- analyst loses SELECT, because REVOKE removes the privilege from the object regardless of who granted it
- The REVOKE fails with an error, since dev_a may not touch an entry created by dev_b
- analyst can still read orders, because dev_b's grant is a separate entry that dev_a's REVOKE does not touch
- analyst keeps SELECT only until the current session ends, when the remaining grant is re-evaluated
Show answer
Each grant is stored together with its grantor, so orders holds two independent entries and dev_a's REVOKE deletes only its own; the union of what remains still contains SELECT. The first option is tempting because the statement prints REVOKE with no warning, but that success means "my entries are gone", not "the grantee has no access" — dev_b or a superuser must remove the other entry.