Cascade delete triggers change user credentials

[Antonios Christofides]

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_us er trigger, the triggered function selects
"current_us er", 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_us er();

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_us er() 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_us er();

DELETE FROM a WHERE id=1;

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Stephan Szabo]

On Mon, 16 Feb 2004, Antonios Christofides wrote:

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_us er trigger, the triggered function selects
"current_us er", 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.

The triggered actions occur as if done by the owner of the fktable so that
they will not fail if the current user does not actually have delete
access on that table. I'm not sure which result for current_user makes
more sense in that context for further triggered actions.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Tom Lane]

Stephan Szabo <ss****@megazon e.bigpanda.com> writes:

On Mon, 16 Feb 2004, Antonios Christofides wrote:

... this triggers a delete from "b", which in turn activates the
show_current_us er trigger, the triggered function selects
"current_us er", and the result is the user who created "b", not the
currently connected user.

The triggered actions occur as if done by the owner of the fktable so that
they will not fail if the current user does not actually have delete
access on that table. I'm not sure which result for current_user makes
more sense in that context for further triggered actions.

I don't think it's a bug. I would suggest that Antonios probably really
wants to be using SESSION_USER, not CURRENT_USER.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Antonios Christofides]

Stephan Szabo wrote:

The triggered actions occur as if done by the owner of the fktable so that
they will not fail if the current user does not actually have delete
access on that table. I'm not sure which result for current_user makes
more sense in that context for further triggered actions.
and Tom Lane added: I would suggest that Antonios probably really wants to be using
SESSION_USER, not CURRENT_USER.

Thank you very much, this explains it all. session_user works as I
want it to. However, the manual is not very clear on this, and I'm a bit
worried about future changes in the semantics of "session_us er".

In PostgreSQL there are actually up to THREE users active, not two:
- The user who connected, which I shall call "connected user".
- The user who became effective as the result of "alter session
authorization" command. This is the user returned by session_user.
- The user who is applicable for permission checking, current_user.

If you try to "alter session authorization", PostgreSQL uses the
"connected user" to determine whether you have permission to do so (or,
at least, remembers that you initially connected as superuser). The
current user is used in most other cases of permission checking.

The 7.4 manual, however, says that the session_user "is the user that
initiated a database connection", and fails to mention "alter session
authorization". Is the manual in error or the implementation? Because my
triggers need to know which user became effective after "alter session
authorization". This is "session_us er" in 7.2.1, is it still so in 7.4?
Will it still be so in the future?

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Antonios Christofides <an*****@itia.n tua.gr> writes:

In PostgreSQL there are actually up to THREE users active, not two:
- The user who connected, which I shall call "connected user".
- The user who became effective as the result of "alter session
authorization" command. This is the user returned by session_user.
- The user who is applicable for permission checking, current_user. If you try to "alter session authorization", PostgreSQL uses the
"connected user" to determine whether you have permission to do so (or,
at least, remembers that you initially connected as superuser). The
current user is used in most other cases of permission checking.
[ looks at code... ] It does remember the original userid (which is
called AuthenticatedUs er in the code), but AFAICT the only thing that
is actually used is knowledge of whether that userid is a superuser.
The 7.4 manual, however, says that the session_user "is the user that
initiated a database connection", and fails to mention "alter session
authorization". Is the manual in error or the implementation?

The manual could stand improvement, evidently. I think this stuff is
correctly described in the vicinity of SET SESSION AUTHORIZATION, but
the status-function documentation sounds like it needs work. Feel free
to send in a docs patch ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Antonios Christofides]

Tom Lane wrote:

Feel free to send in a docs patch ...

I'll do that. I took a look at the PgSQL developer pages to see how it's
co-ordinated and the FAQ tells me to read HACKERS for six months :-) I
don't want to do that. So do I just download the doc devel version and
work on it? I'm worried someone else might be doing the same thing,
resulting in unnecessary work.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Martijn van Oosterhout]

On Fri, Feb 20, 2004 at 09:56:03PM +0200, Antonios Christofides wrote:

Tom Lane wrote:

Feel free to send in a docs patch ...
I'll do that. I took a look at the PgSQL developer pages to see how it's
co-ordinated and the FAQ tells me to read HACKERS for six months :-) I
don't want to do that. So do I just download the doc devel version and
work on it? I'm worried someone else might be doing the same thing,
resulting in unnecessary work.

The docs have been wrong this long and no-one's fixed it. The chances
someone will do it right when you're doing it is very small.

For documentation patches, just send them in. It's not like programming
where you need to understand the whole system before you can make a change.
--
Martijn van Oosterhout <kl*****@svana. org> http://svana.org/kleptog/ If the Catholic church can survive the printing press, science fiction
will certainly weather the advent of bookwarez.
http://craphound.com/ebooksneitherenorbooks.txt - Cory Doctorow

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFANo9oY5T wig3Ge+YRAmRWAJ 49pG4RqT5r4Yj41 1nw3XQpWQGckwCg gh8c
g4mKgNJuroEZw4u mJOabB/A=
=UVVp
-----END PGP SIGNATURE-----