hawq-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hubertzh...@apache.org
Subject incubator-hawq git commit: HAWQ-1396. Add more test cases for querying external table via PXF with Ranger enable.
Date Thu, 13 Apr 2017 09:39:21 GMT
Repository: incubator-hawq
Updated Branches:
  refs/heads/master 2d9bc0306 -> 72928dab2


HAWQ-1396. Add more test cases for querying external table via PXF with Ranger enable.


Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/72928dab
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/72928dab
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/72928dab

Branch: refs/heads/master
Commit: 72928dab2dfc386118ae95e4016f3b549e347de1
Parents: 2d9bc03
Author: interma <interma@outlook.com>
Authored: Wed Apr 5 13:47:40 2017 +0800
Committer: hubertzhang <hubertzhang@apache.org>
Committed: Thu Apr 13 17:38:29 2017 +0800

----------------------------------------------------------------------
 src/test/feature/.gitignore                     |   2 +
 .../feature/Ranger/ans/manual10000_success.ans  |  10 +
 .../feature/Ranger/ans/normal10000_success.ans  |  10 -
 src/test/feature/Ranger/ans/pxf1_fail.ans       |   2 +-
 src/test/feature/Ranger/ans/pxf2_fail.ans       |   9 +
 src/test/feature/Ranger/ans/pxf2_success.ans    |   8 +
 src/test/feature/Ranger/ans/pxf3_success.ans    |  10 +
 src/test/feature/Ranger/ans/pxf4_fail.ans       |   9 +
 src/test/feature/Ranger/ans/pxf4_success.ans    |   8 +
 src/test/feature/Ranger/ans/pxf5_success.ans    |  13 +
 src/test/feature/Ranger/ans/pxf6_fail.ans       |   8 +
 src/test/feature/Ranger/ans/pxf6_success.ans    |   8 +
 src/test/feature/Ranger/ans/pxf7_success.ans    |  14 +
 src/test/feature/Ranger/ans/pxf8_fail.ans       |   9 +
 src/test/feature/Ranger/ans/pxf8_success.ans    |   8 +
 src/test/feature/Ranger/ans/pxf9_success.ans    |  12 +
 .../Ranger/data/testhive_externaltable.sql      |   5 +
 src/test/feature/Ranger/policy/10000/1.json     |   1 -
 src/test/feature/Ranger/policy_helper.cpp       | 204 +++++++++++++
 src/test/feature/Ranger/policy_helper.h         | 110 +++++++
 src/test/feature/Ranger/pxfpolicy/1/3.json      |   2 +-
 src/test/feature/Ranger/rangerpolicy.py         |  56 ++--
 src/test/feature/Ranger/rangerrest.py           |   1 +
 src/test/feature/Ranger/sql/manual/10000.sql    |   2 +-
 src/test/feature/Ranger/sql/pxf/2.sql           |   4 +
 src/test/feature/Ranger/sql/pxf/3.sql           |   4 +
 src/test/feature/Ranger/sql/pxf/4.sql           |   4 +
 src/test/feature/Ranger/sql/pxf/5.sql           |   3 +
 src/test/feature/Ranger/sql/pxf/6.sql           |   4 +
 src/test/feature/Ranger/sql/pxf/7.sql           |   3 +
 src/test/feature/Ranger/sql/pxf/8.sql           |   4 +
 src/test/feature/Ranger/sql/pxf/9.sql           |   3 +
 src/test/feature/Ranger/test_policyhelper.cpp   |  92 ++++++
 src/test/feature/Ranger/test_ranger.cpp         | 301 ++++++++++++++++---
 src/test/feature/Ranger/test_ranger.h           |   7 +-
 src/test/feature/sanity_tests.txt               |   4 +-
 36 files changed, 873 insertions(+), 81 deletions(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/.gitignore
----------------------------------------------------------------------
diff --git a/src/test/feature/.gitignore b/src/test/feature/.gitignore
index fec82bd..10fc2f6 100644
--- a/src/test/feature/.gitignore
+++ b/src/test/feature/.gitignore
@@ -23,3 +23,5 @@ testlib/ans/template.ans
 testlib/sql/template.sql
 utility/ans/copytest.csv
 utility/ans/onek.data
+
+*.pyc

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/manual10000_success.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/manual10000_success.ans b/src/test/feature/Ranger/ans/manual10000_success.ans
new file mode 100644
index 0000000..73f878b
--- /dev/null
+++ b/src/test/feature/Ranger/ans/manual10000_success.ans
@@ -0,0 +1,10 @@
+-- start_ignore
+-- end_ignore
+set session role=usermanual10000;
+SET
+select count(*) from information_schema.view_table_usage;
+0
+select count(*) from hawq_toolkit.hawq_table_indexes;
+0
+select count(*) from pg_catalog.pg_compression;
+4

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/normal10000_success.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/normal10000_success.ans b/src/test/feature/Ranger/ans/normal10000_success.ans
deleted file mode 100644
index 125e649..0000000
--- a/src/test/feature/Ranger/ans/normal10000_success.ans
+++ /dev/null
@@ -1,10 +0,0 @@
--- start_ignore
--- end_ignore
-set session role=usertest10000;
-SET
-select count(*) from information_schema.view_table_usage;
-0
-select count(*) from hawq_toolkit.hawq_table_indexes;
-0
-select count(*) from pg_catalog.pg_compression;
-4

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf1_fail.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf1_fail.ans b/src/test/feature/Ranger/ans/pxf1_fail.ans
index a8a7197..fb2e460 100644
--- a/src/test/feature/Ranger/ans/pxf1_fail.ans
+++ b/src/test/feature/Ranger/ans/pxf1_fail.ans
@@ -5,6 +5,6 @@ SET
 set session role= 'userpxf1';
 SET
 select * from hcatalog.default.testhive;
-psql:/tmp/TestHawqRanger_HcatalogTest.sql:5: ERROR:  permission denied for schema default
+psql:/tmp/TestHawqRanger_PXFHcatalogTest.sql:5: ERROR:  permission denied for schema default
 LINE 1: select * from hcatalog.default.testhive;
                       ^

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf2_fail.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf2_fail.ans b/src/test/feature/Ranger/ans/pxf2_fail.ans
new file mode 100644
index 0000000..ff7c32b
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf2_fail.ans
@@ -0,0 +1,9 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf2';
+SET
+CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writabletbl_1(location text, month text, num_orders int, total_sales float8)
+	LOCATION ('pxf://localhost:51200/ranger_test/pxfwritable_hdfs_textsimple1?PROFILE=HdfsTextSimple')
+	FORMAT 'TEXT' (delimiter=E',');
+psql:/tmp/TestHawqRanger_PXFHDFSTest.sql:7: WARNING:  usage privilege of namespace testhawqranger_pxfhdfstest is required.
+psql:/tmp/TestHawqRanger_PXFHDFSTest.sql:7: ERROR:  permission denied for external protocol pxf

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf2_success.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf2_success.ans b/src/test/feature/Ranger/ans/pxf2_success.ans
new file mode 100644
index 0000000..1f37ff8
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf2_success.ans
@@ -0,0 +1,8 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf2';
+SET
+CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writabletbl_1(location text, month text, num_orders int, total_sales float8)
+	LOCATION ('pxf://localhost:51200/ranger_test/pxfwritable_hdfs_textsimple1?PROFILE=HdfsTextSimple')
+	FORMAT 'TEXT' (delimiter=E',');
+CREATE EXTERNAL TABLE

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf3_success.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf3_success.ans b/src/test/feature/Ranger/ans/pxf3_success.ans
new file mode 100644
index 0000000..d6ccbba
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf3_success.ans
@@ -0,0 +1,10 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf2';
+SET
+INSERT INTO pxf_hdfs_writabletbl_1 VALUES ( 'Frankfurt', 'Mar', 777, 3956.98 );
+INSERT 0 1
+INSERT INTO pxf_hdfs_writabletbl_1 VALUES ( 'Cleveland', 'Oct', 3812, 96645.37 );
+INSERT 0 1
+drop external table pxf_hdfs_writabletbl_1;
+DROP EXTERNAL TABLE

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf4_fail.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf4_fail.ans b/src/test/feature/Ranger/ans/pxf4_fail.ans
new file mode 100644
index 0000000..b7562b3
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf4_fail.ans
@@ -0,0 +1,9 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf3';
+SET
+CREATE EXTERNAL TABLE pxf_hdfs_textsimple_r1(location text, month text, num_orders int, total_sales float8)
+	LOCATION ('pxf://localhost:51200/ranger_test/pxfwritable_hdfs_textsimple1?PROFILE=HdfsTextSimple')
+	FORMAT 'CSV';
+psql:/tmp/TestHawqRanger_PXFHDFSTest.sql:7: WARNING:  usage privilege of namespace testhawqranger_pxfhdfstest is required.
+psql:/tmp/TestHawqRanger_PXFHDFSTest.sql:7: ERROR:  permission denied for external protocol pxf

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf4_success.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf4_success.ans b/src/test/feature/Ranger/ans/pxf4_success.ans
new file mode 100644
index 0000000..5bbe124
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf4_success.ans
@@ -0,0 +1,8 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf3';
+SET
+CREATE EXTERNAL TABLE pxf_hdfs_textsimple_r1(location text, month text, num_orders int, total_sales float8)
+	LOCATION ('pxf://localhost:51200/ranger_test/pxfwritable_hdfs_textsimple1?PROFILE=HdfsTextSimple')
+	FORMAT 'CSV';
+CREATE EXTERNAL TABLE

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf5_success.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf5_success.ans b/src/test/feature/Ranger/ans/pxf5_success.ans
new file mode 100644
index 0000000..89f744c
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf5_success.ans
@@ -0,0 +1,13 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf3';
+SET
+SELECT * FROM pxf_hdfs_textsimple_r1;
+ location  | month | num_orders | total_sales 
+-----------+-------+------------+-------------
+ Cleveland | Oct   |       3812 |    96645.37
+ Frankfurt | Mar   |        777 |     3956.98
+(2 rows)
+
+drop external table pxf_hdfs_textsimple_r1;
+DROP EXTERNAL TABLE

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf6_fail.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf6_fail.ans b/src/test/feature/Ranger/ans/pxf6_fail.ans
new file mode 100644
index 0000000..cced8c2
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf6_fail.ans
@@ -0,0 +1,8 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf4';
+SET
+CREATE EXTERNAL TABLE testhive_ext(a int, b int)
+	LOCATION ('pxf://localhost:51200/default.testhive_ext?PROFILE=Hive')
+	FORMAT 'custom' (formatter='pxfwritable_import');
+psql:/tmp/TestHawqRanger_PXFHiveTest.sql:7: ERROR:  permission denied for external protocol pxf

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf6_success.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf6_success.ans b/src/test/feature/Ranger/ans/pxf6_success.ans
new file mode 100644
index 0000000..e808202
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf6_success.ans
@@ -0,0 +1,8 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf4';
+SET
+CREATE EXTERNAL TABLE testhive_ext(a int, b int)
+	LOCATION ('pxf://localhost:51200/default.testhive_ext?PROFILE=Hive')
+	FORMAT 'custom' (formatter='pxfwritable_import');
+CREATE EXTERNAL TABLE

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf7_success.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf7_success.ans b/src/test/feature/Ranger/ans/pxf7_success.ans
new file mode 100644
index 0000000..b58cc54
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf7_success.ans
@@ -0,0 +1,14 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf4';
+SET
+select * from testhive_ext;
+ a | b 
+---+---
+ 1 | 2
+ 2 | 4
+ 3 | 6
+(3 rows)
+
+drop external table testhive_ext;
+DROP EXTERNAL TABLE

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf8_fail.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf8_fail.ans b/src/test/feature/Ranger/ans/pxf8_fail.ans
new file mode 100644
index 0000000..8318711
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf8_fail.ans
@@ -0,0 +1,9 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf5';
+SET
+CREATE EXTERNAL TABLE test_hbase (recordkey bytea,"f1:col1" int) 
+	LOCATION ('pxf://localhost:51200/test_hbase?Profile=HBase')
+	FORMAT 'CUSTOM' (Formatter='pxfwritable_import');
+psql:/tmp/TestHawqRanger_PXFHBaseTest.sql:7: WARNING:  usage privilege of namespace testhawqranger_pxfhbasetest is required.
+psql:/tmp/TestHawqRanger_PXFHBaseTest.sql:7: ERROR:  permission denied for external protocol pxf

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf8_success.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf8_success.ans b/src/test/feature/Ranger/ans/pxf8_success.ans
new file mode 100644
index 0000000..bbf901a
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf8_success.ans
@@ -0,0 +1,8 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf5';
+SET
+CREATE EXTERNAL TABLE test_hbase (recordkey bytea,"f1:col1" int) 
+	LOCATION ('pxf://localhost:51200/test_hbase?Profile=HBase')
+	FORMAT 'CUSTOM' (Formatter='pxfwritable_import');
+CREATE EXTERNAL TABLE

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/ans/pxf9_success.ans
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/ans/pxf9_success.ans b/src/test/feature/Ranger/ans/pxf9_success.ans
new file mode 100644
index 0000000..599ead7
--- /dev/null
+++ b/src/test/feature/Ranger/ans/pxf9_success.ans
@@ -0,0 +1,12 @@
+-- start_ignore
+-- end_ignore
+set session role= 'userpxf5';
+SET
+select * from test_hbase;
+ recordkey | f1:col1 
+-----------+---------
+ r1        |     100
+(1 row)
+
+drop EXTERNAL TABLE test_hbase;
+DROP EXTERNAL TABLE

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/data/testhive_externaltable.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/data/testhive_externaltable.sql b/src/test/feature/Ranger/data/testhive_externaltable.sql
new file mode 100644
index 0000000..3727ec1
--- /dev/null
+++ b/src/test/feature/Ranger/data/testhive_externaltable.sql
@@ -0,0 +1,5 @@
+drop table if exists testhive_ext;
+CREATE TABLE testhive_ext (a int, b int) ;
+INSERT INTO testhive_ext VALUES(1, 2);
+INSERT INTO testhive_ext VALUES(2, 4);
+INSERT INTO testhive_ext VALUES(3, 6);

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/policy/10000/1.json
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/policy/10000/1.json b/src/test/feature/Ranger/policy/10000/1.json
deleted file mode 100644
index e3a4d86..0000000
--- a/src/test/feature/Ranger/policy/10000/1.json
+++ /dev/null
@@ -1 +0,0 @@
-{"allowExceptions": [], "denyExceptions": [], "denyPolicyItems": [], "description": "no description", "isAuditEnabled": true, "isEnabled": true, "name": "policy10000-1", "policyItems": [{"accesses": [{"isAllowed": true, "type": "usage-schema"}, {"isAllowed": true, "type": "create"}], "conditions": [], "delegateAdmin": true, "groups": null, "users": ["usertest10000", "usersuper10000"]}], "resources": {"database": {"isExcludes": false, "isRecursive": false, "values": ["hawq_feature_test_db"]}, "schema": {"isExcludes": false, "isRecursive": false, "values": ["public"]}, "table": {"isExcludes": false, "isRecursive": false, "values": ["*"]}}, "service": "hawq", "version": 1}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/policy_helper.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/policy_helper.cpp b/src/test/feature/Ranger/policy_helper.cpp
new file mode 100644
index 0000000..277a203
--- /dev/null
+++ b/src/test/feature/Ranger/policy_helper.cpp
@@ -0,0 +1,204 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+#include <iostream>
+#include <stdlib.h>
+#include <string.h>
+#include <unistd.h>
+#include <fcntl.h>
+#include "policy_helper.h"
+#include "lib/command.h"
+#include "lib/string_util.h"
+
+using namespace std;
+
+#define TMP_FOLDER "/tmp/ranger_test/"
+
+
+PolicyHelper::PolicyHelper (string root_path, string ranger_host): _root_path (root_path), _rangerhost(ranger_host)
+{}
+
+PolicyHelper::~PolicyHelper ()
+{}
+
+void PolicyHelper::Reset() 
+{
+	_policys.clear();
+}
+
+int PolicyHelper::add_common_tsf(string policy_name, string user, 
+		string database, string schema, string tsf_field, string tsf_value, 				
+		vector<string> accesses)
+{
+
+	const char * policy_template = 
+	"{\
+		\"allowExceptions\": [ ], \
+		\"denyExceptions\": [ ], \
+		\"denyPolicyItems\": [ ], \
+		\"description\": \"no description\", \
+		\"isAuditEnabled\": true, \
+		\"isEnabled\": true, \
+		\"name\": \"%s\", \
+		\"policyItems\": [\
+			{\
+				\"accesses\": [ %s ], \
+				\"conditions\": [ ], \
+				\"delegateAdmin\": true, \
+				\"groups\": null, \
+				\"users\": [\"%s\"]\
+			}\
+		], \
+		\"resources\": {\
+			\"database\": {\
+				\"isExcludes\": false, \
+				\"isRecursive\": false, \
+				\"values\": [\"%s\"]\
+			}, \
+			\"schema\": {\
+				\"isExcludes\": false, \
+				\"isRecursive\": false, \
+				\"values\": [\"%s\"]\
+			}, \
+			\"%s\": {\
+				\"isExcludes\": false, \
+				\"isRecursive\": false, \
+				\"values\": [\"%s\"]\
+			}\
+		}, \
+		\"service\": \"hawq\", \
+		\"version\": 3\
+	}";
+
+	string accesses_str;
+	for (auto access : accesses)
+	{
+		const char * access_template = "{\"isAllowed\": true, \"type\": \"%s\"}";
+		string acc_str = hawq::test::stringFormat(access_template, access.c_str());
+		if (accesses_str != "")
+		{
+			accesses_str += ",";
+		}
+		accesses_str += acc_str;
+	}
+	
+	string policy_str = hawq::test::stringFormat(policy_template, 
+		policy_name.c_str(), accesses_str.c_str(), user.c_str(), 
+		database.c_str(), schema.c_str(), tsf_field.c_str(), tsf_value.c_str());
+	
+	_policys.push_back(policy_str);
+	//cout<<policy_str<<endl;
+
+	return 0;
+}
+
+int PolicyHelper::add_common_sp(std::string policy_name, std::string user, 
+		std::string tsp_field, std::string tsp_value, 				
+		std::vector<std::string> accesses)
+{
+	const char * policy_template = 
+	"{\
+		\"allowExceptions\": [ ], \
+		\"denyExceptions\": [ ], \
+		\"denyPolicyItems\": [ ], \
+		\"description\": \"no description\", \
+		\"isAuditEnabled\": true, \
+		\"isEnabled\": true, \
+		\"name\": \"%s\", \
+		\"policyItems\": [\
+			{\
+				\"accesses\": [ %s ], \
+				\"conditions\": [ ], \
+				\"delegateAdmin\": true, \
+				\"groups\": null, \
+				\"users\": [\"%s\"]\
+			}\
+		], \
+		\"resources\": {\
+			\"%s\": {\
+				\"isExcludes\": false, \
+				\"isRecursive\": false, \
+				\"values\": [\"%s\"]\
+			}\
+		}, \
+		\"service\": \"hawq\", \
+		\"version\": 3\
+	}";
+
+	string accesses_str;
+	for (auto access : accesses)
+	{
+		const char * access_template = "{\"isAllowed\": true, \"type\": \"%s\"}";
+		string acc_str = hawq::test::stringFormat(access_template, access.c_str());
+		if (accesses_str != "")
+		{
+			accesses_str += ",";
+		}
+		accesses_str += acc_str;
+	}
+	
+	string policy_str = hawq::test::stringFormat(policy_template, 
+		policy_name.c_str(), accesses_str.c_str(), user.c_str(), tsp_field.c_str(), tsp_value.c_str());
+	
+	_policys.push_back(policy_str);
+	return 0;
+}
+
+string PolicyHelper::write_tmpfile(std::string content)
+{
+	char temp[] = TMP_FOLDER "fileXXXXXX"; 
+	const char *file_name = mktemp(temp);
+	
+	int fd = open(file_name, O_CREAT | O_TRUNC | O_WRONLY);
+	if (fd <= 0)
+		return "";
+	write(fd, content.c_str(), content.length());
+	close(fd);
+	return string(file_name);
+}
+
+int PolicyHelper::ActivateAllPoliciesOnRanger()
+{
+	auto cmd = "mkdir -p " TMP_FOLDER;
+	hawq::test::Command::getCommandStatus(cmd);
+	for (auto policy : _policys) 
+	{
+		string file_path= write_tmpfile(policy);	
+		if (file_path == "")
+			return -1;
+		auto cmd = hawq::test::stringFormat("python %s/Ranger/rangerpolicy.py -h %s -a %s", 
+			_root_path.c_str(), _rangerhost.c_str(), file_path.c_str() );
+		int ret = hawq::test::Command::getCommandStatus(cmd);
+		if (ret != 0)
+			return -1;
+	}		
+	sleep(60);
+	return 0;
+}
+
+int PolicyHelper::DeletePolicyOnRanger(std::string policy_name)
+{
+	auto cmd = hawq::test::stringFormat("python %s/Ranger/rangerpolicy.py -h %s -d %s", 
+		_root_path.c_str(), _rangerhost.c_str(), policy_name.c_str() );
+	int ret = hawq::test::Command::getCommandStatus(cmd);
+	if (ret != 0)
+		return -1;
+	return 0;
+}
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/policy_helper.h
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/policy_helper.h b/src/test/feature/Ranger/policy_helper.h
new file mode 100644
index 0000000..ce990ad
--- /dev/null
+++ b/src/test/feature/Ranger/policy_helper.h
@@ -0,0 +1,110 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+#ifndef _POLICY_HELPER_H_
+#define _POLICY_HELPER_H_
+
+#include <string>
+#include <vector>
+
+
+/**
+ * A helper class for ranger policy add/del, using rangerpolicy.py internally
+ * Usage see test_policyhelper.cpp
+ */ 
+class PolicyHelper{
+public:
+	PolicyHelper (std::string root_path, std::string ranger_host);
+	~PolicyHelper ();
+	void Reset();
+
+	/*
+	 * send all policies to ranger and sleep 60s for taking effect
+	 * @returns: 0 success; -1 failed
+	 */ 
+	int ActivateAllPoliciesOnRanger();
+
+	/*
+	Resources hierarchy:
+	|--database
+	|         |--schema
+	|         |       |--table
+	|         |       |--sequence
+	|         |       |--function
+	|         |
+	|         |--language
+	|         
+	|--tablespace
+	|--protocol  
+	*/
+	int AddSchemaPolicy(std::string policy_name, std::string user, 
+		std::string database, std::string schema, std::vector<std::string> accesses) 
+	{
+		return add_common_tsf(policy_name, user, database, schema, "table", "*", accesses);
+	}
+	
+	int AddTablePolicy(std::string policy_name, std::string user, 
+		std::string database, std::string schema, std::string table, std::vector<std::string> accesses) 
+	{
+		return add_common_tsf(policy_name, user, database, schema, "table", table, accesses);
+	}
+	
+	int AddProtocolPolicy(std::string policy_name, std::string user, 
+		std::string protocol, std::vector<std::string> accesses) 
+	{
+		return add_common_sp(policy_name, user, "protocol", protocol, accesses);
+	}
+
+	//TODO other AddXXXPolicy function
+	
+	int DeletePolicyOnRanger(std::string user);
+
+private:
+	std::string _rangerhost;
+	std::string _root_path;
+
+	std::vector<std::string> _policys;
+
+	std::string write_tmpfile(std::string content);
+	int add_common_t(std::string policy_name, std::string user, 
+		std::string database, std::string schema, std::string tsf_field, std::string tsf_value, 				
+		std::vector<std::string> accesses);
+	
+	/**
+	 * tsf means:
+	 *	t: table
+	 *	s: schema
+	 *	f: function
+	 */ 
+	int add_common_tsf(std::string policy_name, std::string user, 
+		std::string database, std::string schema, std::string tsf_field, std::string tsf_value, 				
+		std::vector<std::string> accesses);
+	
+	/**
+	 * sp means:
+	 *	s: tablespace
+	 *	p: protocol
+	 */ 
+	int add_common_sp(std::string policy_name, std::string user, 
+		std::string tsp_field, std::string tsp_value, 				
+		std::vector<std::string> accesses);
+};
+
+#endif
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/pxfpolicy/1/3.json
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/pxfpolicy/1/3.json b/src/test/feature/Ranger/pxfpolicy/1/3.json
index 08937c4..d04dcc5 100644
--- a/src/test/feature/Ranger/pxfpolicy/1/3.json
+++ b/src/test/feature/Ranger/pxfpolicy/1/3.json
@@ -1 +1 @@
-{"allowExceptions": [], "denyExceptions": [], "denyPolicyItems": [], "description": "no description", "isAuditEnabled": true, "isEnabled": true, "name": "pxfpolicy1-3", "policyItems": [{"accesses": [{"isAllowed": true, "type": "usage-schema"}], "conditions": [], "delegateAdmin": true, "groups": null, "users": ["userpxf1"]}], "resources": {"database": {"isExcludes": false, "isRecursive": false, "values": ["hawq_feature_test_db"]}, "schema": {"isExcludes": false, "isRecursive": false, "values": ["testhawqranger_hcatalogtest"]}, "table": {"isExcludes": false, "isRecursive": false, "values": ["*"]}}, "service": "hawq", "version": 3}
+{"allowExceptions": [], "denyExceptions": [], "denyPolicyItems": [], "description": "no description", "isAuditEnabled": true, "isEnabled": true, "name": "pxfpolicy1-3", "policyItems": [{"accesses": [{"isAllowed": true, "type": "usage-schema"}], "conditions": [], "delegateAdmin": true, "groups": null, "users": ["userpxf1"]}], "resources": {"database": {"isExcludes": false, "isRecursive": false, "values": ["hawq_feature_test_db"]}, "schema": {"isExcludes": false, "isRecursive": false, "values": ["testhawqranger_pxfhcatalogtest"]}, "table": {"isExcludes": false, "isRecursive": false, "values": ["*"]}}, "service": "hawq", "version": 3}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/rangerpolicy.py
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/rangerpolicy.py b/src/test/feature/Ranger/rangerpolicy.py
index b5a7c48..cfc4d66 100644
--- a/src/test/feature/Ranger/rangerpolicy.py
+++ b/src/test/feature/Ranger/rangerpolicy.py
@@ -62,26 +62,30 @@ def create_policy(policy_json_file_name, rangerhelper):
             policy_end_pos = response.find("], service=[")
             dup_policy_name = response[0:policy_end_pos]
             
-            #get dupulicate policy and add privilege item.
+            #get duplicate policy and add privilege item.
             service_name = 'hawq'
-            print dup_policy_name;
-            response, is_success = rangerhelper.get_policy(service_name, dup_policy_name);
-            response_dict = json.load(response)
-            for new_policy_item in json_decode['policyItems']:
-                response_dict["policyItems"].append(new_policy_item)
-            for new_policy_item in json_decode['denyPolicyItems']:
-                response_dict["denyPolicyItems"].append(new_policy_item)
-            for new_policy_item in json_decode['allowExceptions']:
-                response_dict["allowExceptions"].append(new_policy_item)
-            for new_policy_item in json_decode['denyExceptions']:
-                response_dict["denyExceptions"].append(new_policy_item)
+            print "find duplicate policy, try to update [%s]" % (dup_policy_name)
+            response, is_success = rangerhelper.get_policy(service_name, dup_policy_name)
+            if is_success:
+                response_dict = json.load(response)
+                for new_policy_item in json_decode['policyItems']:
+                    response_dict["policyItems"].append(new_policy_item)
+                for new_policy_item in json_decode['denyPolicyItems']:
+                    response_dict["denyPolicyItems"].append(new_policy_item)
+                for new_policy_item in json_decode['allowExceptions']:
+                    response_dict["allowExceptions"].append(new_policy_item)
+                for new_policy_item in json_decode['denyExceptions']:
+                    response_dict["denyExceptions"].append(new_policy_item)
+                response, is_success = rangerhelper.update_policy(service_name, dup_policy_name, \
+                    json.dumps(response_dict))
+            else:
+                return policyname, False
                 
-            rangerhelper.update_policy(service_name, dup_policy_name, \
-                                    json.dumps(response_dict));
-        return policyname
+        return policyname, is_success
 
 def delete_policy(delete_policy_name, rangerhelper):
-    rangerhelper.delete_policy("hawq", delete_policy_name);
+    response, is_success = rangerhelper.delete_policy("hawq", delete_policy_name)
+    return is_success
     
     
 if __name__ == '__main__':
@@ -96,12 +100,22 @@ if __name__ == '__main__':
     delete_policy_name = options.deletedpolicyname
     
     #init rangerresthelper
-    helper = RangerRestHelper(host, port, rangeruser, rangerpasswd);
+    helper = RangerRestHelper(host, port, rangeruser, rangerpasswd)
     
     if new_policy_json_file_name != "":
-        policyname = create_policy(new_policy_json_file_name, helper)
-        print "policy {} created".format(policyname)
+        policyname, is_success = create_policy(new_policy_json_file_name, helper)
+        if is_success:
+            print "policy {} created".format(policyname)
+        else:
+            print "policy {} create failed".format(policyname)
+            sys.exit(-1)
         
     if delete_policy_name != "":
-        delete_policy(delete_policy_name, helper)
-        print "policy {} deleted".format(delete_policy_name)
+        is_success = delete_policy(delete_policy_name, helper)
+        if is_success:
+            print "policy {} deleted".format(delete_policy_name)
+        else:
+            print "policy {} delete failed".format(delete_policy_name)
+            sys.exit(-1)
+
+    sys.exit(0)

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/rangerrest.py
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/rangerrest.py b/src/test/feature/Ranger/rangerrest.py
index 851cdd8..fa3196a 100644
--- a/src/test/feature/Ranger/rangerrest.py
+++ b/src/test/feature/Ranger/rangerrest.py
@@ -46,6 +46,7 @@ class RangerRestHelper(object):
                 error_message = e.read()
                 print error_message
                 return error_message, False
+            return "HTTPError", False
     
     def get_policy(self, service_name, policy_name):
         url = 'http://' + self.host + ':' + self.port + '/service/public/v2/api/service/' + \

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/sql/manual/10000.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/sql/manual/10000.sql b/src/test/feature/Ranger/sql/manual/10000.sql
index 3912cb3..e217179 100644
--- a/src/test/feature/Ranger/sql/manual/10000.sql
+++ b/src/test/feature/Ranger/sql/manual/10000.sql
@@ -1,4 +1,4 @@
-set session role=usertest10000;
+set session role=usermanual10000;
 select count(*) from information_schema.view_table_usage;
 select count(*) from hawq_toolkit.hawq_table_indexes;
 select count(*) from pg_catalog.pg_compression;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/sql/pxf/2.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/sql/pxf/2.sql b/src/test/feature/Ranger/sql/pxf/2.sql
new file mode 100644
index 0000000..ba8483b
--- /dev/null
+++ b/src/test/feature/Ranger/sql/pxf/2.sql
@@ -0,0 +1,4 @@
+set session role= 'userpxf2';
+CREATE WRITABLE EXTERNAL TABLE pxf_hdfs_writabletbl_1(location text, month text, num_orders int, total_sales float8)
+	LOCATION ('pxf://localhost:51200/ranger_test/pxfwritable_hdfs_textsimple1?PROFILE=HdfsTextSimple')
+	FORMAT 'TEXT' (delimiter=E',');

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/sql/pxf/3.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/sql/pxf/3.sql b/src/test/feature/Ranger/sql/pxf/3.sql
new file mode 100644
index 0000000..10b1938
--- /dev/null
+++ b/src/test/feature/Ranger/sql/pxf/3.sql
@@ -0,0 +1,4 @@
+set session role= 'userpxf2';
+INSERT INTO pxf_hdfs_writabletbl_1 VALUES ( 'Frankfurt', 'Mar', 777, 3956.98 );
+INSERT INTO pxf_hdfs_writabletbl_1 VALUES ( 'Cleveland', 'Oct', 3812, 96645.37 );
+drop external table pxf_hdfs_writabletbl_1;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/sql/pxf/4.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/sql/pxf/4.sql b/src/test/feature/Ranger/sql/pxf/4.sql
new file mode 100644
index 0000000..20e3728
--- /dev/null
+++ b/src/test/feature/Ranger/sql/pxf/4.sql
@@ -0,0 +1,4 @@
+set session role= 'userpxf3';
+CREATE EXTERNAL TABLE pxf_hdfs_textsimple_r1(location text, month text, num_orders int, total_sales float8)
+	LOCATION ('pxf://localhost:51200/ranger_test/pxfwritable_hdfs_textsimple1?PROFILE=HdfsTextSimple')
+	FORMAT 'CSV';

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/sql/pxf/5.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/sql/pxf/5.sql b/src/test/feature/Ranger/sql/pxf/5.sql
new file mode 100644
index 0000000..b022fb2
--- /dev/null
+++ b/src/test/feature/Ranger/sql/pxf/5.sql
@@ -0,0 +1,3 @@
+set session role= 'userpxf3';
+SELECT * FROM pxf_hdfs_textsimple_r1;
+drop external table pxf_hdfs_textsimple_r1;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/sql/pxf/6.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/sql/pxf/6.sql b/src/test/feature/Ranger/sql/pxf/6.sql
new file mode 100644
index 0000000..50cdbbe
--- /dev/null
+++ b/src/test/feature/Ranger/sql/pxf/6.sql
@@ -0,0 +1,4 @@
+set session role= 'userpxf4';
+CREATE EXTERNAL TABLE testhive_ext(a int, b int)
+	LOCATION ('pxf://localhost:51200/default.testhive_ext?PROFILE=Hive')
+	FORMAT 'custom' (formatter='pxfwritable_import');

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/sql/pxf/7.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/sql/pxf/7.sql b/src/test/feature/Ranger/sql/pxf/7.sql
new file mode 100644
index 0000000..28eb39e
--- /dev/null
+++ b/src/test/feature/Ranger/sql/pxf/7.sql
@@ -0,0 +1,3 @@
+set session role= 'userpxf4';
+select * from testhive_ext;
+drop external table testhive_ext;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/sql/pxf/8.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/sql/pxf/8.sql b/src/test/feature/Ranger/sql/pxf/8.sql
new file mode 100644
index 0000000..71a8e76
--- /dev/null
+++ b/src/test/feature/Ranger/sql/pxf/8.sql
@@ -0,0 +1,4 @@
+set session role= 'userpxf5';
+CREATE EXTERNAL TABLE test_hbase (recordkey bytea,"f1:col1" int) 
+	LOCATION ('pxf://localhost:51200/test_hbase?Profile=HBase')
+	FORMAT 'CUSTOM' (Formatter='pxfwritable_import');

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/sql/pxf/9.sql
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/sql/pxf/9.sql b/src/test/feature/Ranger/sql/pxf/9.sql
new file mode 100644
index 0000000..6d8b3b9
--- /dev/null
+++ b/src/test/feature/Ranger/sql/pxf/9.sql
@@ -0,0 +1,3 @@
+set session role= 'userpxf5';
+select * from test_hbase;
+drop EXTERNAL TABLE test_hbase;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/test_policyhelper.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/test_policyhelper.cpp b/src/test/feature/Ranger/test_policyhelper.cpp
new file mode 100644
index 0000000..b2c55af
--- /dev/null
+++ b/src/test/feature/Ranger/test_policyhelper.cpp
@@ -0,0 +1,92 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements.  See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership.  The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License.  You may obtain a copy of the License at
+ *
+ *   http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied.  See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ */
+
+#include "policy_helper.h"
+#include "gtest/gtest.h"
+#include "lib/sql_util.h"
+
+using namespace std;
+
+class TestRangerPolicyHelper : public ::testing::Test {
+public:
+	TestRangerPolicyHelper() 
+	{
+		_rangerHost = RANGER_HOST;
+	}
+	string _rangerHost;
+	string _suffix = "PolicyHelper_";
+};
+
+TEST_F(TestRangerPolicyHelper, BasicTest) {
+	hawq::test::SQLUtility util;
+	PolicyHelper helper(util.getTestRootPath(), _rangerHost);
+	int ret = 0;
+	string user = _suffix+"TestUser"; //Note: must create this user on Ranger Admin first
+	string database = _suffix+"Testdb";
+	string table = _suffix+"TestTable";
+	string schema = "public";
+	string protocol= "protocol";
+	std::vector<std::string> accesses;
+	
+	ret = helper.AddSchemaPolicy(_suffix+"Policy1", user, database, schema, {"usage-schema", "create-schema"});	
+	EXPECT_EQ(0,ret);
+
+	accesses.clear();
+	accesses.push_back("select");
+	ret = helper.AddTablePolicy(_suffix+"Policy2", user, database, schema, table, accesses);	
+	EXPECT_EQ(0,ret);
+	
+	accesses.clear();
+	accesses.push_back("select");
+	ret = helper.AddProtocolPolicy(_suffix+"Policy3", user, protocol, accesses);	
+	EXPECT_EQ(0,ret);
+
+	ret = helper.ActivateAllPoliciesOnRanger();
+	EXPECT_EQ(0,ret);
+
+	ret = helper.DeletePolicyOnRanger(_suffix+"Policy1");
+	ret = helper.DeletePolicyOnRanger(_suffix+"Policy2");
+	ret = helper.DeletePolicyOnRanger(_suffix+"Policy3");
+	EXPECT_EQ(0,ret);
+}
+
+
+TEST_F(TestRangerPolicyHelper, DuplicateResource) {
+	hawq::test::SQLUtility util;
+	PolicyHelper helper(util.getTestRootPath(), _rangerHost);
+	int ret = 0;
+	string user = _suffix+"TestUser"; //Note: must create this user on Ranger Admin first
+	string user2 = _suffix+"TestUser2"; //Note: must create this user on Ranger Admin first
+	string database = _suffix+"Testdb";
+	string schema = "public";
+	
+	ret = helper.AddSchemaPolicy(_suffix+"Policy1", user, database, schema, {"usage-schema"});	
+	EXPECT_EQ(0,ret);
+	ret = helper.AddSchemaPolicy(_suffix+"Policy2", user2, database, schema, {"create-schema"});	
+	EXPECT_EQ(0,ret);
+	
+	ret = helper.ActivateAllPoliciesOnRanger(); //policy2's access combine into policy1
+	EXPECT_EQ(0,ret);
+	
+	ret = helper.DeletePolicyOnRanger(_suffix+"Policy1");
+	EXPECT_EQ(0,ret);
+	ret = helper.DeletePolicyOnRanger(_suffix+"Policy2");
+	EXPECT_EQ(-1,ret);
+}
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/test_ranger.cpp
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/test_ranger.cpp b/src/test/feature/Ranger/test_ranger.cpp
index 9a61826..b386956 100644
--- a/src/test/feature/Ranger/test_ranger.cpp
+++ b/src/test/feature/Ranger/test_ranger.cpp
@@ -18,6 +18,7 @@
  */
 
 #include "test_ranger.h"
+#include "policy_helper.h"
 
 #include <string>
 #include <pwd.h>
@@ -123,7 +124,6 @@ TEST_F(TestHawqRanger, BasicTest) {
 			string super_ansfile_success = hawq::test::stringFormat("Ranger/ans/super%d_success.ans", i);
 
 			cmd = hawq::test::stringFormat("ls -l %s/Ranger/policy/%d/ 2>/dev/null| grep \"^-\" | wc -l", rootPath.c_str(), i);
-			int policy_num = std::atoi(Command::getCommandOutput(cmd).c_str());
 
 			cmd = hawq::test::stringFormat("ls -l %s/Ranger/sql/super/%d.sql 2>/dev/null | grep \"^-\" | wc -l", rootPath.c_str(), i);
 			int supersqlexist = std::atoi(Command::getCommandOutput(cmd).c_str());
@@ -179,39 +179,31 @@ static void clear_env(SQLUtility &util, int sql_id, string rootPath, string rang
 
 TEST_F(TestHawqRanger, FallbackTest) {
 	SQLUtility util;
+	PolicyHelper helper(util.getTestRootPath(), rangerHost);
 
 	if (util.getGUCValue("hawq_acl_type") == "ranger")
 	{
-		string rootPath(util.getTestRootPath());
-		string rangerHost = RANGER_HOST;
-		string initfile = "Ranger/sql/init_file";
-		string cmd;
-		int FallbackCase = 10000;
-		int i = FallbackCase;
-
-		// clear environment
-		clear_env(util, i, rootPath, rangerHost);
-
-		// create user_num
-		std::string normalusername = hawq::test::stringFormat("usertest%d", i);;
-		std::string superusername = hawq::test::stringFormat("usersuper%d", i);;
-		util.execute(hawq::test::stringFormat("create role %s with login createdb;", normalusername.c_str()),true);
-		util.execute(hawq::test::stringFormat("create role %s with login createdb superuser;", superusername.c_str()),true);
-		// add user
-		cmd = hawq::test::stringFormat("python %s/Ranger/rangeruser.py -h %s -u %s,%s", rootPath.c_str(),
-			rangerHost.c_str(), normalusername.c_str(), superusername.c_str());
-		Command::getCommandStatus(cmd);
-		// add policy
-		cmd = hawq::test::stringFormat("python %s/Ranger/rangerpolicy.py -h %s -a %s/Ranger/policy/%d/%d.json", rootPath.c_str(), rangerHost.c_str(), rootPath.c_str(), i, 1);
-		Command::getCommandStatus(cmd);
-		sleep(60);
-
-		// run sql test
-		string normal_sqlfile = hawq::test::stringFormat("Ranger/sql/manual/%d.sql", i);
-		string normal_ansfile_success = hawq::test::stringFormat("Ranger/ans/normal%d_success.ans", i);
-		util.execSQLFile(normal_sqlfile, normal_ansfile_success, initfile, true, true);
-
-		clear_env(util, i, rootPath, rangerHost);
+		int idx = 10000;
+		int ret = 0;
+		const char * prefix = "manual";
+		std::string db = "hawq_feature_test_db";
+		std::string schema = get_private_schema_name();
+		std::string user = hawq::test::stringFormat("user%s%d", prefix, idx); 
+
+		addUser(&util, prefix, idx, false); 
+
+		// all needed policies
+		helper.AddSchemaPolicy("policy10000-1", user, db, schema, {"usage-schema"});
+
+		ret = helper.ActivateAllPoliciesOnRanger();
+		EXPECT_EQ(0,ret);
+		runSQLFile(&util, prefix, "success", idx, false, true, true); 
+		
+		//delete user 
+		delUser(&util, prefix, idx); 
+
+		//delete policy
+		helper.DeletePolicyOnRanger("pxfpolicy10000-1");
     }
 }
 
@@ -225,6 +217,7 @@ TEST_F(TestHawqRanger, DenyTest) {
 		runSQLFile(&util, "deny", "succeed", 1);
 		addPolicy(&util, "deny", 1);
 		runSQLFile(&util, "deny", "fail", 1);
+		clearEnv(&util, "deny", 1);
 	}
 }
 
@@ -242,6 +235,8 @@ TEST_F(TestHawqRanger, DenyExcludeTest) {
 		runSQLFile(&util, "denyexclude", "fail", 2);
 		addPolicy(&util, "denyexclude", 2);
 		runSQLFile(&util, "denyexclude", "succeed2", 2);
+		clearEnv(&util, "denyexclude", 2);
+		clearEnv(&util, "deny", 2);
 	}
 }
 
@@ -259,6 +254,8 @@ TEST_F(TestHawqRanger, AllowExcludeTest) {
 
 		addPolicy(&util, "allowexclude", 3);
 		runSQLFile(&util, "allowexclude", "fail", 3);
+		clearEnv(&util, "allowexclude", 3);
+		clearEnv(&util, "allow", 3);
 	}
 }
 
