CODEWITHSUNDEEP
postgresqldatabase-permissions
tambakoo
tambakoo

Reputation: 363

Revoke particular columns in postgresql

I've tried using the documentation here but doesn't seem to help. If you can please give me an example.

revoke select (column1, column2) on table from specific_user

hasn't worked.

Access privileges
 Schema | Name  | Type  |     Access privileges     | Column privileges | Policies 
--------+-------+-------+---------------------------+-------------------+----------
 public | users | table | himanshu=arwdDxt/himanshu+|                   | 
        |       |       | reports_user=r/himanshu   |                   | 
(1 row)

Upvotes: 7

Views: 6369

Answers (2)

pink_daemon
pink_daemon

Reputation: 374

@Laurenz's answers is correct. I was however a bit confused with the syntax. Just to clarify:

I have table public.values:

+---------+---------+---------+
|    A    |    B    |    C    |
+---------+---------+---------+
| Value 1 | Value 2 | Value 3 |
+---------+---------+---------+

If user peter is supposed to see only the columns A and B, the following commands are needed:

REVOKE SELECT ON public.values FROM peter;
GRANT SELECT ("A", "B") ON public.values TO peter;

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 246483

The problem is that privileges in SQL are additive, and column privileges and table privileges are different.

Revoking a privilege that you didn't grant before has no effect, and granting SELECT on a table is different from granting SELECT on all columns.

You should revoke the SELECT privilege on the table and grant SELECT on all columns except the one where you want to deny access:

REVOKE SELECT ON "table" FROM specific_user;
GRANT SELECT (<all columns except "column1" and "column2">)
   ON "table" TO specific_user;

Check the result with \z "table".

Upvotes: 21

Related Questions