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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Stephen Frost <sfrost(at)snowman(dot)net>
Cc: Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>, Markus Wanner <markus(at)bluegap(dot)ch>, 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-14 03:45:59
Message-ID: 496D5FF7.8060600@ak.jp.nec.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom Lane wrote:
> BTW, another corner case that I'm not sure gets handled right is
> that the join columns in JOIN USING or NATURAL JOIN need to be marked
> as requiring ACL_SELECT.  (Or so I'd expect anyway; I didn't run through
> the SQL spec looking for chapter and verse on that.)  I forget whether
> we take any shortcuts in setting up the implied join condition
> expressions, but if we do then some extra code might be needed.  This
> would be a good place for a regression test in any case.

It indeed needs special care.

The attached patch put invocations of markColumnForSelectPriv()
at transformJoinUsingClause() to mark those columns are used.

-- Results:
postgres=# CREATE TABLE t1 (a int, b int, c int, x text);
CREATE TABLE
postgres=# CREATE TABLE t2 (a int, b int, c int, y text);
CREATE TABLE
postgres=# GRANT select(a,b,x) ON t1 TO ymj;
GRANT
postgres=# GRANT select(a,c,y) ON t2 TO ymj;
GRANT
postgres=# \c - ymj
psql (8.4devel)
You are now connected to database "postgres" as user "ymj".
postgres=> SELECT x, y FROM t1 NATURAL JOIN t2;
DEBUG:  pg_attribute_aclmask: t1.a required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t1.b required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t1.c required: 0002 allowed: 0000
ERROR:  permission denied for relation t1

postgres=> SELECT x, y FROM t1 JOIN t2 USING (a,b);
DEBUG:  pg_attribute_aclmask: t1.a required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t1.b required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t1.x required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t2.a required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t2.b required: 0002 allowed: 0000
ERROR:  permission denied for relation t2

postgres=> SELECT x, y FROM t1 JOIN t2 USING (a,c);
DEBUG:  pg_attribute_aclmask: t1.a required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t1.c required: 0002 allowed: 0000
ERROR:  permission denied for relation t1

postgres=> SELECT x, y FROM t1 JOIN t2 USING (a);
DEBUG:  pg_attribute_aclmask: t1.a required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t1.x required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t2.a required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t2.y required: 0002 allowed: 0002
 x | y
---+---
(0 rows)

postgres=> \c - kaigai
psql (8.4devel)
You are now connected to database "postgres" as user "kaigai".
postgres=# ALTER TABLE t1 DROP COLUMN c;
ALTER TABLE
postgres=# ALTER TABLE t2 DROP COLUMN b;
ALTER TABLE

postgres=# \c - ymj
psql (8.4devel)
You are now connected to database "postgres" as user "ymj".
postgres=> SELECT x, y FROM t1 NATURAL JOIN t2;
DEBUG:  pg_attribute_aclmask: t1.a required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t1.x required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t2.a required: 0002 allowed: 0002
DEBUG:  pg_attribute_aclmask: t2.y required: 0002 allowed: 0002
 x | y
---+---
(0 rows)

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

Attachment: colprivs_fix_natural_join.20090114.diff
Description: text/x-diff (633 bytes)

In response to

Responses

pgsql-hackers by date

Next:From: Stephen FrostDate: 2009-01-14 03:53:43
Subject: Re: A single escape required for log_filename
Previous:From: Bruce MomjianDate: 2009-01-14 03:41:56
Subject: Re: solaris libpq threaded build fails

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