Cascade delete triggers change user credentials
От | Antonios Christofides |
---|---|
Тема | Cascade delete triggers change user credentials |
Дата | |
Msg-id | 20040216201906.GA3403@localhost обсуждение исходный текст |
Ответы |
Re: Cascade delete triggers change user credentials
|
Список | 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 */
В списке pgsql-general по дате отправления: