Tom,
* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> I haven't dug in the SQL spec to see if that addresses
> the point, but it wouldn't bother me in the least to insist that
> both REFERENCES and SELECT privilege are required to create an FK.
Ok. If we require REFERENCES and SELECT privs to create an FK then I
think the question is: when is the path in RI_Initial_Check not able
to be used (hence, why do we need a fall-back)? My guess would be:
role X has:
Primary table: SELECT, REFERENCES
Foreign table: REFERENCES
This doesn't make much sense either though, because X has to own the
foreign table.
postgres=> alter table fk_tbl add foreign key (x) references pk_tbl;
ERROR: must be owner of relation fk_tbl
So, the only situation, it seems, where the fall-back method has to be
used is when X owns the table but doesn't have SELECT rights on it.
Maybe it's just me, but that seems pretty silly.
If we require:
Primary table: SELECT, REFERENCES
Foreign table: OWNER, SELECT, REFERENCES
Then it seems like we should be able to eliminate the fall-back method
and just use the RI_Initial_Check approach. What am I missing here? :/
> In any case, RI_Initial_Check isn't broken, because if it can't do
> the SELECTs it just falls back to a slower method. It's arguable
> that the FK triggers themselves are assuming more than they should
> about permissions, but I don't think that RI_Initial_Check can be
> claimed to be buggy.
RI_Initial_Check is at least missing an optimization to support
column-level priviledges. If we say that REFERENCES alone is allowed to
create a FK, then the fall-back method is broken because it depends on
SELECT rights on the primary.
To be honest, I'm guessing that the reason there's so much confusion
around this is that the spec probably says you don't need SELECT rights
(I havn't checked though), and at some point in the distant past we
handled that correctly with the fall-back method and that has since been
broken by other changes (possibly to plug the hole the fall-back method
was using).
I'll try to go deipher the spec so we can at least have something more
interesting to discuss (if we agree with doing it how the spec says or
not :).
Thanks,
Stephen
