Re: Solution for RI permission problem

On Wed, 4 Oct 2000, Jan Wieck wrote:

> Stephan Szabo wrote:
> >
> > On Sun, 1 Oct 2000, Peter Eisentraut wrote:
> >
> > > Stephan Szabo writes:
> > >
> > > > With that, I do have a general question though. Are referential actions
> > > > supposed to be limited by the permissions of the user executing the query?
> > > > So, if you for example have write access on the pk table, but not to the
> > > > fk table, and there is a on cascade delete relationship, should that user
> > > > not be able to delete from the pk table?
> > >
> > > Then you could delete records that are not in relation to the foreign keys
> > > in your table.  So I suppose not.  Of course there does seem to be a very
> > > limited range of usefulness of such a setup, but we shouldn't extrapolate
> > > something potentially more useful from that.
> >
> > Actually, I'm mostly confused about what the spec wants done.  The section
> > on the referential actions says things like "the rows are marked for
> > deletion" without and I can't find something there that says whether or
> > not you are actually supposed to pay attention to the associated privs.
> 
>     I think the user deleting (or updating) the PK table must not
>     have DELETE or UPDATE permissions on the  FK  table.  Another
>     user,  who  had  ALTER permission for the FK table implicitly
>     granted that right due to the CASCADE definition.
>
>     The point is IMHO, that the user with  the  ALTER  permission
>     for  the  FK  table  must have REFERENCE permission to the PK
>     table at the time he sets up the  constraint.  Otherwise,  he
>     could  insert  references  to all PK items without specifying
>     CASCADE and thus, deny operations on the PK table.

Actually, right now it may be denying non-owners the right to make
constraint at all.  You have to be a super user or owner of each 
side.  I just noticed this yesterday on my CVS copy that it wouldn't
let me log in as a different user and create a table that references
another table my other user created.  I haven't looked, but my guess
from the notices is that it won't let the other user place triggers
on the PK table.

I assume that you're voting on the side of if you set up a cascade you're
implicitly giving permission to modify the table through the cascade
relationship.  I figure I can make it do either thing easily, it's like
four lines of code in each of the action triggers to do the change
ownership now, so I want to get an idea of what people think is the right
behavior.