REVOKE

PostgreSQL
Prev		Next

REVOKE

Name

REVOKE — Revokes access privilege from a user, a group or all users.

REVOKE privilege [, ...]
    ON object [, ...]
    FROM { PUBLIC | GROUP groupname | username }

Inputs

privilege

The possible privileges are:

SELECT: Privilege to access all of the columns of a specific table/view.
INSERT: Privilege to insert data into all columns of a specific table.
UPDATE: Privilege to update all columns of a specific table.
DELETE: Privilege to delete rows from a specific table.
RULE: Privilege to define rules on table/view. (See CREATE RULE).
ALL: Rescind all privileges.

object

The name of an object from which to revoke access. The possible objects are:

table
view
sequence

group

The name of a group from whom to revoke privileges.

username

The name of a user from whom revoke privileges. Use the PUBLIC keyword to specify all users.

PUBLIC

Rescind the specified privilege(s) for all users.

Outputs

CHANGE: Message returned if successfully.
ERROR: Message returned if object is not available or impossible to revoke privileges from a group or users.

Description

REVOKE allows creator of an object to revoke permissions granted before, from all users (via PUBLIC) or a certain user or group.

Notes

Refer to psql \z command for further information about permissions on existing objects:

Database    = lusitania
+------------------+---------------------------------------------+
|  Relation        |        Grant/Revoke Permissions             |
+------------------+---------------------------------------------+
| mytable          | {"=rw","miriam=arwR","group todos=rw"}      |
+------------------+---------------------------------------------+
Legend:
     uname=arwR -- privileges granted to a user
     group gname=arwR -- privileges granted to a GROUP
     =arwR -- privileges granted to PUBLIC
		  
     r -- SELECT
     w -- UPDATE/DELETE
     a -- INSERT
     R -- RULE
     arwR -- ALL

Tip: Currently, to create a GROUP you have to insert data manually into table pg_group as:
INSERT INTO pg_group VALUES ('todos');
CREATE USER miriam IN GROUP todos;
     

Usage

Revoke insert privilege from all users on table films:

REVOKE INSERT ON films FROM PUBLIC;

Revoke all privileges from user manuel on view kinds:

  
REVOKE ALL ON kinds FROM manuel;

Compatibility

SQL92

The SQL92 syntax for REVOKE has additional capabilities for rescinding privileges, including those on individual columns in tables:

REVOKE { SELECT | DELETE | USAGE | ALL PRIVILEGES } [, ...] ON object FROM { PUBLIC | username [, ...] } { RESTRICT | CASCADE } REVOKE { INSERT | UPDATE | REFERENCES } [, ...] [ ( column [, ...] ) ] ON object FROM { PUBLIC | username [, ...] } { RESTRICT | CASCADE }: Refer to GRANT for details on individual fields.
REVOKE GRANT OPTION FOR privilege [, ...] ON object FROM { PUBLIC | username [, ...] } { RESTRICT | CASCADE }: Rescinds authority for a user to grant the specified privilege to others. Refer to GRANT for details on individual fields.

The possible objects are:

[ TABLE ] table/view

CHARACTER SET character-set

COLLATION collation

TRANSLATION translation

DOMAIN domain

If user1 gives a privilege WITH GRANT OPTION to user2, and user2 gives it to user3 then user1 can revoke this privilege in cascade using the CASCADE keyword.

If user1 gives a privilege WITH GRANT OPTION to user2, and user2 gives it to user3 then if user1 try revoke this privilege it fails if he/she specify the RESTRICT keyword.

Prev	Home	Next
RESET	Up	ROLLBACK