Wrong security context for deferred triggers?

Laurenz Albe <laurenz.albe@cybertec.at>

From: Laurenz Albe <laurenz.albe@cybertec.at>

To: pgsql-hackers@lists.postgresql.org

Date: 2023-11-06T13:23:04Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Doc: improve description of which role runs a trigger.
- c37be39a74b2 18.0 landed
Change role names used in trigger test.
- 4b05ebf0957b 18.0 landed
Ensure that AFTER triggers run as the instigating user.
- 01463e1cccd3 18.0 landed
Reverse the search order in afterTriggerAddEvent().
- 7921927bbb9d 18.0 landed

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