How to Revoke Privileges in PostgreSQL

I still remember auditing a client’s database a while back and finding that half the application roles had ALL PRIVILEGES on every table in the schema — including tables that had nothing to do with what those roles were supposed to be doing. Cleaning that up meant getting comfortable with REVOKE, and honestly, it’s one of those commands that’s simple in syntax but easy to get wrong in practice if you don’t understand how PostgreSQL’s privilege system actually works. Let me walk you through it properly.

What Does REVOKE Do in PostgreSQL?

REVOKE removes previously granted privileges from a role (PostgreSQL’s term for both users and groups). It’s the direct counterpart to GRANT, and understanding one really requires understanding the other, so I’ll touch on GRANT briefly here too — though I’ve written a dedicated article, “How to Grant Privileges in PostgreSQL,” if you want the full picture on the granting side.

Privileges in PostgreSQL control what a role can do to specific database objects: tables, views, sequences, functions, schemas, databases, and more. Common privilege types include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, CREATE, CONNECT, EXECUTE, and USAGE.

Basic Syntax

REVOKE [GRANT OPTION FOR] { privilege [, ...] | ALL [PRIVILEGES] }
    ON [object_type] object_name [, ...]
    FROM role_name [, ...]
    [CASCADE | RESTRICT];

Let’s break this down piece by piece:

• privilege — the specific privilege (or list of privileges) you’re removing, like SELECT, INSERT.
• ALL [PRIVILEGES] — removes every privilege the role currently has on the object.
• object_type object_name — what you’re revoking access to: a table, sequence, function, schema, database, etc.
• role_name — who you’re removing access from. Use PUBLIC to revoke from everyone.
• CASCADE / RESTRICT — controls how PostgreSQL handles dependent privileges (more on this below).

Practical Examples

Revoking Table Privileges

REVOKE SELECT, INSERT ON employees FROM analyst_role;

This removes the ability for analyst_role to read from or insert into the employees table, while leaving any other privileges (like UPDATE, if it had been granted separately) intact.

To strip everything at once:

REVOKE ALL PRIVILEGES ON employees FROM analyst_role;

Revoking on Multiple Tables

REVOKE SELECT ON employees, departments, salaries FROM analyst_role;

Revoking on All Tables in a Schema

This one catches people off guard because it only affects tables that existed when you granted privileges via ALL TABLES IN SCHEMA — it doesn’t retroactively track newly created tables unless you’re also managing default privileges (covered below).

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

Revoking Database-Level Privileges

REVOKE CONNECT ON DATABASE production FROM temp_user;

This prevents temp_user from even connecting to the production database at all.

Revoking Schema Privileges

REVOKE CREATE ON SCHEMA public FROM app_user;

This stops app_user from creating new objects (tables, views, etc.) inside the public schema, while still allowing them to use existing objects if they have separate privileges on those.

Revoking Function Execution Rights

REVOKE EXECUTE ON FUNCTION calculate_bonus(integer) FROM hr_role;

Revoking from PUBLIC

By default, PostgreSQL grants some privileges to the special PUBLIC pseudo-role (for example, EXECUTE on newly created functions, in older versions, and CONNECT on databases). If you want to lock things down, revoking from PUBLIC is often the first step:

REVOKE ALL ON DATABASE production FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

I do this on nearly every production database I set up now, then explicitly grant back only what’s needed to specific roles.

Revoking the GRANT OPTION

If a role was given the ability to grant privileges to others (WITH GRANT OPTION), you can revoke just that ability while leaving the underlying privilege intact:

REVOKE GRANT OPTION FOR SELECT ON employees FROM analyst_role;

This means analyst_role can still SELECT from employees, but can no longer grant that SELECT privilege to anyone else.

Understanding CASCADE and RESTRICT

This is where a lot of confusion happens. If analyst_role granted SELECT on employees to another role (because it had WITH GRANT OPTION), and you try to revoke analyst_role‘s privilege, PostgreSQL needs to know what to do about that dependent grant.

• RESTRICT (the default) — the revoke will fail if there are dependent privileges, protecting you from accidentally breaking access for roles you didn’t intend to touch.
• CASCADE — the revoke proceeds and automatically revokes the dependent privileges too.

REVOKE SELECT ON employees FROM analyst_role CASCADE;

I’d recommend being deliberate here rather than reflexively adding CASCADE to make an error go away — take the error as a signal to check who else has been granted access downstream before you remove it.

Default Privileges

One of the most common mistakes I see is people revoking privileges on existing tables and assuming that also protects future tables — it doesn’t. For that, you need to work with ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
REVOKE SELECT ON TABLES FROM analyst_role;

This changes what happens for tables created after this point by whoever owns the default privilege set (usually the role that runs the CREATE TABLE statements). It does not retroactively affect existing tables — you still need a separate REVOKE for those.

Checking Current Privileges

Before you revoke anything, it’s worth confirming what’s actually granted. A few useful queries:

-- Table-level privileges
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name = 'employees';

-- Using psql's built-in commands
\dp employees
\ddp

\dp shows access privileges for a table, and \ddp shows default privileges configured with ALTER DEFAULT PRIVILEGES.

Common Use Cases

• Offboarding — revoking all privileges from a role when an employee leaves or a contractor’s engagement ends.
• Tightening security after an audit — removing overly broad grants like ALL PRIVILEGES ON ALL TABLES IN SCHEMA public.
• Restricting a read-only reporting role from accidentally being given write access.
• Rotating service accounts — revoking old credentials’ privileges before decommissioning them.
• Enforcing least privilege — regularly reviewing and trimming down roles that have accumulated more access than they need over time.

Troubleshooting Common Issues

“cannot drop role because other objects depend on it” — this happens when you try to drop a role that still owns objects or has grants pending. Revoke the privileges first, and reassign ownership of any objects using REASSIGN OWNED BY old_role TO new_role; before dropping the role.

Revoke seems to have no effect — check whether the role is inheriting privileges through membership in another role (via GRANT role_a TO role_b). Revoking a privilege directly from role_b won’t remove access it gets by being a member of role_a. You may need to revoke from the parent role, or remove the role membership itself.

Superuser or table owner still has access after revoke — this is expected behavior, not a bug. Superusers bypass all privilege checks, and table owners implicitly have all privileges on objects they own regardless of explicit grants. To restrict the owner, you’d need to change ownership.

REVOKE on schema doesn’t stop access to existing tables — schema-level USAGE and CREATE privileges are separate from table-level privileges. Revoking USAGE on a schema prevents a role from even seeing objects inside it via normal path resolution, but if the role still has explicit privileges on a specific table, it may still be able to access it directly depending on search_path and grants.

Best Practices

• Follow the principle of least privilege — grant only what a role needs, and treat REVOKE as your regular cleanup tool, not just an emergency response.
• Revoke default PUBLIC privileges on new databases and schemas as a standard part of your provisioning process.
• Use ALTER DEFAULT PRIVILEGES alongside REVOKE so your privilege model applies to future objects, not just current ones.
• Audit privileges periodically with information_schema.role_table_grants rather than assuming your mental model of who has access matches reality.
• Be cautious with CASCADE — understand what dependent grants exist before you wipe them out.
• Document your role and privilege structure somewhere outside the database itself, so revocations are intentional decisions, not guesswork.

Wrapping Up

REVOKE is a small command with real teeth — get too aggressive with it and you’ll break an application; get too lax with it and you’ll end up with the kind of privilege sprawl that makes security audits miserable. The key is understanding that privileges in PostgreSQL are additive, inherited through role membership, and separate at each object level (database, schema, table, column, function). Once that model clicks, revoking privileges correctly becomes a lot less error-prone.