@@ -279,6 +276,8 @@ TEST_F(TestHawqRanger, ResourceExcludeTest) {
 		//add usage-schema to public
 		addPolicy(&util, "allow", 4);
 		runSQLFile(&util, "resourceexclude", "succeed", 4);
+		clearEnv(&util, "resourceexclude", 4);
+		clearEnv(&util, "allow", 4);
 	}
 }
 
@@ -298,6 +297,8 @@ TEST_F(TestHawqRanger, ResourceExcludeStarTest) {
 		//add usage-schema to public
 		addPolicy(&util, "allow", 5);
 		runSQLFile(&util, "resourceexclude", "fail2", 5);
+		clearEnv(&util, "resourceexclude", 5);
+		clearEnv(&util, "allow", 5);
 	}
 }
 
@@ -311,10 +312,15 @@ TEST_F(TestHawqRanger, ResourceIncludeATest) {
 
 		addPolicy(&util, "allow", 6);
 		runSQLFile(&util, "allow", "fail", 6);
+		clearEnv(&util, "allow", 6);
 	}
 }
 
-TEST_F(TestHawqRanger, HcatalogTest) {
+
+/**
+ * read HIVE data using hcatalog(read-only) with PXF
+ */ 
+TEST_F(TestHawqRanger, PXFHcatalogTest) {
 	SQLUtility util;
 	if (util.getGUCValue("hawq_acl_type") == "ranger")
 	{
@@ -322,8 +328,6 @@ TEST_F(TestHawqRanger, HcatalogTest) {
 		 * create a table in hive and populate some rows
 		 */
 		clearEnv(&util, "pxf", 1);
-		clearEnv(&util, "pxf", 2);
-		clearEnv(&util, "pxf", 3);
 		string rootPath(util.getTestRootPath());
 		string sqlPath = rootPath + "/Ranger/data/testhive.sql";
 		auto cmd =  hawq::test::stringFormat("hive -f %s", sqlPath.c_str());
@@ -338,13 +342,214 @@ TEST_F(TestHawqRanger, HcatalogTest) {
 		/*
 		 * add allow policies for this user and query again, succeed.
 		 */
-		addPolicy(&util, "pxf", 1); // usage of default
-		addPolicy(&util, "pxf", 2); // select of table
-		addPolicy(&util, "pxf", 3); // usage of current schema(e.g.testhawqranger_hcatalogtest)
+		/*
+			usage of default
+			select of table
+			usage of current schema(e.g.testhawqranger_hcatalogtest)
+		*/
+		addPolicy(&util, "pxf", 1); 
 		runSQLFile(&util, "pxf", "success", 1);
+		
+		//clean
+		clearEnv(&util, "pxf", 1);
+	}
+}
+
+/**
+ * read and write HDFS data using external table with PXF
+ */ 
+TEST_F(TestHawqRanger, PXFHDFSTest) {
+	SQLUtility util;
+	PolicyHelper helper(util.getTestRootPath(), rangerHost);
+
+	//case idx and folder prefix
+	const char * prefix = "pxf";
+	int idx = 0;
+	int ret = 0;
+
+	if (util.getGUCValue("hawq_acl_type") == "ranger")
+	{
+		std::string db = "hawq_feature_test_db";
+		std::string schema = get_private_schema_name();
+		bool usingDefaultSchema= false;
+		std::string user; 
+
+		// clean hdfs folder
+		std::string cmd = "";
+		cmd =  hawq::test::stringFormat("hdfs dfs -rm -r /ranger_test/");
+		Command::getCommandStatus(cmd);
+		cmd =  hawq::test::stringFormat("hdfs dfs -mkdir /ranger_test/");
+		Command::getCommandStatus(cmd);
+
+		// -- write --
+		idx = 2;
+		addUser(&util, prefix, idx, false); 
+		user = hawq::test::stringFormat("user%s%d", prefix ,idx); 
+
+		runSQLFile(&util, prefix, "fail", 2, usingDefaultSchema, false, true); // create writable table
+		
+		// all needed policies
+		helper.AddSchemaPolicy("pxfpolicy2-1", user, db, schema, {"usage-schema","create"});
+		helper.AddProtocolPolicy("pxfpolicy2-2", user, "pxf", {"insert"});
+		helper.AddTablePolicy("pxfpolicy2-3", user, db, schema, "pxf_hdfs_writabletbl_1", {"insert"});
+
+		ret = helper.ActivateAllPoliciesOnRanger();
+		EXPECT_EQ(0,ret);
+		runSQLFile(&util, prefix, "success", 2, usingDefaultSchema, false, true); // create table
+		runSQLFile(&util, prefix, "success", 3, usingDefaultSchema, false, true); // insert
+		
+		
+		// -- read --
+		idx = 3;
+		addUser(&util, prefix, idx, false);
+		user = hawq::test::stringFormat("user%s%d", prefix ,idx); 
+			
+		runSQLFile(&util, prefix, "fail", 4, usingDefaultSchema, false, true); // create readable table
+
+		helper.Reset();
+		// all needed policies
+		helper.AddSchemaPolicy("pxfpolicy3-1", user, db, schema, {"usage-schema","create"});
+		helper.AddProtocolPolicy("pxfpolicy3-2", user, "pxf", {"select"});
+		helper.AddTablePolicy("pxfpolicy3-3", user, db, schema, "pxf_hdfs_textsimple_r1", {"select"});
+
+		ret = helper.ActivateAllPoliciesOnRanger();
+		EXPECT_EQ(0,ret);
+		runSQLFile(&util, prefix, "success", 4, usingDefaultSchema, false, true); // create table
+		runSQLFile(&util, prefix, "success", 5, usingDefaultSchema, false, true); // select
+
+		//delete user 
+		delUser(&util, prefix, 2); 
+		delUser(&util, prefix, 3); 
+
+		//delete policy
+		helper.DeletePolicyOnRanger("pxfpolicy2-1");
+		helper.DeletePolicyOnRanger("pxfpolicy2-2");
+		helper.DeletePolicyOnRanger("pxfpolicy2-3");
+		helper.DeletePolicyOnRanger("pxfpolicy3-1");
+		helper.DeletePolicyOnRanger("pxfpolicy3-2");
+		helper.DeletePolicyOnRanger("pxfpolicy3-3");
 	}
 }
 
+/**
+ * read Hive data using external table with PXF
+ */ 
+TEST_F(TestHawqRanger, PXFHiveTest) {
+	SQLUtility util;
+	PolicyHelper helper(util.getTestRootPath(), rangerHost);
+
+	//case idx and folder prefix
+	const char * prefix = "pxf";
+	int idx = 0;
+	int ret = 0;
+
+	if (util.getGUCValue("hawq_acl_type") == "ranger")
+	{
+		std::string db = "hawq_feature_test_db";
+		std::string schema = get_private_schema_name();
+		bool usingDefaultSchema= false;
+		std::string user; 
+		
+		// create hive table
+		string rootPath(util.getTestRootPath());
+		string sqlPath = rootPath + "/Ranger/data/testhive_externaltable.sql";
+		auto cmd =  hawq::test::stringFormat("hive -f %s", sqlPath.c_str());
+		Command::getCommandStatus(cmd);
+
+		// -- read --
+		idx = 4;
+		addUser(&util, prefix, idx, false);
+		user = hawq::test::stringFormat("user%s%d", prefix ,idx); 
+			
+		runSQLFile(&util, prefix, "fail", 6, usingDefaultSchema, false, true); // create readable table
+
+		helper.Reset();
+		// all needed policies
+		helper.AddSchemaPolicy("pxfpolicy4-1", user, db, schema, {"usage-schema","create"});
+		helper.AddProtocolPolicy("pxfpolicy4-2", user, "pxf", {"select"});
+		helper.AddTablePolicy("pxfpolicy4-3", user, db, schema, "testhive_ext", {"select"});
+
+		ret = helper.ActivateAllPoliciesOnRanger();
+		EXPECT_EQ(0,ret);
+		runSQLFile(&util, prefix, "success", 6, usingDefaultSchema, false, true); // create table
+		runSQLFile(&util, prefix, "success", 7, usingDefaultSchema, false, true); // select
+
+		//delete user 
+		delUser(&util, prefix, idx); 
+
+		//delete policy
+		helper.DeletePolicyOnRanger("pxfpolicy4-1");
+		helper.DeletePolicyOnRanger("pxfpolicy4-2");
+		helper.DeletePolicyOnRanger("pxfpolicy4-3");
+	}
+}
+
+/**
+ * read HBase data using external table with PXF
+ */ 
+TEST_F(TestHawqRanger, PXFHBaseTest) {
+	SQLUtility util;
+	PolicyHelper helper(util.getTestRootPath(), rangerHost);
+
+	//case idx and folder prefix
+	const char * prefix = "pxf";
+	int idx = 0;
+	int ret = 0;
+
+	if (util.getGUCValue("hawq_acl_type") == "ranger")
+	{
+		std::string db = "hawq_feature_test_db";
+		std::string schema = get_private_schema_name();
+		bool usingDefaultSchema= false;
+		std::string user; 
+		
+		// create hbase table
+		auto cmd =  hawq::test::stringFormat(
+			"echo \" create 'test_hbase','f1'; put 'test_hbase','r1', 'f1:col1','100' \" | hbase shell");
+		Command::getCommandStatus(cmd);
+
+		// -- read --
+		idx = 5;
+		addUser(&util, prefix, idx, false);
+		user = hawq::test::stringFormat("user%s%d", prefix ,idx); 
+			
+		runSQLFile(&util, prefix, "fail", 8, usingDefaultSchema, false, true); // create readable table
+
+		helper.Reset();
+		// all needed policies
+		helper.AddSchemaPolicy("pxfpolicy5-1", user, db, schema, {"usage-schema","create"});
+		helper.AddProtocolPolicy("pxfpolicy5-2", user, "pxf", {"select"});
+		helper.AddTablePolicy("pxfpolicy5-3", user, db, schema, "test_hbase", {"select"});
+
+		ret = helper.ActivateAllPoliciesOnRanger();
+		EXPECT_EQ(0,ret);
+		runSQLFile(&util, prefix, "success", 8, usingDefaultSchema, false, true); // create table
+		runSQLFile(&util, prefix, "success", 9, usingDefaultSchema, false, true); // select
+
+		//delete user 
+		delUser(&util, prefix, idx); 
+
+		//delete policy
+		helper.DeletePolicyOnRanger("pxfpolicy5-1");
+		helper.DeletePolicyOnRanger("pxfpolicy5-2");
+		helper.DeletePolicyOnRanger("pxfpolicy5-3");
+		
+		// drop hbase table
+		cmd =  hawq::test::stringFormat(
+			"echo \" disable 'test_hbase'; drop 'test_hbase' \" | hbase shell");
+		Command::getCommandStatus(cmd);
+	}
+}
+
+// only drop user in database
+void TestHawqRanger::delUser(hawq::test::SQLUtility* util, std::string case_name, int user_index)
+{
+	string rootPath = util->getTestRootPath();
+	string cmd = "";
+	std::string username = hawq::test::stringFormat("user%s%d", case_name.c_str(), user_index);
+	util->execute(hawq::test::stringFormat("drop role %s;", username.c_str()), false);
+}
+
 void TestHawqRanger::addUser(hawq::test::SQLUtility* util, std::string case_name, int user_index, bool full_policy, int writable_index)
 {
 	string rootPath = util->getTestRootPath();
@@ -425,7 +630,7 @@ void TestHawqRanger::clearEnv(hawq::test::SQLUtility* util, std::string case_nam
 	}
 }
 
-void TestHawqRanger::runSQLFile(hawq::test::SQLUtility* util, std::string case_name, std::string ans_suffix, int sql_index)
+void TestHawqRanger::runSQLFile(hawq::test::SQLUtility* util, std::string case_name, std::string ans_suffix, int sql_index, bool usingDefaultSchema, bool printTupleOnly, bool focus_run)
 {
 	string rootPath = util->getTestRootPath();
 	auto cmd = hawq::test::stringFormat("ls -l %s/Ranger/sql/%s/*.sql 2>/dev/null| grep \"^-\" | wc -l", rootPath.c_str(), case_name.c_str());
@@ -441,8 +646,8 @@ void TestHawqRanger::runSQLFile(hawq::test::SQLUtility* util, std::string case_n
 			auto cmd = hawq::test::stringFormat("ls -l %s/Ranger/%spolicy/%d/ 2>/dev/null| grep \"^-\"| wc -l", rootPath.c_str(), case_name.c_str(), i);
 			int policy_num = std::atoi(Command::getCommandOutput(cmd).c_str());
 
-			if (policy_num > 0){
-				util->execSQLFile(deny_sqlfile, deny_ansfile_succeed, initfile);
+			if (focus_run || policy_num > 0){
+				util->execSQLFile(deny_sqlfile, deny_ansfile_succeed, initfile, usingDefaultSchema, printTupleOnly);
 			}
 		}
 	} else {
@@ -452,8 +657,8 @@ void TestHawqRanger::runSQLFile(hawq::test::SQLUtility* util, std::string case_n
 		auto cmd = hawq::test::stringFormat("ls -l %s/Ranger/%spolicy/%d/ 2>/dev/null| grep \"^-\"| wc -l", rootPath.c_str(), case_name.c_str(), sql_index);
 		int policy_num = std::atoi(Command::getCommandOutput(cmd).c_str());
 
-		if (policy_num > 0){
-			util->execSQLFile(deny_sqlfile, deny_ansfile_succeed, initfile);
+		if (focus_run || policy_num > 0){
+			util->execSQLFile(deny_sqlfile, deny_ansfile_succeed, initfile, usingDefaultSchema, printTupleOnly);
 		}
 	}
 }
@@ -485,3 +690,15 @@ void TestHawqRanger::addPolicy(hawq::test::SQLUtility* util, std::string case_na
 	}
 	sleep(60);
 }
+
+/**
+ * get the private schema name based by current test
+ * example: testhawqranger_xxxtest 	
+ */ 
+std::string TestHawqRanger::get_private_schema_name()
+{
+	const ::testing::TestInfo *const test_info = ::testing::UnitTest::GetInstance()->current_test_info();
+	string data = string(test_info->test_case_name()) + "_" + test_info->name();
+	std::transform(data.begin(), data.end(), data.begin(), ::tolower);
+	return data;
+}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/Ranger/test_ranger.h
----------------------------------------------------------------------
diff --git a/src/test/feature/Ranger/test_ranger.h b/src/test/feature/Ranger/test_ranger.h
index 31283f3..d7f78dc 100644
--- a/src/test/feature/Ranger/test_ranger.h
+++ b/src/test/feature/Ranger/test_ranger.h
@@ -33,12 +33,15 @@ public:
 	std::string initfile;
 
 	void clearEnv(hawq::test::SQLUtility* util, std::string case_name, int user_index);
-	void runSQLFile(hawq::test::SQLUtility* util, std::string case_name,
-			std::string ans_suffix, int sql_index = -1);
+	void runSQLFile(hawq::test::SQLUtility* util, std::string case_name, std::string ans_suffix, int sql_index = -1, 
+		bool usingDefaultSchema = false, bool printTupleOnly = false, bool focus_run = false);
 
 	void addPolicy(hawq::test::SQLUtility* util, std::string case_name, int policy_index);
 	void addUser(hawq::test::SQLUtility* util, std::string case_name, int user_index = -1, bool full_policy = false,
 			int writable_index = -1);
+	void delUser(hawq::test::SQLUtility* util, std::string case_name, int user_index);
+
+	std::string get_private_schema_name();
 };
 
 #endif

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/72928dab/src/test/feature/sanity_tests.txt
----------------------------------------------------------------------
diff --git a/src/test/feature/sanity_tests.txt b/src/test/feature/sanity_tests.txt
index bc6df0b..384f6fa 100644
--- a/src/test/feature/sanity_tests.txt
+++ b/src/test/feature/sanity_tests.txt
@@ -2,5 +2,5 @@
 #SERIAL=* are the serial tests to run, optional but should not be empty
 #you can have several PARALLEL or SRRIAL
 
-PARALLEL=TestErrorTable.*:TestPreparedStatement.*:TestUDF.*:TestAOSnappy.*:TestAlterOwner.*:TestAlterTable.*:TestCreateTable.*:TestGuc.*:TestType.*:TestDatabase.*:TestParquet.*:TestPartition.*:TestSubplan.*:TestAggregate.*:TestCreateTypeComposite.*:TestGpDistRandom.*:TestInformationSchema.*:TestQueryInsert.*:TestQueryNestedCaseNull.*:TestQueryPolymorphism.*:TestQueryPortal.*:TestQueryPrepare.*:TestQuerySequence.*:TestCommonLib.*:TestToast.*:TestTransaction.*:TestCommand.*:TestCopy.*:TestHawqRegister.TestPartitionTableMultilevel:TestHawqRegister.TestUsage1ExpectSuccessDifferentSchema:TestHawqRegister.TestUsage1ExpectSuccess:TestHawqRegister.TestUsage1SingleHawqFile:TestHawqRegister.TestUsage1SingleHiveFile:TestHawqRegister.TestDataTypes:TestHawqRegister.TestUsage1EofSuccess:TestHawqRegister.TestUsage2Case1Expected:TestHawqRegister.TestUsage2Case2Expected
-SERIAL=TestHawqRanger.*:TestExternalOid.TestExternalOidAll:TestExternalTable.TestExternalTableAll:TestTemp.BasicTest:TestRowTypes.*
+PARALLEL=TestErrorTable.*:TestPreparedStatement.*:TestUDF.*:TestAOSnappy.*:TestAlterOwner.*:TestAlterTable.*:TestCreateTable.*:TestGuc.*:TestType.*:TestDatabase.*:TestParquet.*:TestPartition.*:TestSubplan.*:TestAggregate.*:TestCreateTypeComposite.*:TestGpDistRandom.*:TestInformationSchema.*:TestQueryInsert.*:TestQueryNestedCaseNull.*:TestQueryPolymorphism.*:TestQueryPortal.*:TestQueryPrepare.*:TestQuerySequence.*:TestCommonLib.*:TestToast.*:TestTransaction.*:TestCommand.*:TestCopy.*:TestHawqRegister.TestPartitionTableMultilevel:TestHawqRegister.TestUsage1ExpectSuccessDifferentSchema:TestHawqRegister.TestUsage1ExpectSuccess:TestHawqRegister.TestUsage1SingleHawqFile:TestHawqRegister.TestUsage1SingleHiveFile:TestHawqRegister.TestDataTypes:TestHawqRegister.TestUsage1EofSuccess:TestHawqRegister.TestUsage2Case1Expected:TestHawqRegister.TestUsage2Case2Expected:TestHawqRanger.FallbackTest:TestHawqRanger.PXF*
+SERIAL=TestHawqRanger.BasicTest:TestHawqRanger.Deny*:TestHawqRanger.Allow*:TestHawqRanger.Resource*:TestExternalOid.TestExternalOidAll:TestExternalTable.TestExternalTableAll:TestTemp.BasicTest:TestRowTypes.*


Mime
View raw message