PostgreSQL · January 2, 2024

PostgreSQL Command: REVOKE

PostgreSQL Command: REVOKE

PostgreSQL is a powerful open-source relational database management system that offers a wide range of features and functionalities. One of the essential commands in PostgreSQL is the REVOKE command, which allows database administrators to control access privileges for database objects.

Understanding Access Privileges

In PostgreSQL, access privileges determine who can perform specific actions on database objects such as tables, views, functions, and schemas. These privileges can be granted to or revoked from users or roles.

There are several types of privileges in PostgreSQL:

  • SELECT: Allows reading data from a table or view.
  • INSERT: Allows inserting new rows into a table.
  • UPDATE: Allows modifying existing rows in a table.
  • DELETE: Allows deleting rows from a table.
  • USAGE: Allows using a schema or a language.
  • EXECUTE: Allows executing a function or procedure.

The REVOKE Command

The REVOKE command in PostgreSQL is used to revoke previously granted privileges from users or roles. The basic syntax of the REVOKE command is as follows:

REVOKE privilege_name [(column_name [, ...])]
  ON object_name
  FROM {user_name | PUBLIC | role_name} [, ...]
  [CASCADE | RESTRICT];

Let's break down the different parts of the syntax:

  • privilege_name: Specifies the name of the privilege to be revoked.
  • column_name: Specifies the name of the column for which the privilege should be revoked (optional).
  • object_name: Specifies the name of the object from which the privilege should be revoked.
  • user_name | PUBLIC | role_name: Specifies the user, role, or the special keyword PUBLIC from which the privilege should be revoked.
  • CASCADE | RESTRICT: Specifies whether the revocation should cascade to dependent objects or not (optional).

Examples

Let's look at a few examples to understand how the REVOKE command works:

Example 1: Revoking SELECT Privilege

To revoke the SELECT privilege on a table named employees from a user named john, you can use the following command:

REVOKE SELECT ON employees FROM john;

This command will remove the SELECT privilege from the user john for the employees table.

Example 2: Revoking Multiple Privileges

You can also revoke multiple privileges at once. For example, to revoke both the INSERT and UPDATE privileges on a table named products from a role named manager, you can use the following command:

REVOKE INSERT, UPDATE ON products FROM manager;

This command will revoke both the INSERT and UPDATE privileges from the role manager for the products table.

Summary

The REVOKE command in PostgreSQL is a powerful tool for controlling access privileges to database objects. It allows database administrators to revoke previously granted privileges from users or roles. By using the REVOKE command, you can ensure that only authorized users have the necessary privileges to perform specific actions on your PostgreSQL database.

For more information about PostgreSQL and its features, consider exploring Server.HK, a leading VPS hosting company that offers reliable and secure hosting solutions.

You may also like...