How to Revoke Permissions in PostgreSQL

How do I revoke permissions in PostgreSQL?

REVOKE removes previously granted privileges from roles or the public group, tightening access to database objects.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why revoke privileges in PostgreSQL?

Revoking permissions is essential when a user’s job changes, when sensitive data moves tables, or when least-privilege policies are enforced. It guarantees that only the right roles can query, insert, or alter data.

What is the basic REVOKE syntax?

Use REVOKE privilege[, ...] ON object FROM role[, ...] [CASCADE | RESTRICT].Privileges include SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER, and ALL.

How to revoke SELECT from a table?

Run REVOKE SELECT ON TABLE Customers FROM analytics_reader;.The role analytics_reader can no longer read the Customers table.

How to revoke all privileges for a role?

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM developer_temp; removes every right that developer_temp once had in the public schema.

How to revoke default future privileges?

ALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE SELECT ON TABLES FROM data_viewer; stops automatic SELECT rights on new tables.

How to check current privileges?

Query information_schema.role_table_grants or run \dp in psql to list grants and confirm that revocations succeeded.

Best practices for revoking

Create functional roles, grant only what is needed, test in staging, and script revocations alongside grants for reproducibility.

Why How to Revoke Permissions in PostgreSQL is important

How to Revoke Permissions in PostgreSQL Example Usage


-- Scenario: remove read access after an audit
BEGIN;
REVOKE SELECT ON TABLE Customers FROM read_only;
REVOKE SELECT ON TABLE Orders FROM read_only;
COMMIT;

How to Revoke Permissions in PostgreSQL Syntax


REVOKE [ GRANT OPTION FOR ] privilege [, ...]
ON { TABLE | SEQUENCE | DATABASE | SCHEMA | FUNCTION | ALL TABLES IN SCHEMA schema_name | ALL SEQUENCES IN SCHEMA schema_name }
FROM { role_name | PUBLIC } [, ...]
[ CASCADE | RESTRICT ];

-- Ecommerce examples
-- Revoke SELECT on one table
REVOKE SELECT ON TABLE Customers FROM analytics_reader;

-- Revoke UPDATE, DELETE on multiple tables
REVOKE UPDATE, DELETE ON Products, Orders FROM api_service;

-- Revoke all privileges in a schema
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA reporting FROM contractor_role CASCADE;

Common Mistakes

Using REVOKE without CASCADE when dependent privileges exist. Fix: add CASCADE or manually drop dependent grants first.
Revoking from individual users instead of roles, creating administration overhead. Fix: grant and revoke on role groups, then add or remove users from those roles.