![PostgreSQL](https://cdn-0.reconshell.com/wp-content/uploads/2021/04/PostgreSQL.png)
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 theALL 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
Leave a Reply