How to Revoke Permissions in PostgreSQL

How do I revoke permissions in PostgreSQL?

REVOKE removes previously granted privileges from a role or public on database objects.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does REVOKE do in PostgreSQL?

REVOKE removes one or more privileges that were granted with GRANT.Use it to tighten security when users no longer need access.

When should I revoke permissions?

Revoke privileges when an employee leaves, a service account is retired, or a role should be read-only instead of read/write.

What is the basic REVOKE syntax?

REVOKE privilege_list ON object FROM role_list [CASCADE | RESTRICT];

How do I revoke table privileges?

Use REVOKE SELECT, INSERT, UPDATE, DELETE ON table_name FROM role;

How do I revoke schema privileges?

REVOKE USAGE, CREATE ON SCHEMA schema_name FROM role;

How do I revoke all privileges at once?

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

Can I revoke from PUBLIC?

Yes.Replace role_list with PUBLIC to remove default privileges from every role.

Practical example: lock a reporting role down to read-only

1. Revoke write operations
2. Keep SELECT for analytics tools

Steps

REVOKE INSERT, UPDATE, DELETE ON Orders FROM reporting_role;
REVOKE INSERT, UPDATE, DELETE ON OrderItems FROM reporting_role;

Best practices for REVOKE

1. Audit existing grants before revoking.
2. Combine REVOKE with ALTER DEFAULT PRIVILEGES to stop future grants.
3. Use CASCADE only when you understand dependent objects.

Common mistakes with REVOKE

1. Forgetting to revoke sequence privileges required for INSERT.
2.Omitting REVOKE on views that expose the same data.

Frequently asked questions

Does REVOKE remove ownership?

No. Use ALTER OBJECT OWNER instead.

What happens if the role never had the privilege?

Nothing changes; REVOKE is harmless in this case.

How do I test effective privileges?

SET ROLE target_role; attempt the operation; RESET ROLE.

Why How to Revoke Permissions in PostgreSQL is important

How to Revoke Permissions in PostgreSQL Example Usage


-- Marketing user should no longer read customer emails
REVOKE SELECT ON Customers (email) FROM marketing_user;
-- Remove write access from old service account
REVOKE INSERT, UPDATE, DELETE ON Orders FROM legacy_service;
-- Strip all privileges on product data for interns
REVOKE ALL PRIVILEGES ON Products FROM intern_role;

How to Revoke Permissions in PostgreSQL Syntax


REVOKE [ { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
       [, ...] ]
       ON TABLE [ IF EXISTS ] table_name [, ...]
       FROM { PUBLIC | role_name [, ...] }
       [ CASCADE | RESTRICT ];

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA schema_name FROM role_name;

Example (e-commerce):
REVOKE INSERT, UPDATE ON Orders FROM app_writer;
REVOKE SELECT ON Customers FROM marketing_user;

Common Mistakes

Revoking only table privileges but forgetting associated sequences. INSERT needs sequence USAGE; revoke both to block row creation.
Using CASCADE blindly. CASCADE also drops dependent privileges on objects owned by others, potentially breaking apps. Prefer RESTRICT or explicit revokes.