From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Wrong security context for deferred triggers? |
Date: | 2023-11-06 13:23:04 |
Message-ID: | 77ee784cf248e842f74588418f55c2931e47bd78.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Create a table and a deferrable constraint trigger:
CREATE TABLE tab (i integer);
CREATE FUNCTION trig() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
RAISE NOTICE 'current_user = %', current_user;
RETURN NEW;
END;$$;
CREATE CONSTRAINT TRIGGER trig AFTER INSERT ON tab
DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW EXECUTE FUNCTION trig();
Create a role and allow it INSERT on the table:
CREATE ROLE duff;
GRANT INSERT ON tab TO duff;
Now become that role and try some inserts:
SET ROLE duff;
BEGIN;
INSERT INTO tab VALUES (1);
NOTICE: current_user = duff
That looks ok; the current user is "duff".
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO tab VALUES (2);
Become a superuser again and commit:
RESET ROLE;
COMMIT;
NOTICE: current_user = postgres
So a deferred constraint trigger does not run with the same security context
as an immediate trigger. This is somewhat nasty in combination with
SECURITY DEFINER functions: if that function performs an operation, and that
operation triggers a deferred trigger, that trigger will run in the wrong
security context.
This behavior looks buggy to me. What do you think?
I cannot imagine that it is a security problem, though.
Yours,
Laurenz Albe
From | Date | Subject | |
---|---|---|---|
Next Message | Jeevan Chalke | 2023-11-06 13:23:06 | Re: More new SQL/JSON item methods |
Previous Message | Matthias van de Meent | 2023-11-06 13:16:09 | Re: Optimizing "boundary cases" during backward scan B-Tree index descents |