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?
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;
REVOKE UPDATE(id) ON product FROM GROUP editor_user; will have no effect, if you granted all on the complete table.SELECT * FROM information_schema.column_privileges WHERE table_name = 'product' and change privileges for all columns except idYou 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 Information 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.
