Re: ExecutorCheckPerms() hook

(2010/05/26 12:17), Tom Lane wrote:
> Stephen Frost<sfrost(at)snowman(dot)net> writes:
>> That may be the case. I'm certainly more concerned with a bug in the
>> existing code than any new code that we're working on. The question is-
>> is this actually a user-visible bug? Or do we require that a user
>> creating an FK needs SELECT rights on the primary table? If so, it's
>> still a bug, but at that point it's a bug in our documentation where we
>> don't mention that SELECT rights are also needed.
>
> Having an FK to another table certainly allows at least an indirect
> form of SELECT, because you can determine whether any given key
> exists in the PK table by seeing if you're allowed to insert a
> referencing row. 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.
>
> 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.

Hmm. If both REFERENCES and SELECT privilege are required to create
a new FK constraint, why RI_Initial_Check() need to check SELECT
permission prior to SPI_execute()?

It eventually checks SELECT privilege during execution of the secondary
query. It is unclear for me why we need to provide a slower fallback.

Thanks,
--
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>