Re: New patch for Column-level privileges

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>