Skip to main content
The REVOKE statement revokes privileges from users and/or roles. For the list of privileges that can be granted to and revoked from users and roles, see GRANT. You can use REVOKE to directly revoke privileges from a role or user, or you can revoke membership to an existing role, which effectively revokes that role’s privileges.

​
Syntax

REVOKE {ALL [PRIVILEGES] | privilege_list}
  ON {DATABASE | SCHEMA | TABLE | TYPE | FUNCTION | EXTERNAL CONNECTION} target [, ...]
  FROM role_spec [, ...]

REVOKE {role_name [, ...]} FROM {role_spec [, ...]}

​
Parameters

​
ALL | ALL PRIVILEGES
keyword
Revoke all privileges.
​
privilege_list
list
A comma-separated list of privileges to revoke.Supported privileges include:
  • SELECT - Read data from a table
  • INSERT - Insert data into a table
  • UPDATE - Update data in a table
  • DELETE - Delete data from a table
  • CREATE - Create objects in a database or schema
  • DROP - Drop objects from a database
  • GRANT - Grant privileges to other users
  • USAGE - Use a schema or type
  • EXECUTE - Execute a function
  • ZONECONFIG - Modify replication zones
  • CONNECT - Connect to a database
​
target
identifier
A comma-separated list of database, table, sequence, or function names. The list should be preceded by the object type (e.g., DATABASE mydatabase).
​
ALL SEQUENCES IN SCHEMA
clause
Revoke privileges on all sequences in a schema or list of schemas.
​
ALL TABLES IN SCHEMA
clause
Revoke privileges on all tables and sequences in a schema or list of schemas.
​
ALL FUNCTIONS IN SCHEMA
clause
Revoke privileges on all user-defined functions in a schema or list of schemas.
​
role_spec
identifier
A comma-separated list of roles.

​
Required privileges

  • To revoke privileges, user revoking privileges must have the GRANT privilege on the target database, schema, table, or user-defined type. In addition to the GRANT privilege, the user revoking privileges must have the privilege being revoked on the target object. For example, a user revoking the SELECT privilege on a table to another user must have the GRANT and SELECT privileges on that table.
  • To revoke role membership, the user revoking role membership must be a role admin (i.e., members with the WITH ADMIN OPTION) or a member of the admin role. To remove membership to the admin role, the user must have WITH ADMIN OPTION on the admin role.

​
Considerations

  • The root user cannot be revoked from the admin role

​
Examples

​
Revoke privileges on databases

CREATE USER max WITH PASSWORD 'roach';

GRANT CREATE ON DATABASE movr TO max;

SHOW GRANTS ON DATABASE movr;

  database_name | grantee | privilege_type | is_grantable
----------------+---------+----------------+---------------
  movr          | admin   | ALL            |      t
  movr          | max     | CREATE         |      f
  movr          | root    | ALL            |      t

REVOKE CREATE ON DATABASE movr FROM max;

SHOW GRANTS ON DATABASE movr;

  database_name | grantee | privilege_type | is_grantable
----------------+---------+----------------+---------------
  movr          | admin   | ALL            |      t
  movr          | root    | ALL            |      t
Any tables that previously inherited the database-level privileges retain the privileges.

​
Revoke privileges on specific tables

GRANT ALL ON TABLE rides TO max;

SHOW GRANTS ON TABLE rides;

  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  movr          | public      | rides      | admin   | ALL            |      t
  movr          | public      | rides      | max     | ALL            |      f
  movr          | public      | rides      | root    | ALL            |      t

REVOKE ALL ON TABLE rides FROM max;

SHOW GRANTS ON TABLE rides;

  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  movr          | public      | rides      | admin   | ALL            |      t
  movr          | public      | rides      | root    | ALL            |      t

​
Revoke privileges on all tables in a schema

GRANT ALL ON TABLE rides, users TO max;

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM max;
This is equivalent to: 
REVOKE ALL ON movr.public.* FROM max;

​
Revoke system-level privileges

GRANT SYSTEM MODIFYCLUSTERSETTING TO max;

SHOW SYSTEM GRANTS FOR max;

  grantee | privilege_type  | is_grantable
----------+-----------------+---------------
  max     | MODIFYCLUSTERSETTING |      f

REVOKE SYSTEM MODIFYCLUSTERSETTING FROM max;

SHOW SYSTEM GRANTS FOR max;

  grantee | privilege_type | is_grantable
----------+----------------+---------------
(0 rows)

​
Revoke role membership

CREATE ROLE developer WITH CREATEDB;

CREATE USER abbey WITH PASSWORD 'lincoln';

GRANT developer TO abbey;

SHOW GRANTS ON ROLE developer;

  role_name | member | is_admin
------------+--------+-----------
  developer | abbey  |  false

REVOKE developer FROM abbey;

SHOW GRANTS ON ROLE developer;

  role_name | member | is_admin
------------+--------+-----------
(0 rows)

​
Revoke the admin option

GRANT developer TO abbey WITH ADMIN OPTION;

SHOW GRANTS ON ROLE developer;

  role_name | member | is_admin
------------+--------+-----------
  developer | abbey  |   true

REVOKE ADMIN OPTION FOR developer FROM abbey;

SHOW GRANTS ON ROLE developer;

  role_name | member | is_admin
------------+--------+-----------
  developer | abbey  |  false

​
See also

  • GRANT
  • SHOW GRANTS
  • CREATE ROLE
  • ALTER ROLE
  • DROP ROLE
  • Privileges

Build docs developers (and LLMs) love

Get started for freeTalk to us