I’ve got a table with row level permissions enabled. I’ve got an insert policy for my user, and I’ve granted permissions for them on specific columns. I added a new column to track the id of whoever inserts columns, and populate it with a BEFORE INSERT trigger.

Boiled down to the relevant parts, my table setup looks like this:

CREATE TABLE "MyTable" (
"MyId" SERIAL PRIMARY KEY,
"SomeData" NOT NULL TEXT,
"CreatedById" UUID NOT NULL
);

-- Trigger Function
CREATE FUNCTION "SetCreatedById_tr"() RETURNS TRIGGER AS $$
BEGIN
    IF current_user_id() IS NULL THEN
      RAISE EXCEPTION 'current_user_id() is NULL';
    END IF;

    NEW."CreatedById" = current_user_id(); -- Why does this work when myUser doesn't have an insert policy for "CreatedById"
    RETURN NEW;
END;
$$ LANGUAGE plpgsql VOLATILE SECURITY INVOKER;

-- BEFORE INSERT Trigger
CREATE TRIGGER "CreatedById_tr" 
    BEFORE INSERT ON "MyTable" 
    FOR EACH ROW EXECUTE FUNCTION "SetCreatedById_tr"();

-- ROW LEVEL SECURITY
ALTER TABLE "MyTable" ENABLE ROW LEVEL SECURITY;

-- POLICIES
CREATE POLICY "myUser_Insert_MyTable" ON "MyTable" FOR INSERT TO myUser WITH CHECK ("UserId" = current_user_id());
GRANT INSERT ("SomeData") ON TABLE "MyTable" TO myUser;
GRANT USAGE ON SEQUENCE "MyTable_MyId_seq" TO myUser;

When I added this feature, I notably forgot to add the “CreatedById” column to the GRANT permissions, but to my surprise, it worked anyways, which I find deeply concerning. Shouldn’t the insert fail because the trigger function updated a column for which the user doesn’t have the appropriate permissions? Shouldn’t the SECURITY INVOKER on the trigger function mean the resulting INSERT is still constrained by the GRANT?

As it currently is set up, if ‘myUser’ executes this:

INSERT INTO "MyTable" ("SomeData") VALUES (input.*) RETURNING * INTO newRow;

The trigger successfully inserts the CreatedById.

However in that case I’d think this would also work:

INSERT INTO "MyTable" ("SomeData" "CreatedById") VALUES (input.*) RETURNING * INTO newRow;

But this errors as we should expect with “permission denied for table MyTable”