PostgreSQL Tutorial: REVOKE

September 23, 2023

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

Table of Contents

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.

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

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;

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;

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

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