PostgreSQL REVOKE Statement

Summary: in this tutorial, you will learn about the PostgreSQL REVOKE statement to remove privileges from a role.

Introduction to the PostgreSQL REVOKE statement

The REVOKE statement revokes previously granted privileges on database objects from a role.

The following shows the syntax of the REVOKE statement that revokes privileges on one or more tables from a role:

REVOKE privilege | ALL
ON TABLE table_name |  ALL TABLES IN SCHEMA schema_name
FROM role_name;

In this syntax:

• First, specify the one or more privileges that you want to revoke. You use the ALL option to revoke all privileges.
• Second, specify the name of the table after the ON keyword. You use the ALL TABLES to revoke specified privileges from all tables in a schema.
• Third, specify the name of the role from which you want to revoke privileges.

PostgreSQL REVOKE statement example

Let’s take an example of using the REVOKE statement.

1. Create a role and grant privileges

First, use the postgres user to log in to the dvdrental sample database:

psql -U postgres -d dvdrental

Second, create a new role called jim with the LOGIN and PASSWORD attributes:

CREATE ROLE jim 
LOGIN 
PASSWORD 'securePass1';

Third, grant all privileges on the film table to the role jim:

GRANT ALL 
ON film
TO jim;

Finally, grant the SELECT privilege on the actor table to the role jim:

GRANT SELECT
ON actor
TO jim;

2. Revoke privileges from a role

To revoke the SELECT privilege on the actor table from the role jim, you use the following statement:

REVOKE SELECT
ON actor
FROM jim;

To revoke all privileges on the film table from the role jim, you use REVOKE statement with the ALL option like this:

REVOKE ALL
ON film
FROM jim;

Revoking privileges on other database objects

To revoke privileges from other database objects such as sequences, functions, stored procedures, schemas, databases, check it out the REVOKE statement.

Summary

• Use the PostgreSQL REVOKE statement to revoke previously granted privileges on database objects from a role

---