PostgreSQL REVOKE Statement

Summary: In this tutorial, you’ll learn how to remove previously granted privileges on an object from a role using the PostgreSQL REVOKE statement.

PostgreSQL REVOKE Statement Overview #

The REVOKE statement removes previously granted privileges from roles, including:

• Users – roles with LOGIN privilege.
• Groups
• PUBLIC – a pseudo-role

Here’s the syntax of the REVOKE statement:

REVOKE privilege1, privilege2 
ON object_type object_name
FROM
  role_name1,
  role_name2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• privilege – a specific privilege to revoke, such as SELECT, INSERT, UPDATE, and DELETE.
• object_type – the type of database object TABLE, VIEW, etc.
• object_name – the name of the database object.
• role_name – the name of the role from which you want to revoke the privilege.

Each object type has certain privileges.

PostgreSQL REVOKE Statement Example #

We’ll create a new role, grant privileges, and revoke one of them.

Step 1: Connect to your PostgreSQL and create a role called dev:

CREATE ROLE dev
WITH LOGIN PASSWORD 'securepassword';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Replace the 'securepassword' with your password.

Step 2: Create a new table called logs:

CREATE TABLE logs (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  message VARCHAR NOT NULL
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 3: Grant INSERT and SELECT privileges on the logs table to the dev user:

GRANT SELECT, INSERT ON
TABLE logs
TO dev;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The dev user can insert data into the logs table and select data from it.

Step 4: Verify the granted privileges:

SELECT
  grantee,
  privilege_type
FROM
  information_schema.role_table_grants
WHERE
  table_name = 'logs'
  AND grantee = 'dev';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 5: Revoke INSERT privilege from the dev role:

REVOKE INSERT ON logs
FROM dev;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Step 6: Verify that the INSERT privilege has been revoked successfully:

SELECT
  grantee,
  privilege_type
FROM
  information_schema.role_table_grants
WHERE
  table_name = 'logs'
  AND grantee = 'dev';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the REVOKE statement to revoke a previously granted privilege.