How to lock certain columns from being edited for a user in postgresql

Question

How to lock certain columns from being edited even though user have access to editing rights for the table in postgresql.

Answer 1

PostgreSQL supports Column Security (as well as Row Security)

Let's call our limited role authors

create table staff (
  name text primary key,
  salary decimal(19,4)
);

create role authors;

grant select, insert, delete, update(name) on table staff to authors;

set role authors;

insert into staff values ('frank', 100); -- works!

select * from staff; -- works!

update staff set name='jim'; -- works!

update staff set salary=999; -- permission denied

Answer 2

You can add a trigger that barfs if a forbidden column gets changed:

CREATE OR REPLACE FUNCTION cerberus() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF NEW.forbiddencol IS DISTINCT FROM OLD.forbiddencol
      AND current_user = 'luser'
   THEN
      RAISE EXCEPTION '"luser" must not update "forbiddencol"';
   END IF;
   RETURN NEW;
END;$$;

CREATE TRIGGER cerberus BEFORE UPDATE OF mytable
   FOR EACH ROW EXECUTE PROCEDURE cerberus();