Fix for regression test privileges

Related to issue 3520503, this test is failing because remote join query scan
in XC needs to more columns than necessary, sometimes including columns to
which users might not have access.
Reducing the target list on remote query scan is not that easy, so for the time
being this regression test is updated by being aware of this issue.

2 files changed, 21 insertions, 28 deletions

diff --git a/src/test/regress/expected/privileges_1.out b/src/test/regress/expected/privileges_1.out
index b623962be7..517272d81d 100644
--- a/src/test/regress/expected/privileges_1.out
+++ b/src/test/regress/expected/privileges_1.out
@@ -274,12 +274,11 @@ SELECT 1 FROM atest5; -- ok
         1
 (1 row)
 
-SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok
- ?column? 
-----------
-        1
-(1 row)
-
+-- PGXCTODO: Related to issue 3520503, target list on a remote query scan needs to be
+-- reduced to necessary columns only. Now all the columns are fetched, including ones
+-- user has no permission to.
+SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- fail
+ERROR:  permission denied for relation atest5
 SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail
 ERROR:  permission denied for relation atest5
 SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail
@@ -296,12 +295,8 @@ SELECT atest1.*,atest5.one FROM atest1, atest5; -- ok
 ERROR:  permission denied for relation atest5
 SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.two); -- fail
 ERROR:  permission denied for relation atest5
-SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- ok
- a |  b  | one 
----+-----+-----
- 1 | two |   1
-(1 row)
-
+SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- fail due to issue 3520503, see above
+ERROR:  permission denied for relation atest5
 SELECT one, two FROM atest5; -- fail
 ERROR:  permission denied for relation atest5
 SET SESSION AUTHORIZATION regressuser1;
@@ -312,13 +307,11 @@ ERROR:  permission denied for relation atest5
 SET SESSION AUTHORIZATION regressuser1;
 GRANT SELECT (two) ON atest5 TO regressuser4;
 SET SESSION AUTHORIZATION regressuser4;
-SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now
- one | two 
------+-----
-(0 rows)
-
+SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail due to issue 3520503, see above
+ERROR:  permission denied for relation atest5
 -- test column-level privileges for INSERT and UPDATE
-INSERT INTO atest5 (two) VALUES (3); -- ok
+INSERT INTO atest5 (two) VALUES (3); -- fail due to issue 3520503, see above
+ERROR:  permission denied for relation atest5
 COPY atest5 FROM stdin; -- fail
 ERROR:  permission denied for relation atest5
 COPY atest5 (two) FROM stdin; -- ok
@@ -365,11 +358,8 @@ SELECT atest6 FROM atest6; -- ok
 --------
 (0 rows)
 
-SELECT one FROM atest5 NATURAL JOIN atest6; -- ok
- one 
------
-(0 rows)
-
+SELECT one FROM atest5 NATURAL JOIN atest6; -- fail due to issue 3520503, see above
+ERROR:  permission denied for relation atest5
 SET SESSION AUTHORIZATION regressuser1;
 ALTER TABLE atest6 DROP COLUMN two;
 REVOKE SELECT (one,blue) ON atest6 FROM regressuser4;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index a87ce77aa6..696c01e7c1 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -192,7 +192,10 @@ COPY atest5 (two) TO stdout; -- fail
 SELECT atest5 FROM atest5; -- fail
 COPY atest5 (one,two) TO stdout; -- fail
 SELECT 1 FROM atest5; -- ok
-SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok
+-- PGXCTODO: Related to issue 3520503, target list on a remote query scan needs to be
+-- reduced to necessary columns only. Now all the columns are fetched, including ones
+-- user has no permission to.
+SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- fail
 SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail
 SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail
 SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail
@@ -201,7 +204,7 @@ SELECT * FROM atest1, atest5; -- fail
 SELECT atest1.* FROM atest1, atest5; -- ok
 SELECT atest1.*,atest5.one FROM atest1, atest5; -- ok
 SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.two); -- fail
-SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- ok
+SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- fail due to issue 3520503, see above
 SELECT one, two FROM atest5; -- fail
 
 SET SESSION AUTHORIZATION regressuser1;
@@ -214,10 +217,10 @@ SET SESSION AUTHORIZATION regressuser1;
 GRANT SELECT (two) ON atest5 TO regressuser4;
 
 SET SESSION AUTHORIZATION regressuser4;
-SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now
+SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail due to issue 3520503, see above
 
 -- test column-level privileges for INSERT and UPDATE
-INSERT INTO atest5 (two) VALUES (3); -- ok
+INSERT INTO atest5 (two) VALUES (3); -- fail due to issue 3520503, see above
 COPY atest5 FROM stdin; -- fail
 COPY atest5 (two) FROM stdin; -- ok
 1
@@ -255,7 +258,7 @@ ALTER TABLE atest6 DROP COLUMN three;
 
 SET SESSION AUTHORIZATION regressuser4;
 SELECT atest6 FROM atest6; -- ok
-SELECT one FROM atest5 NATURAL JOIN atest6; -- ok
+SELECT one FROM atest5 NATURAL JOIN atest6; -- fail due to issue 3520503, see above
 
 SET SESSION AUTHORIZATION regressuser1;
 ALTER TABLE atest6 DROP COLUMN two;