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 \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:

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 tries to revoke this privilege it fails if he specifies the RESTRICT keyword.