PostgreSQL – REVOKE Statement

ByAdmin

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.
  • CASCADE or RESTRICT: These optional keywords determine how the revocation affects dependent objects. CASCADE will revoke the privilege and also revoke it from objects that depend on the object being modified. RESTRICT will 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.

Similar Posts

PostgreSQL – USERS

ByAdmin

Database Users are accounts that can be used to log into the database system and perform various actions, such as querying the database, creating tables, and executing administrative tasks. PostgreSQL supports both regular users and superusers (also known as administrators). Creating a User: To create a user, you can use the CREATE USER command: CREATE…

PostgreSQL – Privileges

ByAdmin

Privileges control the actions that users and roles are allowed to perform on database objects such as tables, views, functions, and schemas. Privileges determine who can read, write, modify, or delete data within the database. PostgreSQL provides a fine-grained privilege system that allows you to control access at different levels of granularity. Here are some…

PostgreSQL – Roles

ByAdmin

Roles are used to manage authentication and authorization. Roles can represent users or groups of users, and they control who can access the database and what actions they are allowed to perform. PostgreSQL provides two main types of roles: user roles and group roles (also known as role hierarchies). User Roles: User roles represent individual…