REVOKE

The REVOKE statement removes permissions from a role. It allows for removing permissions to specific objects.

Learn more about the permission system in the access control guide.

See also GRANT, DROP ROLE.

Permissions

To revoke permissions, the current role must have the SUPERUSER permission, or have the ADMIN OPTION.

Syntax

-- Revoke permissions at the instance/ storage cluster level:
REVOKE
{
  SUPERUSER
  | LOGIN
  | PASSWORD
}
FROM <role> [, ...]

-- Revoke permissions at the database level:
REVOKE
{
  { CREATE
  | CONNECT
  | DDL
  | SUPERUSER
  | CREATE FUNCTION }[, ...]
  | ALL [PERMISSIONS]
}
ON DATABASE <database> [, ...]
FROM <role> [, ...]

-- Revoke permissions at the schema level:
REVOKE
{
  { CREATE
  | DDL
  | USAGE
  | SUPERUSER } [, ...]
  | ALL [PERMISSIONS]
}
ON SCHEMA <schema> [, ...]
FROM <role> [, ...]

-- Revoke permissions at the object level:
REVOKE
{
  { SELECT
  | INSERT
  | DELETE
  | DDL
  | UPDATE } [, ...]
  | ALL
}
ON
{
  [ TABLE ] <table_name> [, ...]
  | ALL TABLES IN SCHEMA <schema_name> [, ...]
  | VIEW <schema_name.view_name> [, ...]
  | ALL VIEWS IN SCHEMA <schema_name> [, ...]
  | FOREIGN TABLE <table_name> [, ...]
  | ALL FOREIGN TABLES IN SCHEMA <schema_name> [, ...]
}
FROM <role> [, ...]

-- Revoke permissions at the catalog level:

REVOKE SELECT
ON { CATALOG <catalog_name> [, ...] }
FROM <role> [, ...]

-- Revoke permissions at the column level:
REVOKE
{
  { SELECT
  | DDL } [, ...]
  | ALL [PERMISSIONS]}
ON
{
  COLUMN <column_name> [,<column_name_2>] IN TABLE <table_name> [,<table_name2>] | COLUMN <column_name> [,<column_name_2>] IN FOREIGN TABLE <table_name> [,<table_name2>]
  | ALL COLUMNS IN TABLE <schema_name.table_name> [, ...]
  | ALL COLUMNS IN FOREIGN TABLE <schema_name.foreign_table_name> [, ...]
}
FROM <role> [, ...]

-- Revoke permissions at the service level:
REVOKE
{
  { USAGE } [, ...]
  | ALL [PERMISSIONS]
}
ON { SERVICE <service_name> }
FROM <role> [, ...]

-- Removes access to permissions in role1 by role 2
REVOKE [ADMIN OPTION FOR] <role1> [, ...]
FROM <role2> [, ...]

-- Removes permissions to grant role1 to additional roles from role2
REVOKE [ADMIN OPTION FOR] <role1> [, ...]
FROM <role2> [, ...]

Parameters

Parameter	Description
role_name	The name of the role to revoke permissions from
table_name, database_name, schema_name, function_name, catalog_name, column_name, service_name	Object to revoke permissions from
WITH ADMIN OPTION	If WITH ADMIN OPTION is specified, the role that has the admin option can in turn grant membership in the role to others, and revoke membership in the role as well. Specifying WITH ADMIN OPTION for revocation will return the role to an ordinary role. An ordinary role cannot grant or revoke membership.

Examples

Prevent a role from modifying table contents

If you don’t trust user shifty, reokve DDL and INSERT permissions.

REVOKE INSERT ON TABLE important_table FROM shifty;
REVOKE DDL ON TABLE important_table FROM shifty;

Demoting a user from superuser

-- On the entire cluster
REVOKE SUPERUSER FROM new_role;

Revoking admin option

If WITH ADMIN OPTION is specified, the role that has the admin option can in turn grant membership in the role to others, and revoke membership in the role as well.

-- dba_user1 has been demoted from team lead, so he should not be able to grant
-- permissions to other users.

REVOKE r_database_architect FROM dba_user1 WITH ADMIN OPTION;