By default, a normal user has no all privileges on any database object that they do not own. To explicitly revoke
a right after it has been granted, the object's owner (or a superuser) can issue the REVOKE command.
This command is very similar in form to the GRANT command.
Here is the syntax for REVOKE:
REVOKE
privilege
[, ...] ON
object
[, ...]
FROM { PUBLIC |
username
| GROUP
groupname
}
The structure of the REVOKE command syntax is identical to that of the
GRANT command, with the exception that the SQL command itself
is REVOKE rather than GRANT, and the
keyword FROM is used, rather than the TO
keyword.
Note: Revoking privileges from PUBLIC only affects the special
"public" group, which includes all users. Revoking rights from PUBLIC will not affect
any users who have been explicitly granted those privileges.
Suppose the UPDATE rights on the books table
have been granted to the user david. When David is transferred
to another department, and no longer needs the ability to modify book information, you should revoke David's UPDATE privilege
on the books table.
Example 10-19 uses the \z slash command in
psql
to check the permissions on the books table, revealing
that david has write-access privileges to that table. A
REVOKE statement then explicitly revokes the UPDATE and
DELETE privileges on the books table from the user
david. Finally, another \z slash command is executed
to verify the removal of the privilege.
Example 10-19. Revoking rights
booktown=#
\z books
Access permissions for database "booktown"
Relation | Access permissions
----------+--------------------------------
books | {"=","manager=arwR","david=w"}
(1 row)
booktown=#
REVOKE UPDATE, DELETE ON books
booktown-#
FROM david;
CHANGE
booktown=#
\z books
Access permissions for database "booktown"
Relation | Access permissions
----------+----------------------
books | {"=","manager=arwR"}
(1 row)
