By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,609 Members | 1,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,609 IT Pros & Developers. It's quick & easy.

Cascade delete triggers change user credentials

P: n/a
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 */

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

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

Nov 22 '05 #1
Share this Question
Share on Google+
6 Replies

P: n/a
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_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.


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 YourEmailAddressHere" to ma*******@postgresql.org)

Nov 22 '05 #2

P: n/a
Stephan Szabo <ss****@megazone.bigpanda.com> writes:
On Mon, 16 Feb 2004, Antonios Christofides wrote:
... 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.
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*******@postgresql.org

Nov 22 '05 #3

P: n/a
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_user".

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_user" 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

Nov 22 '05 #4

P: n/a
Antonios Christofides <an*****@itia.ntua.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 AuthenticatedUser 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

Nov 22 '05 #5

P: n/a
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

Nov 22 '05 #6

P: n/a
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

iD8DBQFANo9oY5Twig3Ge+YRAmRWAJ49pG4RqT5r4Yj411nw3X QpWQGckwCggh8c
g4mKgNJuroEZw4umJOabB/A=
=UVVp
-----END PGP SIGNATURE-----

Nov 22 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.