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
– Laurenz Albe, Commented Jan 18, 2018 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
– tambakoo, Commented Jan 18, 2018 at 12:07

Laurenz Albe · Accepted Answer · 2019-06-07 08:24:23Z

22

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 7, 2019 at 8:24

answered Jan 18, 2018 at 12:36

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tambakoo Over a year ago

Should I enter all column names in (<all columns except "column1" and "column2">) ? This gives a syntax error at "<" . I have over 60 columns.

Laurenz Albe Over a year ago

Then enter 58 columns.

pink_daemon · Accepted Answer · 2024-01-05 17:26:16Z

3

@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;

edited Jan 5, 2024 at 17:26

answered Jun 28, 2022 at 10:03

pink_daemon

3943 silver badges6 bronze badges

Collectives™ on Stack Overflow

Revoke particular columns in postgresql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related