PostgreSQL – REVOKE Statement
The REVOKE statement is used to remove previously granted privileges from users or roles on database objects. The REVOKE statement allows you to control access and privileges by revoking certain permissions that were previously granted.
Here is the basic syntax of the REVOKE statement:
REVOKE privilege_name [, ...] ON object_name FROM user_or_role [, ...] [CASCADE | RESTRICT];privilege_name: The name of the privilege you want to revoke (e.g., SELECT, INSERT, UPDATE, DELETE, EXECUTE, etc.).object_name: The name of the database object from which you want to revoke the privilege (e.g., table name, view name, function name, etc.).user_or_role: The user or role from whom you want to revoke the privilege.CASCADEorRESTRICT: These optional keywords determine how the revocation affects dependent objects.CASCADEwill revoke the privilege and also revoke it from objects that depend on the object being modified.RESTRICTwill only revoke the privilege if no dependent objects exist.
Here are a few examples of how the REVOKE statement can be used:
Revoking SELECT privilege on a table from a user:
REVOKE SELECT ON table_name FROM username;Revoking multiple privileges on a table from a role:
REVOKE SELECT, INSERT, UPDATE ON table_name FROM rolename;Revoking EXECUTE privilege on a function from a role:
REVOKE EXECUTE ON FUNCTION function_name(arg_type, ...) FROM rolename;Revoking all privileges on a schema from a user:
REVOKE ALL PRIVILEGES ON SCHEMA schema_name FROM username;Revoking a privilege with cascading:
REVOKE SELECT ON table_name FROM username CASCADE;The REVOKE statement allows you to manage access control and permissions within your PostgreSQL database. Careful consideration should be given when revoking privileges, as it can affect users’ ability to perform their tasks.