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.