Re: New patch for Column-level privileges - Mailing list 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@ak.jp.nec.com>
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index 4008a23..54c4ffa 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -322,6 +322,13 @@ transformJoinUsingClause(ParseState *pstate, List *leftVars, List *rightVars)
         Node       *rvar = (Node *) lfirst(rvars);
         A_Expr       *e;

+        /*
+         * mark ACL_SELECT for columns appeared in NATURAL JOIN
+         * JOIN USING(...).
+         */
+        markColumnForSelectPriv(lvar, pstate);
+        markColumnForSelectPriv(rvar, pstate);
+
         e = makeSimpleA_Expr(AEXPR_OP, "=",
                              copyObject(lvar), copyObject(rvar),
                              -1);