O'Reilly logo

PostgreSQL 9 Administration Cookbook by Hannu Krosing, Simon Riggs

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 the user X cannot access the table Y?"

Getting ready

The current user must either be a superuser, the owner of the table, or must have 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 to table mysecrettable from user userwhoshouldnotseeit, one must run the following SQL command:

REVOKE ALL ON mysecrettable FROM userwhoshoudnotseeit;

However, because the table is usually also accessible to all users through role PUBLIC, the following must also be run:

REVOKE ALL ON mysecrettable FROM PUBLIC;

How it works...

By default all users have a set of rights (SELECT, INSERT, UPDATE, DELETE, TRUNCATE, ...

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