Re: SPI_execp() failed in RI_FKey_cascade_del()

On Thu, 11 Apr 2002, Papp, Gyozo wrote:

> Hello,
> ----- Original Message -----
> From: "Stephan Szabo" <sszabo(at)megazone23(dot)bigpanda(dot)com>
> To: "Papp, Gyozo" <pgerzson(at)freestart(dot)hu>
> Cc: <pgsql-general(at)postgresql(dot)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

Well, the code should be using the table owner which means you
shouldn't need to grant delete permission to the user that deletes from
request, however anyone who can delete from request can delete can
delete its associated rows. Someone who can both insert and delete from
request can delete any row.

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

It looks like admin has arwd on 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}
> request | {=,gerzson=arwdRxt,target=r,admin=arwd,scheduler=rw}

> 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).

I'm not sure I understand, but for cases wher you want
one of the actions or you want the action to fail if a row is referenced
the foreign key is the right thing usually. If you only want an
insert check, foreign keys won't do that because they must always be
satisified which means something has to give when you modify keys or
delete a referenced row.