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 from user2
• Fix the role—that is, revoke privileges on table1 from role3

Both choices are imperfect because of their side-effects. The former will revoke all of the privileges associated to role3, not just the privileges concerning table1; the latter will revoke the privileges on table1 from all of the other users that have...