PostgreSQL revoke privileges from column

Question

I have a user group named editor_users in my PostgreSQL 9.5 database. And my product table is granted select, insert, update and delete to editor_user members.

But I want to prevent my id column. Nobody may not update id column. How can I revoke update privileges from users?

score 5 · Accepted Answer · answered Jan 05 '18 at 07:42

5

You could give privileges for every column. Assuming you have a table like the following:

CREATE TABLE product (
    id serial primary key,
    mytext text
);

You can grant privileges to editor_user like that:

GRANT SELECT(id), INSERT(id) ON product TO editor_user;
GRANT SELECT(mytext), UPDATE(mytext), INSERT(mytext), REFERENCES(mytext) ON product TO editor_user;

answered Jan 05 '18 at 07:42

Max

160
1
11

I have 15 columns. Does REVEOKE comment do this only one command? I do not want to write column names for all tables. – barteloma Jan 05 '18 at 07:45
1

I guess no. `REVOKE UPDATE(id) ON product FROM GROUP editor_user;` will have no effect, if you granted all on the complete table. – Max Jan 05 '18 at 07:55
You could write a script with `SELECT * FROM information_schema.column_privileges WHERE table_name = 'product'` and change privileges for all columns except `id` – Max Jan 05 '18 at 08:03
3

excerpt from [https://www.postgresql.org/docs/9.0/static/sql-grant.html] (postgresql) "Granting the privilege at the table level and then revoking it for one column will not do what you might wish: the table-level grant is unaffected by a column-level operation." – Max Jan 05 '18 at 08:15

score 3 · Answer 2 · answered Jan 05 '18 at 11:25

You have two options here. The first is to revoke to the table and grant to the columns. If you do this, then it is worth using the Indoemation schema or system catalogs to discover all the relevant columns and programmatically creating the grant statements. If you go that route array_agg and array_to_string are your friends.

for example you could:

revoke all on table product from public, ...;
select 'grant insert(' || array_to_string(array_agg(attname), ', ') || ') to ... ;' 
  from pg_attribute
 where attrelid = 'product'::regclass and attnum > 0;

Then copy and paste the output into the psql window.

PostgreSQL revoke privileges from column

2 Answers2

Linked