O'Reilly logo

PostgreSQL 9 Administration Cookbook - Second Edition by Simon Riggs, Gianni Ciolli, Hannu Krosing, Gabriele Bartolini

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, 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 had 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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required