Re: Wrong security context for deferred triggers? - Mailing list pgsql-hackers

From Laurenz Albe
Subject Re: Wrong security context for deferred triggers?
Date
Msg-id 1e5a6d9e34b38705f3ac555171fdc15af3e2c7eb.camel@cybertec.at
Whole thread Raw
In response to Wrong security context for deferred triggers?  (Laurenz Albe <laurenz.albe@cybertec.at>)
Responses Re: Wrong security context for deferred triggers?
List pgsql-hackers
On Mon, 2023-11-06 at 14:23 +0100, Laurenz Albe wrote:
>  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();
>
>  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.

I just realized one problem with running a deferred constraint trigger as
the triggering role: that role might have been dropped by the time the trigger
executes.  But then we could still error out.

Yours,
Laurenz Albe



pgsql-hackers by date:

Previous
From: Mark Hills
Date:
Subject: Re: Regression on pg_restore to 16.0: DOMAIN not available to SQL function
Next
From: Tom Lane
Date:
Subject: Re: Regression on pg_restore to 16.0: DOMAIN not available to SQL function