Re: New patch for Column-level privileges

On Wed, Jan 7, 2009 at 1:46 AM, KaiGai Kohei <kaigai@ak.jp.nec.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 privilegesSchema | Name | Type  |     Access privileges     | Column
Accessprivileges 
--------+------+-------+---------------------------+--------------------------public | t1   | table |
postgres=arwdDxt/postgres| col1                                                  :   postgres=arw/postgres
                                   :   rol1=r/postgres                                                  : col2
                                       :   postgres=arw/postgres                                                  :
rol1=r/postgrespublic| 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: 0002col1 | 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?



--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157