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

The following bug has been logged on the website:

Bug reference:      8512
Logged by:          Kurt Roeckx
Email address:      kurt@roeckx.be
PostgreSQL version: 9.0.6
Operating system:   Linux
Description:

Hi,


When I read the documentation for GRANT, I see:
SELECT


    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.


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?




Kurt

* kurt@roeckx.be (kurt@roeckx.be) 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.
>=20
>=20
> I read that as "SELECT field1 from table where field2 =3D 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.

    Thanks,

        Stephen

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.