How to Use the REVOKE Command in PostgreSQL

Managing who can do what in a database is one of those responsibilities that’s easy to ignore right up until it becomes a serious problem — an intern with more access than they should have, an old service account that still has write permissions on tables it hasn’t touched in years, or a departing employee whose database privileges never actually got cleaned up. REVOKE is the command PostgreSQL gives you to take permissions away, cleanly and precisely, and it deserves a lot more attention than it usually gets.

I want to walk you through exactly how REVOKE works, its syntax, the different privilege types you can revoke, and how to actually use it to keep a PostgreSQL database properly locked down.

What Is REVOKE?

REVOKE removes previously granted privileges from a role (which could represent a user or a group) on a specific database object — a table, schema, function, sequence, database, or several other object types. It’s the direct counterpart to GRANT, which is how privileges get assigned in the first place.

Privileges in PostgreSQL control what actions a role is allowed to perform: reading data (SELECT), modifying it (INSERT, UPDATE, DELETE), changing the structure of objects, executing functions, and more. REVOKE lets you take any of these back, either partially or entirely, from any role that currently holds them.

Why REVOKE Matters

Access control isn’t a “set it once and forget it” task. Roles change over time — someone moves to a different team, a service gets decommissioned, a temporary contractor’s project wraps up. If you only ever grant privileges and never revoke them, your database’s actual security posture drifts further and further from what it should be, and you end up with what’s sometimes called “privilege creep” — accounts quietly accumulating more access than they need, none of which anyone remembers granting or why.

REVOKE is how you correct that drift. It’s also essential for implementing the principle of least privilege: giving each role exactly the access it needs to do its job, nothing more.

Basic Syntax

