0

I literally want to prevent specific user from deleting a records from table, but at the same time to log what he wanted to delete.

I tried two approaches but first one with COMMIT inside throws an ERROR of

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function prevent_del_trg() line 7 at COMMIT

the second one was to split it into two triggers, but then nothing is saved into log table (and my exception is thrown).

Is there any other way that could do the job for my case? I'm working on PG12

DBFIDDLE

create table test (id serial, value text);
insert into test (value)
select 'test'||generate_series(10,20);
create table test_del_log (id integer, value text, del_date timestamp );


CREATE OR REPLACE FUNCTION prevent_del_trg (
)
RETURNS trigger
    LANGUAGE 'plpgsql'
AS
$body$
BEGIN 
--IF current_user = 'X'  THEN
 INSERT INTO test_del_log
    VALUES (old.*,  now()) ;
--END IF;
COMMIT;

--IF current_user = 'X' THEN
RAISE EXCEPTION 'Delete guard. Access denied for user X';
--ELSE RETURN NEW;
--END IF;

END;
$body$;

CREATE TRIGGER test_prevent_del_trg
    BEFORE DELETE
    ON public.test
    FOR EACH ROW
    EXECUTE PROCEDURE prevent_del_trg();
sh4rkyy
  • 343
  • 2
  • 19
  • Is there a reason you need to raise the exception? If not, maybe you could just do something like `IF current_user = 'X' THEN RETURN null; ELSE return old; END IF;` Then the `insert` should be committed whenever the caller is committed. – EdmCoff Jun 17 '22 at 20:45
  • Unfortunately yes, it's a part of debugging some legacy application, and whole transaction that app is doing together with this delete needs to be terminated – sh4rkyy Jun 17 '22 at 20:58
  • 1
    The best way to monitor database events is the server log. `Raise exception` is the right solution. The trace will remain in the server log file, including the time and text of the query (with default log parameters). Note also, that the tricky solution in the cited post makes no sense as trapping the exception hides it, so the result is as if it wasn't there at all. – klin Jun 18 '22 at 00:32

0 Answers0