473,320 Members | 2,048 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 */

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

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

Nov 22 '05 #1
6 3220
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Andrew DeFaria | last post by:
I created the following .sql file to demonstrate a problem I'm having. According to the manual: If |ON DELETE CASCADE| is specified, and a row in the parent table is deleted, then InnoDB...
3
by: SuryaPrakash Patel via SQLMonster.com | last post by:
Hello, There are three tables: OS-GroupOFCompanies (Table1) GoC_GroupOFCompaniesID (PK) OS-Organization (Table 2) Org_OrganizationID (PK)
2
by: Jack | last post by:
We are have a question about the no cascade option on before triggers. The description stays that no other triggers will be fired by the changes of a before trigger. One of our developers is...
33
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and...
3
by: Tim Marshall | last post by:
HI all, Access 2003, Jet back end. Rather than annoy my users in a particular app by having relationships with enforced relational integrity refuse to delete a record with related records, I'm...
2
by: R.Welz | last post by:
Hello. I want to discuss a problem I have with my database design becourse I feel I cannot decide wheather I am on the right way of doing things. First of all, I am writing a literature and...
0
by: Marty Alchin | last post by:
Fir of all, hello to all of you. I'm new to this list, and I apologize if this has already been asked, but the mailing list archives don't seem to be responding at the moment for me to search...
2
by: nekiv90 | last post by:
Greetings, I have to delete older policies and its related records in other tables. The deletion from the parent table will trigger the deletion of relevant records from about 30 something...
1
by: Nemisis | last post by:
Hi everyone, Wonder if anyone can shed some light on how i should setup cascade deletes of object data within my database. One option is to use SQL triggers, is this a good idea? Should the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.