Thread: Cascade delete triggers change user credentials
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 */
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.
Stephan Szabo <sszabo@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
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?
Antonios Christofides <anthony@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
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.
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 <kleptog@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