Wrong security context for deferred triggers?

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

Responses

Browse pgsql-hackers by date

  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