For a PostgreSQL table I would like to lock specific rows from future editing. This is part of a process of signing off audited data.
From the documentation, and other threads, everything to do with locking is related to locking rows temporarily whilst other database processes are running. However I want to prevent users indefinitely being able to modify data that has been audited.
Does anyone have any experience or thoughts on doing this?
A row level security policy can do that: demo at db<>fiddle
create table test (
id int generated by default as identity primary key,
payload text,
is_locked boolean default false);
insert into test values
(default,'Auditing is due to review this one.',default)
,(default,'We are waiting for legal to sign off on this.',false)
,(default,'This one passed audit and is locked.',true);
create role mc_hammer;
grant usage on schema public to mc_hammer;
alter table test enable row level security;
create policy can_see_this on test for select
to mc_hammer using (true);
create policy cant_touch_this_i on test for insert
to mc_hammer with check (not is_locked);
create policy cant_touch_this_u on test for update
to mc_hammer using (not is_locked)
with check (not is_locked);
create policy cant_touch_this_d on test for delete
to mc_hammer using (not is_locked);
grant select,insert,update,delete,references,trigger on test to mc_hammer;
Now user mc_hammer can modify the rows where is_locked=false, like row 1:
set role mc_hammer;
update test
set payload=current_user||' touched this '||now()::text
where id=1
returning *;
| id | payload | is_locked |
|---|---|---|
| 1 | mc_hammer touched this 2024-06-14 10:18:06.844776+00 | F |
But row 3 is locked, so they won't be able to update or delete it. Upserts with insert..on conflict do update or merge won't work either. They also cannot insert an already locked row. It just doesn't work, without throwing an error, as if the using()/with check() condition were quietly added to all their queries against that table, filtering out the rows they're not supposed to touch:
update test
set payload=current_user||' touched this '||now()::text
where id=3
--and (not is_locked)--added by RLS, stops this from matching any row
returning *;
| id | payload | is_locked |
|---|
UPDATE 0
set role postgres;
select * from test;
| id | payload | is_locked |
|---|---|---|
| 1 | mc_hammer touched this 2024-06-14 10:18:06.844776+00 | F |
| 2 | We are waiting for legal to sign off on this. | F |
| 3 | This one passed audit and is locked. | T |
mc_hammer tried to touch both 1 and 3, but it only worked for 1 because 3 was locked.