Revoking user access to tables

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 the rights of 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 with role3, not just the privileges concerning...