Clever Geek Handbook

How to lock specific columns for editing for a user in postgresql

How to block certain columns from editing even if the user has edit access to the table in postgresql.

+3


source to share


2 answers


You can add a trigger that barfs if the forbidden column is 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();
+1


source


PostgreSQL supports column security (as well as row security)

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
+3


source






More articles:


All Articles