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?

Answer 1

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;

Answer 2

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.