Re: New patch for Column-level privileges

>>> Currently,
>>> column-level privileges are not honored when JOINs are involved (you
>>> must have the necessary table-level privileges, as you do today). It
>>> would really be great to have that working and mainly involves
>>> modifying the rewriter to add on to the appropriate range table column
>>> list entries the columns which are used in the joins and output from
>>> joins.
>>
>> Understood. Do you plan to work on that? Or do you think the patch
>> should go into 8.4 even without support for JOINs?
>
> I tried to check the latest Column-level privileges patch.
>
> This trouble related to JOINs is come from inappropriate handling
> of rte->cols_sel list, in my opinion.
> The list is constructed at scanRTEForColumn() and expandRelation().
> However, scanRTEForColumn() appends an appeared attribute number
> even if given RangeTblEntry is RTE_JOIN, and expandRelation() appends
> all the attribute number contained within the given relation.
>
> I think your design is basically fine, so the issue is minor one.
> But it is necessary to pick up carefully what columns are really
> accessed.
>
> Here is one proposition.
> Is it possible to implement a walker function to pick up appeared
> columns and to chain them on rte->cols_sel/cols_mod?
> In this idea, columns in Query->targetList should be chained on
> rte->cols_mod, and others should be chained on rte->cols_sel.
>
> Here is an example to pick up all appeared relation:
> http://code.google.com/p/sepgsql/source/browse/trunk/sepgsql/src/backend/security/rowacl/rowacl.c#30
>
>
> If you don't have enough availability, I'll be able to do it within
> a few days.

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.

This patch can be applied on the latest CVS HEAD with Stephen's
colprivs_wip.2009010201.diff.gz.

Any comment?

I strongly want the Column-level privileges to be get merged
as soon as possible, so I don't spare any possible assist
for his works.

Thanks,

---- example of the patched Column-level privileges ----
postgres=# CREATE TABLE t1 (a int, b text, c bool);
CREATE TABLE
postgres=# CREATE TABLE t2 (x int, y text, z bool);
CREATE TABLE
postgres=# GRANT SELECT(a,b) ON t1 TO ymj;
GRANT
postgres=# GRANT UPDATE(a,b) ON t1 TO ymj;
GRANT
postgres=# GRANT SELECT(x,y) ON t2 TO ymj;
GRANT
postgres=# INSERT INTO t1 VALUES (1, 'aaa', true), (2, 'bbb', null), (3, 'ccc', false);
INSERT 0 3
postgres=# INSERT INTO t2 VALUES (1, 'xxx', false), (2, 'yyy', null), (3, 'zzz', true);
INSERT 0 3
postgres=# \c - ymj
psql (8.4devel)
You are now connected to database "postgres" as user "ymj".
postgres=> SELECT * FROM t1;
NOTICE: pg_attribute_aclmask: t1.a required: 0002 allowed: 0002
NOTICE: pg_attribute_aclmask: t1.b required: 0002 allowed: 0002
NOTICE: pg_attribute_aclmask: t1.c required: 0002 allowed: 0000
ERROR: permission denied for relation t1
postgres=> SELECT a,b FROM t1;
NOTICE: pg_attribute_aclmask: t1.a required: 0002 allowed: 0002
NOTICE: pg_attribute_aclmask: t1.b required: 0002 allowed: 0002
a | b
---+-----
1 | aaa
2 | bbb
3 | ccc
(3 rows)

postgres=> SELECT x,y FROM t2;
NOTICE: pg_attribute_aclmask: t2.x required: 0002 allowed: 0002
NOTICE: pg_attribute_aclmask: t2.y required: 0002 allowed: 0002
x | y
---+-----
1 | xxx
2 | yyy
3 | zzz
(3 rows)

postgres=> SELECT b,y FROM t1 JOIN t2 ON a = x;
NOTICE: pg_attribute_aclmask: t1.b required: 0002 allowed: 0002
NOTICE: pg_attribute_aclmask: t1.a required: 0002 allowed: 0002
NOTICE: pg_attribute_aclmask: t2.y required: 0002 allowed: 0002
NOTICE: pg_attribute_aclmask: t2.x required: 0002 allowed: 0002
b | y
-----+-----
aaa | xxx
bbb | yyy
ccc | zzz
(3 rows)

postgres=> SELECT b,z FROM t1 JOIN t2 ON a = x;
NOTICE: pg_attribute_aclmask: t1.b required: 0002 allowed: 0002
NOTICE: pg_attribute_aclmask: t1.a required: 0002 allowed: 0002
NOTICE: pg_attribute_aclmask: t2.z required: 0002 allowed: 0000
ERROR: permission denied for relation t2
postgres=> UPDATE t1 SET a = 1;
NOTICE: pg_attribute_aclmask: t1.a required: 0004 allowed: 0004
UPDATE 3
postgres=> UPDATE t1 SET c = true;
NOTICE: pg_attribute_aclmask: t1.c required: 0004 allowed: 0000
ERROR: permission denied for relation t1
postgres=>

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