Revoke particular columns in postgresql

Question

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)

Please edit the question to include the output of `\z table` run in `psql`. — Laurenz Albe, Jan 18 '18 at 11:23
I first grant users select to reports_user, then I revoke column1 and column2 with the revoke syntac above. Despite doing that I can still access column1 and column 2 from reports user. — tambakoo, Jan 18 '18 at 12:07

Laurenz Albe · Accepted Answer · 2019-06-07T08:24:23.287

14

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".

edited Jun 07 '19 at 08:24

answered Jan 18 '18 at 12:36

Laurenz Albe

209,280
17
206
263

4

Should I enter all column names in ```()``` ? This gives a syntax error at "<" . I have over 60 columns. – tambakoo Jan 18 '18 at 13:00
3

Then enter 58 columns. – Laurenz Albe Jan 18 '18 at 13:27

score 0 · Answer 2 · answered Jun 28 '22 at 10:03

@Laurenz's answers is 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;

Revoke particular columns in postgresql

2 Answers2