How to REVOKE Permissions in PostgreSQL

Why use REVOKE in PostgreSQL?

REVOKE lets you quickly pull back privileges you once granted. This keeps sensitive ecommerce data—like customer emails or order totals—behind proper role boundaries and supports least-privilege access.

What privileges can I revoke?

You can revoke SELECT, INSERT, UPDATE, DELETE, TRIGGER, REFERENCES, or ALL on tables and views; USAGE on sequences or schemas; EXECUTE on functions; and even ADMIN OPTION on roles.

What is the REVOKE syntax?

The core pattern is REVOKE privilege_list ON object FROM role_list [CASCADE|RESTRICT]. "GRANT OPTION FOR" strips a role’s ability to pass that privilege to others.

REVOKE [GRANT OPTION FOR] privilege[, ...]
ON object_name
FROM role_name | PUBLIC [, ...]
[CASCADE | RESTRICT];

How do I revoke SELECT from analysts on the Customers table?

Run a single statement: REVOKE SELECT ON Customers FROM analyst_role; The role still exists but can no longer read customer names or emails.

How do I undo a mistaken privilege escalation?

First strip the GRANT OPTION, preventing further propagation: REVOKE GRANT OPTION FOR UPDATE ON Orders FROM junior_dev; Then remove the underlying privilege: REVOKE UPDATE ON Orders FROM junior_dev;

When should I add CASCADE?

Use CASCADE when you want PostgreSQL to remove the same privilege from every role that received it indirectly from the target role. Without it, REVOKE on a parent role fails if children still hold the privilege.

Best practices for REVOKE

Grant privileges to roles, not individual users; keep a change log; and periodically audit with pg_roles and information_schema.table_privileges. Revoke privileges before dropping objects to avoid orphaned grants.

What errors can occur and how do I fix them?

"ERROR: permission denied" appears when you attempt to revoke privileges you don’t own. Switch to a superuser or the object owner. "cannot revoke from current user" means you’re trying to revoke a privilege you depend on—verify session role.

FAQ: Does REVOKE affect existing data?

No. REVOKE only changes future access attempts. Rows already inserted by the role remain untouched.

FAQ: Can I revoke all privileges at once?

Yes: REVOKE ALL PRIVILEGES ON Products FROM PUBLIC;