Postgres Permissions violated by before-insert trigger function

Question

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"

Answer 1

The column privileges on the table only guarantee that the user is denied the right to insert "CreatedById" explicitly. Privileges are checked before the statement is executed: they only restrict which statements you are allowed to run, but they don't restrict what happens during query execution or what values are stored in the table.

To execute control over the data that get inserted, you use AFTER triggers or row-level security.

So everything is working as it should: the user cannot explicitly set "CreatedById", and the policy guarantees that the data inserted comply with the policy.