Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL REVOKE

The `REVOKE` command in PostgreSQL is used to remove previously granted privileges from a user or role. It is essential for managing access to database objects, ensuring data security and compliance.

Usage

The `REVOKE` command is utilized when there is a need to withdraw permissions from users or roles on specific database objects, such as tables or schemas. It is often used to tighten security or adjust access controls.

REVOKE privilege [, ...]
ON object_name [, ...]
FROM role_name [, ...];

In this syntax, `REVOKE` removes the specified privileges from the defined roles on the designated objects. Commas are used to separate multiple privileges, objects, or roles.

Privilege Types

Common privileges that can be revoked include:

  • `SELECT`
  • `INSERT`
  • `UPDATE`
  • `DELETE`
  • `TRUNCATE`
  • `REFERENCES`
  • `TRIGGER`

Examples

1. Basic Table Privilege Revocation

REVOKE SELECT
ON employees
FROM user123;

This example revokes the `SELECT` privilege on the `employees` table from the user `user123`. If `user123` did not have the `SELECT` privilege, this command will have no effect.

2. Revoking Multiple Privileges

REVOKE INSERT, UPDATE
ON projects
FROM manager_role;

Here, both `INSERT` and `UPDATE` privileges are removed from the `manager_role` on the `projects` table, limiting their ability to modify data.

3. Revoking All Privileges from a Schema

REVOKE ALL PRIVILEGES
ON SCHEMA sales_data
FROM analyst_role;

This example removes all privileges granted to `analyst_role` on the `sales_data` schema, effectively preventing any interaction with the schema's objects.

Tips and Best Practices

  • Review privileges regularly. Regular audits of user privileges can help maintain security and proper access controls.
  • Use roles instead of individual users. Managing privileges through roles simplifies administration and scalability.
  • Test privilege changes in a development environment. Before applying changes in production, testing in a development setup can prevent accidental access issues.
  • Document privilege changes. Keeping records of changes ensures traceability and helps in future audits or troubleshooting.
  • Understand default privileges and inherited roles. Revoking privileges does not affect privileges granted to any roles the user might be part of.
  • Be aware of default role behavior. Understanding how default privileges and roles interact can prevent unintended access issues.