Re: Re: [SQL] Foreign keys breaks tables permissions - Mailing list pgsql-hackers

Hannu Krosing wrote:
> 
> Hiroshi Inoue wrote:
> >
> > Tom Lane wrote:
> >
> > > "Stephan Szabo" <sszabo@kick.com> writes:
> > > > I believe the reason that the trigger does a select for update was
> > > > because otherwise there could exist a case that we select and see it
> > > > and then have the row go away afterwards because nothing stops the
> > > > delete.
> > >
> > > Probably the denial-of-service argument is the weakest of the three
> > > points.  Is anyone in favor of reducing SELECT FOR UPDATE to only
> > > requiring "SELECT" rights, and living with the possible lock-that-
> > > you-shouldn't-really-have-been-able-to-get issue?
> > >
> >
> > But what about DELETE CASCADE cases for exmaple ?
> > Maybe RI_trigger should be able to update/insert/delete
> > the referenced table.
> > However another kind of permission for foreign key
> > seems to be needed. i.e only granted users could
> > define foreign key of the referenced table in CREATE
> > (ALTER) TABLE command.
> 
> IIRC this is even in the SQL standard as a separate right (maybe REFERENCES ?)

Here's from SQL92 draft:
We should at least consider it when designing our GRANT system

.........
        4.26  Privileges
        A privilege authorizes a given category of <action> to be per-        formed on a specified base table, view,
column,domain,
 
character        set, collation, or translation by a specified <authorization
iden-        tifier>. The mapping of <authorization identifier>s to
operating        system users is implementation-dependent. The <action>s that
can be        specified are:
        -  INSERT
        -  INSERT (<column name list>)
        -  UPDATE
        -  UPDATE (<column name list>)
        -  DELETE
        -  SELECT
        -  REFERENCES
        -  REFERENCES (<column name list>)
        -  USAGE
.......

        A privilege descriptor with an action of INSERT, UPDATE,
DELETE,        SELECT, or REFERENCES is called a table privilege descriptor
and        identifies the existence of a privilege on the table identified
by        the privilege descriptor.
        A privilege descriptor with an action of SELECT (<column name        list>), INSERT (<column name list>),
UPDATE(<column name
 
list>),        or REFERENCES (<column name list>) is called a column privilege
de-        scriptor and identifies the existence of a privilege on the
column        in the table identified by the privilege descriptor.
        Note: In this International Standard, a SELECT column privilege        cannot be explicitly granted or revoked.
However,for the sake        of compatibility with planned future language extensions,
 
SELECT        column privilege descriptors will appear in the Information
Schema.
        A table privilege descriptor specifies that the privilege iden-        tified by the action (unless the action
isDELETE) is to be au-        tomatically granted by the grantor to the grantee on all
 
columns        subsequently added to the table.
        A privilege descriptor with an action of USAGE is called a
usage        privilege descriptor and identifies the existence of a
privilege on        the domain, character set, collation, or translation identified
by        the privilege descriptor.
        A grantable privilege is a privilege associated with a schema
that        may be granted by a <grant statement>.
        The phrase applicable privileges refers to the privileges
defined        by the privilege descriptors that define privileges granted to
the        current <authorization identifier>.
        The set of applicable privileges for the current <authorization        identifier> consists of the privileges
definedby the privilege        descriptors associated with that <authorization identifier> and        the privileges
definedby the privilege descriptors associated
 
with        PUBLIC.
        Privilege descriptors that represent privileges for the owner
of        an object have a special grantor value, "_SYSTEM". This value
is        reflected in the Information Schema for all privileges that
apply        to the owner of the object.


........
        11.36  <grant statement>
        Function
        Define privileges.
        Format
        <grant statement> ::=             GRANT <privileges> ON <object name>                 TO <grantee> [ { <comma>
<grantee>}... ]                 [ WITH GRANT OPTION ]
 
        <object name> ::=               [ TABLE ] <table name>             | DOMAIN <domain name>             |
COLLATION<collation name>             | CHARACTER SET <character set name>             | TRANSLATION <translation
name>


-----------
Hannu