Re: BUG #8512: Can't use columns I can't read in the where clause of a select

Stephen Frost wrote
> *

> kurt@

> (

> kurt@

> ) wrote:
>> Allows SELECT from any column, or the specific columns listed, of the
>> specified table, view, or sequence. Also allows the use of COPY TO. This
>> privilege is also needed to reference existing column values in UPDATE or
>> DELETE.
>>
>>
>> I read that as "SELECT field1 from table where field2 = 1" should work if
>> I
>> have grant select(field1), but not on field2. I'm getting a "permission
>> denied". If I remove the where clause it of course works.
>
> You have to have SELECT rights on a column to be able to use it in a
> conditional (eg: with WHERE).
>
>> I'm not sure if the behaviour is expected or not. Maybe I'm reading the
>> documentation wrong, or maybe the documentation is just wrong. Could
>> someone please clarify?
>
> It's expected. The documentation could perhaps be improved, but the
> second sentence ("This privilege is also needed..") is intended to cover
> the case where the column is being referred to *anywhere* in the query,
> basically, and that applies to SELECT as much as UPDATE or DELETE.

"SELECT": read the current value
"UPDATE": cause the current value to be changed (does not require knowing
the existing value)
"DELETE": cause the current value (indirectly via row removal) to be removed
(does not require knowing the existing value)

A where clause requires that the user can know the current value in the
field

Within a SELECT statement there is no permissions distinction between the
different sub-clauses (e.g., ORDER BY, GROUP BY, WHERE, select-list) since
in any of these cases it is the current value of a column that is needed.
So "SELECT" is read as being a top-level (as are UPDATE and DELETE) - and as
Stephen said because WHERE can be part of UPDATE and DELETE the additional
comment is made that WHERE in those contexts require "SELECT-like"
privileges if the column is used there. But not if the column only exists
as a target-column.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/BUG-8512-Can-t-use-columns-I-can-t-read-in-the-where-clause-of-a-select-tp5773752p5773757.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.