REVOKE [ GRANT OPTION FOR ]
    { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    FROM role_name [, ...]
    [ CASCADE | RESTRICT ];

That’s the table-privilege form specifically, but REVOKE works similarly across many object types. Let’s break down the key pieces.

Parameters Explained

Privilege type The specific action you’re taking away — SELECT for read access, INSERT for adding rows, UPDATE for modifying existing rows, DELETE for removing rows, TRUNCATE for clearing entire tables, REFERENCES for creating foreign keys pointing at this table, and TRIGGER for creating triggers on it. You can revoke one, several, or use ALL PRIVILEGES to remove everything at once.

ON Specifies which object (or objects) you’re revoking privileges on — a specific table, all tables in a schema, a function, a sequence, a database, and more, depending on the privilege type.

FROM The role (or roles) losing the privilege.

CASCADE / RESTRICT These control what happens to privileges that were granted onward by the role you’re revoking from. If role A granted a privilege to role B, and you revoke that privilege from role A, what happens to B’s privilege, which depended on A having it in the first place? RESTRICT (the default) will refuse the revoke if doing so would leave dependent grants dangling, forcing you to handle those explicitly. CASCADE will automatically revoke those dependent grants too.

GRANT OPTION FOR Used when you want to revoke only a role’s ability to grant a privilege to others, while leaving the underlying privilege itself intact. More on this below.

A Basic Example

Let’s say you previously granted a role broad access, and now you want to scale it back.

-- Previously granted:
GRANT SELECT, INSERT, UPDATE ON orders TO app_user;

-- Now revoking UPDATE, keeping SELECT and INSERT:
REVOKE UPDATE ON orders FROM app_user;

After this, app_user can still read from and insert into the orders table, but can no longer modify existing rows.

Revoking All Privileges

If you want to strip a role of everything on a given object, ALL PRIVILEGES is the cleanest way:

REVOKE ALL PRIVILEGES ON orders FROM app_user;

Or, more explicitly across every table in a schema:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM app_user;

Keep in mind that this doesn’t prevent the role from being granted access again in the future by someone with appropriate permissions — it just removes what’s currently in effect.

Revoking Privileges on Different Object Types

REVOKE isn’t limited to tables. Here are some other common forms you’ll run into.

Revoking database-level connect privilege:

REVOKE CONNECT ON DATABASE analytics FROM contractor_role;

Revoking schema usage:

REVOKE USAGE ON SCHEMA reporting FROM contractor_role;

Revoking execute privilege on a function:

REVOKE EXECUTE ON FUNCTION calculate_payroll(int) FROM temp_worker;

Revoking privileges on a sequence:

REVOKE USAGE, SELECT ON SEQUENCE orders_id_seq FROM app_user;

Revoking role membership (this uses a slightly different form since it’s about role membership rather than object privileges):

REVOKE analytics_team FROM jane_doe;

This last example removes jane_doe from the analytics_team role, taking away whatever privileges that group role conferred to its members.

Understanding GRANT OPTION FOR

This one’s subtle but important. When you grant a privilege WITH GRANT OPTION, you’re not just giving the role the privilege itself — you’re also letting that role grant the same privilege to others.

GRANT SELECT ON orders TO team_lead WITH GRANT OPTION;

Now team_lead can both SELECT from orders and grant SELECT on orders to other roles. If you later decide team_lead shouldn’t be able to hand out this privilege anymore, but should keep their own access, use:

REVOKE GRANT OPTION FOR SELECT ON orders FROM team_lead;

This removes only the ability to re-grant, while team_lead retains their own SELECT access. Compare that to a plain REVOKE SELECT ON orders FROM team_lead, which would take away everything — both the privilege itself and the ability to grant it onward.

A Real-World Example: Cleaning Up an Offboarded Employee

Here’s a practical scenario that comes up constantly in real database administration — someone leaves the team, and you need to make sure their access is fully cleaned up.

-- Revoke table-level privileges across the schema
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM departing_employee;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM departing_employee;

-- Revoke schema-level access
REVOKE ALL PRIVILEGES ON SCHEMA public FROM departing_employee;

-- Revoke database connect privilege
REVOKE CONNECT ON DATABASE production FROM departing_employee;

-- Revoke role memberships
REVOKE analytics_team, reporting_team FROM departing_employee;

-- Finally, if you're removing the role entirely (after confirming it owns no objects)
DROP ROLE departing_employee;

Note that DROP ROLE will actually fail if the role still owns any database objects or has privileges granted that would leave orphaned dependencies — so working through a proper REVOKE cleanup first (or reassigning ownership with REASSIGN OWNED BY) is usually a necessary step before you can cleanly drop the role.

Common Use Cases for REVOKE

1. Offboarding — removing access when an employee or contractor leaves, as shown above.
2. Scaling back over-provisioned access — correcting situations where a role was granted broader privileges than it actually needs.
3. Temporary access cleanup — revoking privileges that were granted for a specific short-term project once that project wraps up.
4. Security incident response — quickly cutting off a compromised or suspicious account’s access to sensitive data.
5. Enforcing least privilege during regular audits — periodically reviewing and tightening up privilege grants across your database as part of routine security hygiene.

Troubleshooting Common REVOKE Issues

“REVOKE doesn’t seem to have any effect.” Check whether the role has the privilege through a different path — for example, via membership in a group role that itself has the privilege, rather than a direct grant to the individual role. Revoking a directly-granted privilege from a user won’t remove access they still have indirectly through a group role membership. You’d need to revoke from the group role, or remove the user from that group role.

“ERROR: dependent privileges exist” This happens when you try to revoke a privilege that other grants depend on (via WITH GRANT OPTION chains), without specifying CASCADE. Either use CASCADE to remove the dependent grants automatically, or manually revoke them first if you want more control over exactly what gets removed.

“I revoked a privilege but the role can still access the data.” Remember that superusers and role owners bypass normal privilege checks entirely — REVOKE has no effect on a superuser’s access, since superusers can access everything regardless of granted privileges. Also double check whether the role owns the table in question; object owners always retain full privileges on objects they own, regardless of REVOKE statements, unless ownership itself is transferred.

“Can’t drop a role because it still has privileges or owns objects.” Use REASSIGN OWNED BY old_role TO new_role; to transfer ownership of objects, and DROP OWNED BY old_role; to clean up remaining privileges and objects before attempting DROP ROLE.

Best Practices

• Prefer granting privileges to group roles, not individual users, and manage access by adding or removing users from those group roles. This makes both GRANT and REVOKE operations far simpler to reason about and audit.
• Regularly audit privileges, especially in larger teams, using PostgreSQL’s information_schema.role_table_grants and related views to see exactly what’s been granted to whom.
• Be deliberate with CASCADE. It’s powerful but can remove more than you expect if there are chains of grants you didn’t fully account for. Consider reviewing dependent grants before using it in production.
• Follow the principle of least privilege from the start, granting only what’s needed rather than granting broadly and relying on REVOKE to clean up later. It’s much easier to grant additional access when needed than to hunt down and revoke over-provisioned access after the fact.
• Build offboarding into a documented, repeatable process, ideally scripted, so that revoking access for a departing team member isn’t something that depends on someone remembering every place that person was granted privileges.
• Remember object ownership and superuser status bypass REVOKE. If your goal is truly removing all access, check ownership and role attributes (\du in psql), not just granted privileges.

Wrapping Up

REVOKE doesn’t get talked about nearly as much as GRANT, but a database’s actual security posture depends just as much on what you take away as what you hand out in the first place. Whether you’re cleaning up after an offboarded employee, tightening an over-provisioned service account, or just doing routine access hygiene, understanding exactly how REVOKE interacts with group roles, grant chains, and object ownership is what separates a database that’s actually secure from one that just looks secure on the surface.