How to Revoke Privileges in PostgreSQL: Syntax and Examples

In PostgreSQL, you use the REVOKE command to remove privileges from a user or role. The syntax is REVOKE privilege_type ON object FROM user;, which lets you specify what permission to take away and from whom.

Syntax

The REVOKE command removes specific privileges from a user or role on a database object like a table or schema.

• privilege_type: The permission to remove, such as SELECT, INSERT, or ALL PRIVILEGES.
• object: The database object (table, schema, database) on which the privilege was granted.
• user: The user or role from whom you want to revoke the privilege.

sql

REVOKE privilege_type ON object FROM user;

Example

This example shows how to revoke the SELECT privilege on a table named employees from a user called john. After running this, john will no longer be able to read data from the employees table.

sql

REVOKE SELECT ON employees FROM john;

Output

REVOKE

Common Pitfalls

Common mistakes when revoking privileges include:

• Trying to revoke privileges that were never granted, which has no effect but can confuse users.
• Not specifying the correct object or user, so the intended privilege is not revoked.
• Assuming REVOKE ALL PRIVILEGES removes all possible rights; it only removes those explicitly granted.

Always check current privileges with \dp in psql or query information_schema.role_table_grants before revoking.

sql

/* Wrong: revoking from wrong user */
REVOKE SELECT ON employees FROM alice;

/* Right: revoking from correct user */
REVOKE SELECT ON employees FROM john;

Output

REVOKE
REVOKE

Quick Reference

Command	Description
REVOKE SELECT ON table_name FROM user;	Remove read access on a table
REVOKE INSERT ON table_name FROM user;	Remove insert permission on a table
REVOKE ALL PRIVILEGES ON database_name FROM user;	Remove all granted privileges on a database
REVOKE USAGE ON schema_name FROM user;	Remove usage rights on a schema

Key Takeaways

Use the REVOKE command to remove specific privileges from users or roles in PostgreSQL.

Always specify the exact privilege, object, and user to avoid unintended effects.

Check existing privileges before revoking to ensure accuracy.

REVOKE only removes privileges that were explicitly granted.

Use \dp in psql or query information_schema to review current permissions.