Skip site navigation (1) Skip section navigation (2)

Re: New patch for Column-level privileges

From: KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>
To: Markus Wanner <markus(at)bluegap(dot)ch>, Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Alex Hunsaker <badalex(at)gmail(dot)com>, PostgreSQL-development Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: New patch for Column-level privileges
Date: 2009-01-07 06:46:03
Message-ID: 49644FAB.7060602@ak.jp.nec.com (view raw or flat)
Thread:
Lists: pgsql-hackers
>>> 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>

Attachment: colprivs_fix_joins.20090107.diff
Description: text/x-diff (5.4 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Greg SmithDate: 2009-01-07 06:46:09
Subject: Re: version() output vs. 32/64 bits
Previous:From: Fujii MasaoDate: 2009-01-07 06:42:40
Subject: Re: Multiplexing SUGUSR1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group