PostgreSQL Question posted in
The official documentation can be found here.

Which role is responsible for performing `ON UPDATE SET DEFAULT` in PostgreSQL

I’m looking for a confirmation and possible explanation.

In PostgreSQL, I create a table A, referencing table B by FK with constrain option ON DELETE UPDATE SET DEFAULT. If the referenced record from table B is deleted, the physical "change" of the original FK for the default value is apparently done by the "system role" (in my case postgres)

Now, I have certain other triggers set on table A, that prevent editing when my "is_locked" column is set to True. Based on the described role switching, I expected that I can let the change happen (even with is_locker==True if current_role = 'postgres'.

However this only turned to work when the trigger is "BEFORE UPDATE" and not "AFTER UPDATE". in other words, based on my observations, the moment the records gets physically updated, the db changes the executing role back to the original role (the one that deleted the record in table B).

Is this something expected? If so, how do you think I should approach the "allow edits if role is postgres" even after the record is inserted?

A code to replicate and observe:

BEGIN;

CREATE TABLE parent (id INT PRIMARY KEY, name VARCHAR(100));

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT DEFAULT 0,
    edited_by text,
    CONSTRAINT fk_parent FOREIGN KEY (parent_id) REFERENCES parent (id) ON DELETE SET DEFAULT
);

CREATE TABLE logs ("text" text);

CREATE OR REPLACE FUNCTION react_on_child_update () RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO logs ("text") VALUES (CURRENT_role::text);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_before_react_on_child_update BEFORE
UPDATE ON child FOR EACH ROW
EXECUTE FUNCTION react_on_child_update();

CREATE TRIGGER trigger_after_react_on_child_update
AFTER UPDATE ON child FOR EACH ROW
EXECUTE FUNCTION react_on_child_update();

INSERT INTO parent (id, name) VALUES (0, 'ghost');
INSERT INTO parent (id, name) VALUES (1, 'Parent 1');
INSERT INTO parent (id, name) VALUES (2, 'Parent 2');
INSERT INTO child (id, parent_id) VALUES (1, 1);
INSERT INTO child (id, parent_id) VALUES (2, 2);


CREATE ROLE "user";
GRANT ALL ON TABLE parent,logs,child TO "user";
SET ROLE TO "user";

DELETE FROM parent
WHERE id = 1;

SELECT * FROM parent;
SELECT * FROM child;

-- See that first role was the admin one, second the caller ("user")
SELECT * FROM logs;

Thanks a lot!

Tags:

2

Answers


  1. 0

    I replicated your case study and got postgres as the credential in the update according to your commands. But that’s because I’m logged to the data base as such. Not sure I understand the purpose of this test.

    Login or Signup to reply.
  2. 0

    Which role is responsible for performing ON UPDATE SET DEFAULT in PostgreSQL

    The owner of the constraint, which by extension has to also be the owner of the table it’s on. There’s no "system role", things are just owned by regular roles and things they set up can be traced back to them.

    the physical "change" of the original FK for the default value is apparently done by the "system role" (in my case postgres)

    I’m guessing the role postgres just owns that table. If someone_else owned it, you’d see the update being run by them instead – postgres isn’t special, it just happens to be their table and their constraint. You can check it in pg_tables:

    select tableowner
from pg_tables
where tablename='child';

    turned to work when the trigger is "BEFORE UPDATE" and not "AFTER UPDATE". in other words, based on my observations, the moment the records gets physically updated, the db changes the executing role back to the original role

    It shouldn’t matter. If you check the current_user and based on that raise exception in the after trigger, this will cause the entire chain of events to be invalidated just as well as if it was thrown before, after or instead of.

    As to the visibility: even if you set the before trigger function to security invoker, the update caused by referential integrity constraint cascade action is issued as the owner of that constraint, so the function is also invoked by them. That’s what current_role shows both in the context of trigger..when() as well as inside the function that it proceeds to execute:
    demo at db-fiddle

    event op schema table current_user session_user trigger_arg0
    BEFORE UPDATE public child owner owner param of the trigger BEFORE update function call
    AFTER UPDATE public child not_owner owner param of the trigger AFTER update function call

    In order to see who caused that cascading update to run from inside the trigger it fired, you could check the session_user and use that to allow/reject that update – that remains untouched even for the update resulting from cascade.
    Problem is, the owner that operates under set role not_the_owner, would still get identified as the owner. And vice versa: someone connected as not-the-owner but switched to owner mid-session, still gets rejected unless they reconnect as the owner.

    The create constraint trigger..after update idea should work fine (here‘s user failing to remove the parent, here‘s the owner succeeding). With security invoker (default), the function will run under the current_user role and from there, you can raise to prevent the cascaded update, or run a different one if that’s what you want. Make sure to check new/old values and compare them to the default inside when() to avoid falling in a loop where this new update triggers another update and so on.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search