The “REVOKE” statement revokes granted privileges from single or multiple roles in the PostgreSQL database. The granted privileges represent those rights that have already been granted to database objects. These objects include tables, schemas, indexes, functions, and many more. Today, we will guide you on how to use the “REVOKE” statement in PostgreSQL.

Usage of REVOKE Privileges in PostgreSQL

Users can use the “REVOKE” statement to remove the specific privilege from the existing role in the database. It is possible by specifying the REVOKE statement followed by the user's name from whom you want to revoke the privileges. For instance, the syntax is provided below.

Syntax

REVOKE priv_list ON obj FROM role_name;

The description of parameters is illustrated as follows:

The priv_list contains the number of privileges that the user wants to revoke. The obj specifies the database objects, such as schemas, tables, indexes, functions, etc. The role_name represents the name of the role from which the privileges are being revoked.

The priv_list may include:

- INSERT
- SELECT
- UPDATE
- CREATE
- DELETE
- CONNECT
- TRUNCATE
- TRIGGER
- EXECUTE
- REFERENCES

Note: You can utilize ALL keyword for revoking all privileges on database objects.

This tutorial comprises step-by-step instructions for revoking privileges in PostgreSQL.

Step 1: Create a New Database

Let’s create a new database named db_std using the “CREATE DATABASE” statement. In this database, various operations will be performed regarding privileges:

CREATE DATABASE db_std;

By executing the above statement, the database “db_std” has been successfully created. Now user can switch from the default database “postgres” to “db_std”.

Step 2: Establish Connection with Database

After creating a database, establish a connection by executing the “\c” statement followed by the name of the desired database:

\c db_std;

The output authenticates that we are successfully connected to the “db_std” database.

Step 3: Creating a New Role

Let’s create a new role with the login attribute:

CREATE ROLE tech_role login password 'zar422';

User can confirm through the output message that “tech_role” has been successfully created.

Step 4: Create a Table

In this step, a table “tech_tab” is created through the “CREATE TABLE” statement:

CREATE TABLE tech_tab(
f_name varchar(100) not null, 
l_name varchar(100) not null);

The table “tech_tab” has been created with two columns: “f_name” and “l_name”.

Step 5: GRANT Privileges

The “GRANT” statement is utilized to assign the privilege of table “tech_tab” to the selected role named “tech_role”. You can override the privileges through the “GRANT” statement in PostgreSQL.

GRANT SELECT ON tech_tab TO tech_role;

The “GRANT” message in the output confirms that privilege has been successfully granted to the particular role.

Step 6: REVOKE Privileges

PostgreSQL offers the “REVOKE” statement to remove the privilege from a specific role:

REVOKE SELECT ON tech_tab FROM tech_role;

In the above statement, privileges of “tech_role” have been revoked, which authenticates through the “REVOKE” message in the output.

Step 6: Authenticate the Working of REVOKE Statement

Login as a tech_role:

Now perform any operation, like insert, update, delete, etc., on the “tech_tab” table. Let’s say the user wants to insert a new row into the tech_tab. To do that, execute the INSERT command as follows:

INSERT INTO tech_tab(f_name, l_name)
VALUES(‘tim’, ‘stoke’);

The output shows that an error occurred when we tried to insert the data into the tech_tab table. It proves that the REVOKE statement has successfully removed the privileges.

That’s it! You have learned the practical implementation of revoke privileges in PostgreSQL.

Conclusion

In PostgreSQL, the “REVOKE” statement is quite helpful in revoking the granted privileges from single or multiple roles. The priv_list may include: INSERT, SELECT, UPDATE, CREATE, DELETE, CONNECT, TRUNCATE, TRIGGER, EXECUTE, and REFERENCES. This tutorial has provided the step-by-step procedure to revoke the rights of the existing role in PostgreSQL.