Revoking user access to a table
This recipe answers the question How do I make sure that user X cannot access table Y?
Getting ready
The current user must either be a superuser, the owner of the table, or a user with a GRANT
option for the table.
Also, bear in mind that you can't revoke rights from a user who is a superuser.
How to do it…
To revoke all rights on the table1
table from the user2
user, you must run the following SQL command:
REVOKE ALL ON table1 FROM user2;
However, if user2
has been granted another role that gives them some rights on table1
—say, role3
—this command is not enough; you must also choose one of the following options:
- Fix the user—that is, revoke
role3
fromuser2
- Fix the role—that is, revoke privileges on
table1
fromrole3
Both choices are imperfect because of their side effects. The former will revoke all of the privileges associated with role3
, not just the privileges concerning...