REVOKE
Name
REVOKE — Revokes access privilege from a user, a group or all users.
REVOKE privilege [, ...]
ON object [, ...]
FROM { PUBLIC | GROUP group | 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
index
- 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 the GRANT command 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 the GRANT command 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.
Report a documentation issue
Read the upstream documentation. For corrections, first check the current manual.