Cascade delete triggers change user credentials - Mailing list pgsql-general

Hi, I've prepared a test case about this, which I include below. I
have tables "a" and "b"; "b" has a foreign key to "a", on delete
cascade. In addition, there is a "before delete on b" trigger, which
all that does is show the current_user. If a row is deleted from "a",
and this triggers a delete from "b", which in turn activates the
show_current_user trigger, the triggered function selects
"current_user", and the result is the user who created "b", not the
currently connected user.

Is this a bug? Is there any workaround? I'm running Debian 3.0 with
its prepackaged PostgreSQL 7.2.1. I greatly appreciate your help.


---------------------------------------------------------------------

Here's the test script:

DROP TABLE a;
DROP TABLE b;
DROP FUNCTION show_current_user();

CREATE TABLE a (id INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE b (id INTEGER NOT NULL,
                CONSTRAINT fd_b_id FOREIGN KEY (id) REFERENCES a(id)
                    ON DELETE CASCADE);
GRANT ALL ON a TO PUBLIC;
GRANT ALL ON b TO PUBLIC;
INSERT INTO a(id) VALUES (1);
INSERT INTO b(id) VALUES (1);

CREATE FUNCTION show_current_user() RETURNS OPAQUE AS '
DECLARE
  curuser VARCHAR(25);
BEGIN
  SELECT INTO curuser current_user;
  RAISE EXCEPTION ''Current user is %'', curuser;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER delb
    BEFORE DELETE ON b
    FOR EACH ROW EXECUTE PROCEDURE show_current_user();

DELETE FROM a WHERE id=1;

/* Now retry the last delete as a different user */