Re: New patch for Column-level privileges

Jaime Casanova wrote:
> On Wed, Jan 7, 2009 at 1:46 AM, KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com> wrote:
>> The attached patch is a proof of the concept.
>> It walks on a given query tree to append accessed columns on
>> rte->cols_sel and rte->cols_mod.
>> When aliasvar of JOIN'ed relation is accesses, its source is
>> appended on the list.
>>
>
> for my test i created to tables:
>
> CREATE TABLE t1 (col1 int primary key, col2 int);
> CREATE TABLE t2 (col1 int references t1);
>
> and a role:
>
> CREATE ROLE rol1;
>
> then i granted all cols in the table to the role:
>
> GRANT SELECT (col1) ON t1 TO rol1;
> GRANT SELECT (col2) ON t1 TO rol1;
> GRANT SELECT (col1) ON t2 TO rol1;
>
> prueba=> \dp
> Access privileges
> Schema | Name | Type | Access privileges | Column Access privileges
> --------+------+-------+---------------------------+--------------------------
> public | t1 | table | postgres=arwdDxt/postgres | col1
> : postgres=arw/postgres
> : rol1=r/postgres
> : col2
> : postgres=arw/postgres
> : rol1=r/postgres
> public | t2 | table | postgres=arwdDxt/postgres | col1
> : postgres=arw/postgres
> : rol1=r/postgres
> (2 rows)
>
>
> then i execute:
>
> prueba=> select t1.* from t1, t2 where t1.col1 = t2.col1;
> NOTICE: pg_attribute_aclmask: t1.col1 required: 0002 allowed: 0002
> NOTICE: pg_attribute_aclmask: t1.col2 required: 0002 allowed: 0002
> NOTICE: pg_attribute_aclmask: t1.col1 required: 0002 allowed: 0002
> NOTICE: pg_attribute_aclmask: t2.col1 required: 0002 allowed: 0002
> col1 | col2
> ------+------
> (0 rows)
>
> good, but if i doesn't include filter conditions:
>
> prueba=> select t1.* from t1, t2;
> NOTICE: pg_attribute_aclmask: t1.col1 required: 0002 allowed: 0002
> NOTICE: pg_attribute_aclmask: t1.col2 required: 0002 allowed: 0002
> ERROR: permission denied for relation t2
>
> is this intended?

Basically, I want to wait for Stephen's opinion.

However, it seem's to me it is a correct behavior.

ExecCheckRTEPerms() checks user's privileges on columns, when he does
not have required privileges on the table. When he has proper privileges
on all the appeared columns within the table, it is allowed.
But, when no columns are used on the table, it applies result of checks
on the table.

In this example, "rol1" does not have any privileges on relation "t1"
and "t2", but he can select "t1.col1", "t1.col2" and "t2.col1".
Since he does not have any privs on relations, column's privs are
checked in both of queries.

In the first query, he uses "col1" and "col2" for "t1" and "col1" for
"t2", and all of them are allowed to select. So, he got succeeded.
In the other query, he uses "col1" and "col2" for "t1" but no columns
for "t2", so the result of checks on relation "t2" is applied.

Stephen, could you indicate us what behavior is proper in this case?

Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>