Re: SPI_execp() failed in RI_FKey_cascade_del() - Mailing list pgsql-general

Hello,
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone23.bigpanda.com>
To: "Papp, Gyozo" <pgerzson@freestart.hu>
Cc: <pgsql-general@postgresql.org>
Sent: Wednesday, April 10, 2002 5:41 PM
Subject: Re: [GENERAL] SPI_execp() failed in RI_FKey_cascade_del()


| On Tue, 9 Apr 2002, Papp, Gyozo wrote:
|
| > | I think I see something that might be doing it...
| > | There's a rule on answer_summary that forces deletes to be
| > | negated which means the trigger sees that the delete wasn't
| > | executed which would give the error since the constraint
| > | would no longer be satisfied.
| >
| > So, if I drop these rules and I try to protect my database
| > with a right acl system (grant + revoke), it should work.
|
| Well, it's not going to not delete those rows whatever you
| do pretty much.  It uses the table owner as the deleter
| of the rows so revoking write from answer_summary won't
| probably work (if it does, that's a bug).

So, I must grant delete privilege on each table referencing to
"request" to that user who deletes the rows from "request".
It implies that user can deletes any kind of rows from those
tables at any time. As you guessed below, it is not my original
intention.

BTW, I suspect there might be a bug around here.
I have an "admin" user granted read privilege on "answer" only
and he is able to delete a speficied row from "request".
But, do not trust in it too much! I changed the privileges really
often, so i have to see what the current settings are:

                         Access privileges for database "CAF"
             Table             |                  Access privileges
-------------------------------+------------------------------------------------------
 access                        | {=,gerzson=arwdRxt,target=arw,scheduler=r}
 answer                        | {=,gerzson=arwdRxt,target=ar,admin=r,scheduler=r}
 answer_summary                | {=,gerzson=arwdRxt,admin=r,scheduler=a}
 asked                         | {=,gerzson=arwdRxt,target=rd,scheduler=arwd}
 category                      | {=,gerzson=arwdRxt,admin=r}
 class                         | {=,gerzson=arwdRxt,admin=r}
 classing                      | {=,gerzson=arwdRxt,admin=arwd}
 criterium                     | {=,gerzson=arwdRxt,target=r,admin=arwd}
 notification                  | {=,gerzson=arwdRxt,admin=ard,scheduler=r}
 official                      | {=,gerzson=arwdRxt,admin=ard,scheduler=r}
 organization                  | {=,gerzson=arwdRxt,admin=arw,scheduler=r}
 question                      | {=,gerzson=arwdRxt,target=r,admin=ar}
 questionnare                  | {=,gerzson=arwdRxt,admin=arw}
 questionnare_criterium        | {=,gerzson=arwdRxt,target=r,admin=arwd}
 questionnare_question         | {=,gerzson=arwdRxt,target=r,admin=arwd,scheduler=r}
 request                       | {=,gerzson=arwdRxt,target=r,admin=arwd,scheduler=rw}
 s_category_id                 | {=,gerzson=arwdRxt,admin=rwd}
 s_class_id                    | {=,gerzson=arwdRxt,admin=rwd}
 s_criterium_id                | {=,gerzson=arwdRxt,admin=rwd}
 s_official_id                 | {=,gerzson=arwdRxt,admin=rwd}
 s_organization_id             | {=,gerzson=arwdRxt,admin=rwd}
 s_question_no                 | {=,gerzson=arwdRxt,admin=rwd}
 s_questionnare_id             | {=,gerzson=arwdRxt,admin=rwd}
 s_request_id                  | {=,gerzson=arwdRxt,admin=rwd}
 scheme_position               | {=,gerzson=arwdRxt,admin=r}
 task                          | {=,gerzson=arwdRxt,scheduler=arw}
 v_answer_summary_normalized   | {=,gerzson=arwdRxt,admin=r}
 v_asked_login                 | {=,gerzson=arwdRxt,target=r,admin=r}
 v_questionnare_criterium_list | {=,gerzson=arwdRxt,target=r,admin=r}
 v_questionnare_list           | {=,gerzson=arwdRxt,admin=r}
 v_questionnare_question_list  | {=,gerzson=arwdRxt,target=r,admin=r}
 v_request_list                | {=,gerzson=arwdRxt,admin=r}
(32 rows)

Could you reproduce it? Is it a bug?
May I send a new demo database scheme?

| In any case, if it can't delete them, it's a constraint
| violation and it should error at some point (probably
| currently with the SPI_execp).
|
| I'm guessing the intent was that on insert to answer_summary
| the row needed to be there but from that point forward
| you don't care if the row gets deleted in the referenced
| table?  If so, you're probably best off writing a small
| plpgsql trigger function to do the check, foreign keys
| have a bunch of semantics you don't want.

The original goal was to protect "answer" and "answer_summary"
tables against updates and deletes as you guessed. Now, it has
been changed a bit. It must allow to delete a "request" with
all the corresponding answers, summaries, etc.
But, it would be still appreciated to protect the se tables
from any other modifications (undesired updates and deletes).

Anyway, thanks you guidance:


--
Gyozo Papp - pgerzson@freestart.hu