You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hawq.apache.org by rl...@apache.org on 2016/10/28 03:53:08 UTC
[05/17] incubator-hawq git commit: HAWQ-1122. Fix ORCA - gpdb
exception handling. This closes #793
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f6b76472/src/test/regress/expected/namespace_optimizer.out
----------------------------------------------------------------------
diff --git a/src/test/regress/expected/namespace_optimizer.out b/src/test/regress/expected/namespace_optimizer.out
new file mode 100644
index 0000000..88a4411
--- /dev/null
+++ b/src/test/regress/expected/namespace_optimizer.out
@@ -0,0 +1,142 @@
+--
+-- Regression tests for schemas (namespaces)
+--
+CREATE SCHEMA test_schema_1
+ CREATE INDEX abc_a_idx ON abc (a)
+ CREATE VIEW abc_view AS
+ SELECT a+1 AS a, b+1 AS b FROM abc
+ CREATE TABLE abc (
+ a serial,
+ b int UNIQUE
+ ) DISTRIBUTED BY (b);
+NOTICE: CREATE TABLE will create implicit sequence "abc_a_seq" for serial column "abc.a"
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "abc_b_key" for table "abc"
+-- verify that the objects were created
+SELECT COUNT(*) FROM pg_class WHERE relnamespace =
+ (SELECT oid FROM pg_namespace WHERE nspname = 'test_schema_1');
+ count
+-------
+ 5
+(1 row)
+
+INSERT INTO test_schema_1.abc DEFAULT VALUES;
+INSERT INTO test_schema_1.abc DEFAULT VALUES;
+INSERT INTO test_schema_1.abc DEFAULT VALUES;
+SELECT * FROM test_schema_1.abc;
+ a | b
+---+---
+ 1 |
+ 2 |
+ 3 |
+(3 rows)
+
+SELECT * FROM test_schema_1.abc_view;
+ a | b
+---+---
+ 2 |
+ 3 |
+ 4 |
+(3 rows)
+
+-- Test GRANT/REVOKE
+CREATE SCHEMA test_schema_2;
+CREATE TABLE test_schema_2.abc as select * from test_schema_1.abc DISTRIBUTED BY (a);
+create role tmp_test_schema_role RESOURCE QUEUE pg_default;
+GRANT ALL ON SCHEMA test_schema_1 to tmp_test_schema_role;
+SET SESSION AUTHORIZATION tmp_test_schema_role;
+CREATE TABLE test_schema_1.grant_test(a int) DISTRIBUTED BY (a);
+DROP TABLE test_schema_1.grant_test;
+CREATE TABLE test_schema_2.grant_test(a int) DISTRIBUTED BY (a); -- no permissions on schema
+ERROR: permission denied for schema test_schema_2
+SELECT * FROM test_schema_1.abc; -- no permissions on table
+ERROR: permission denied for relation abc
+ERROR: GPDB exception. Aborting GPORCA plan generation. (CGPOptimizer.cpp:66)
+SELECT * FROM test_schema_2.abc; -- no permissions on schema
+ERROR: permission denied for schema test_schema_2
+LINE 1: SELECT * FROM test_schema_2.abc;
+ ^
+ALTER SCHEMA test_schema_1 RENAME to myschema; -- not the schema owner
+ERROR: must be owner of schema test_schema_1
+RESET SESSION AUTHORIZATION;
+DROP TABLE test_schema_2.abc;
+DROP SCHEMA test_schema_2;
+-- ALTER SCHEMA .. OWNER TO
+ALTER SCHEMA pg_toast OWNER to tmp_test_schema_role; -- system schema
+ERROR: permission denied to ALTER SCHEMA "pg_toast"
+DETAIL: Schema pg_toast is reserved for system use.
+alter schema test_schema_1 owner to tmp_test_schema_role;
+select rolname from pg_authid a, pg_namespace n where a.oid = n.nspowner
+ and nspname = 'test_schema_1';
+ rolname
+----------------------
+ tmp_test_schema_role
+(1 row)
+
+-- test CREATE SCHEMA/ALTER SCHEMA for reserved names
+CREATE SCHEMA pg_schema; -- reserved name
+ERROR: unacceptable schema name "pg_schema"
+DETAIL: The prefix "pg_" is reserved for system schemas.
+CREATE SCHEMA gp_schema; -- reserved name
+ERROR: unacceptable schema name "gp_schema"
+DETAIL: The prefix "gp_" is reserved for system schemas.
+ALTER SCHEMA test_schema_1 RENAME to pg_schema; -- reseved name
+ERROR: unacceptable schema name "pg_schema"
+DETAIL: The prefix "pg_" is reserved for system schemas.
+ALTER SCHEMA test_schema_1 RENAME to gp_schema; -- reserved name
+ERROR: unacceptable schema name "gp_schema"
+DETAIL: The prefix "gp_" is reserved for system schemas.
+ALTER SCHEMA pg_toast RENAME to bread; -- system schema
+ERROR: permission denied to ALTER SCHEMA "pg_toast"
+DETAIL: Schema pg_toast is reserved for system use.
+-- RENAME to a valid new name
+ALTER SCHEMA test_schema_1 RENAME to test_schema_2;
+-- Check that ALTER statements dispatched correctly
+select *
+FROM gp_dist_random('pg_namespace') n1
+ full outer join pg_namespace n2 on (n1.oid = n2.oid)
+WHERE n1.nspname != n2.nspname or n1.nspowner != n2.nspowner or
+ n1.nspname is null or n2.nspname is null;
+ nspname | nspowner | nspacl | nspname | nspowner | nspacl
+---------+----------+--------+---------+----------+--------
+(0 rows)
+
+-- DROP SCHEMA
+DROP SCHEMA pg_toast; -- system schema
+ERROR: cannot drop schema pg_toast because it is required by the database system
+DROP SCHEMA test_schema_1; -- does not exist
+ERROR: schema "test_schema_1" does not exist
+DROP SCHEMA test_schema_2; -- contains objects
+NOTICE: view test_schema_2.abc_view depends on schema test_schema_2
+NOTICE: rule _RETURN on view test_schema_2.abc_view depends on view test_schema_2.abc_view
+NOTICE: table test_schema_2.abc depends on schema test_schema_2
+NOTICE: default for table test_schema_2.abc column a depends on sequence test_schema_2.abc_a_seq
+ERROR: cannot drop schema test_schema_2 because other objects depend on it
+HINT: Use DROP ... CASCADE to drop the dependent objects too.
+DROP SCHEMA test_schema_2 CASCADE;
+NOTICE: drop cascades to view test_schema_2.abc_view
+NOTICE: drop cascades to rule _RETURN on view test_schema_2.abc_view
+NOTICE: drop cascades to table test_schema_2.abc
+NOTICE: drop cascades to default for table test_schema_2.abc column a
+DROP ROLE tmp_test_schema_role;
+-- verify that the objects were dropped
+SELECT nspname, relname
+FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
+WHERE nspname ~ 'test_schema_[12]';
+ nspname | relname
+---------+---------
+(0 rows)
+
+SELECT nspname
+FROM pg_namespace n
+WHERE nspname ~ 'test_schema_[12]';
+ nspname
+---------
+(0 rows)
+
+SELECT rolname
+FROM pg_authid a
+WHERE rolname ~ 'tmp_test_schema_role'
+ rolname
+---------
+(0 rows)
+
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f6b76472/src/test/regress/expected/privileges_optimizer.out
----------------------------------------------------------------------
diff --git a/src/test/regress/expected/privileges_optimizer.out b/src/test/regress/expected/privileges_optimizer.out
new file mode 100755
index 0000000..a743ac4
--- /dev/null
+++ b/src/test/regress/expected/privileges_optimizer.out
@@ -0,0 +1,733 @@
+--
+-- Test access privileges
+--
+set optimizer=off;
+-- Clean up in case a prior regression run failed
+-- Suppress NOTICE messages when users/groups don't exist
+SET client_min_messages TO 'error';
+DROP ROLE IF EXISTS regressgroup1;
+DROP ROLE IF EXISTS regressgroup2;
+DROP ROLE IF EXISTS regressuser1;
+DROP ROLE IF EXISTS regressuser2;
+DROP ROLE IF EXISTS regressuser3;
+DROP ROLE IF EXISTS regressuser4;
+DROP ROLE IF EXISTS regressuser5;
+RESET client_min_messages;
+-- test proper begins here
+CREATE USER regressuser1;
+CREATE USER regressuser2;
+CREATE USER regressuser3;
+CREATE USER regressuser4;
+CREATE USER regressuser5;
+CREATE USER regressuser5; -- duplicate
+ERROR: role "regressuser5" already exists
+CREATE GROUP regressgroup1;
+CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2;
+ALTER GROUP regressgroup1 ADD USER regressuser4;
+ALTER GROUP regressgroup2 ADD USER regressuser2; -- duplicate
+NOTICE: role "regressuser2" is already a member of role "regressgroup2"
+ALTER GROUP regressgroup2 DROP USER regressuser2;
+GRANT regressgroup2 TO regressuser4 WITH ADMIN OPTION;
+-- test owner privileges
+SET SESSION AUTHORIZATION regressuser1;
+SELECT session_user, current_user;
+ session_user | current_user
+--------------+--------------
+ regressuser1 | regressuser1
+(1 row)
+
+CREATE TABLE atest1 ( a int, b text );
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+SELECT * FROM atest1;
+ a | b
+---+---
+(0 rows)
+
+INSERT INTO atest1 VALUES (1, 'one');
+DELETE FROM atest1;
+UPDATE atest1 SET b = 'blech' WHERE a = 213;
+TRUNCATE atest1;
+LOCK atest1 IN ACCESS EXCLUSIVE MODE;
+REVOKE ALL ON atest1 FROM PUBLIC;
+NOTICE: no privileges could be revoked from role PUBLIC on object atest1
+SELECT * FROM atest1;
+ a | b
+---+---
+(0 rows)
+
+GRANT ALL ON atest1 TO regressuser2;
+GRANT SELECT ON atest1 TO regressuser3, regressuser4;
+SELECT * FROM atest1;
+ a | b
+---+---
+(0 rows)
+
+CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'col1' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+GRANT SELECT ON atest2 TO regressuser2;
+GRANT UPDATE ON atest2 TO regressuser3;
+GRANT INSERT ON atest2 TO regressuser4;
+GRANT TRUNCATE ON atest2 TO regressuser5;
+SET SESSION AUTHORIZATION regressuser2;
+SELECT session_user, current_user;
+ session_user | current_user
+--------------+--------------
+ regressuser2 | regressuser2
+(1 row)
+
+-- try various combinations of queries on atest1 and atest2
+SELECT * FROM atest1; -- ok
+ a | b
+---+---
+(0 rows)
+
+SELECT * FROM atest2; -- ok
+ col1 | col2
+------+------
+(0 rows)
+
+INSERT INTO atest1 VALUES (2, 'two'); -- ok
+INSERT INTO atest2 VALUES ('foo', true); -- fail
+ERROR: permission denied for relation atest2
+INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
+UPDATE atest1 SET b = 'twotwo' WHERE a = 2; -- ok
+UPDATE atest2 SET col2 = NOT col2; -- fail
+ERROR: permission denied for relation atest2
+SELECT * FROM atest1 FOR UPDATE; -- ok
+ a | b
+---+--------
+ 1 | two
+ 2 | twotwo
+(2 rows)
+
+SELECT * FROM atest2 FOR UPDATE; -- fail
+ERROR: permission denied for relation atest2
+DELETE FROM atest2; -- fail
+ERROR: permission denied for relation atest2
+TRUNCATE atest2; -- fail
+ERROR: permission denied for relation atest2
+LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
+ERROR: permission denied for relation atest2
+COPY atest2 FROM stdin; -- fail
+ERROR: permission denied for relation atest2
+GRANT ALL ON atest1 TO PUBLIC; -- fail
+WARNING: no privileges were granted for "atest1"
+-- checks in subquery, both ok
+SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
+ a | b
+---+---
+(0 rows)
+
+SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
+ col1 | col2
+------+------
+(0 rows)
+
+SET SESSION AUTHORIZATION regressuser3;
+SELECT session_user, current_user;
+ session_user | current_user
+--------------+--------------
+ regressuser3 | regressuser3
+(1 row)
+
+SELECT * FROM atest1; -- ok
+ a | b
+---+--------
+ 1 | two
+ 2 | twotwo
+(2 rows)
+
+SELECT * FROM atest2; -- fail
+ERROR: permission denied for relation atest2
+INSERT INTO atest1 VALUES (2, 'two'); -- fail
+ERROR: permission denied for relation atest1
+INSERT INTO atest2 VALUES ('foo', true); -- fail
+ERROR: permission denied for relation atest2
+INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
+ERROR: permission denied for relation atest1
+UPDATE atest1 SET b = 'twotwo' WHERE a = 2; -- fail
+ERROR: permission denied for relation atest1
+UPDATE atest2 SET col2 = NULL; -- ok
+UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
+ERROR: permission denied for relation atest2
+UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5; -- ok
+SELECT * FROM atest1 FOR UPDATE; -- fail
+ERROR: permission denied for relation atest1
+SELECT * FROM atest2 FOR UPDATE; -- fail
+ERROR: permission denied for relation atest2
+DELETE FROM atest2; -- fail
+ERROR: permission denied for relation atest2
+TRUNCATE atest2; -- fail
+ERROR: permission denied for relation atest2
+LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
+COPY atest2 FROM stdin; -- fail
+ERROR: permission denied for relation atest2
+-- checks in subquery, both fail
+SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
+ERROR: permission denied for relation atest2
+SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
+ERROR: permission denied for relation atest2
+SET SESSION AUTHORIZATION regressuser4;
+COPY atest2 FROM stdin; -- ok
+SELECT * FROM atest1; -- ok
+ a | b
+---+--------
+ 1 | two
+ 2 | twotwo
+(2 rows)
+
+-- groups
+SET SESSION AUTHORIZATION regressuser3;
+CREATE TABLE atest3 (one int, two int, three int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'one' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+GRANT DELETE ON atest3 TO GROUP regressgroup2;
+SET SESSION AUTHORIZATION regressuser1;
+SELECT * FROM atest3; -- fail
+ERROR: permission denied for relation atest3
+DELETE FROM atest3; -- ok
+-- views
+SET SESSION AUTHORIZATION regressuser3;
+CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
+/* The next *should* fail, but it's not implemented that way yet. */
+CREATE VIEW atestv2 AS SELECT * FROM atest2;
+CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
+SELECT * FROM atestv1; -- ok
+ a | b
+---+--------
+ 1 | two
+ 2 | twotwo
+(2 rows)
+
+SELECT * FROM atestv2; -- fail
+ERROR: permission denied for relation atest2
+GRANT SELECT ON atestv1, atestv3 TO regressuser4;
+GRANT SELECT ON atestv2 TO regressuser2;
+SET SESSION AUTHORIZATION regressuser4;
+SELECT * FROM atestv1; -- ok
+ a | b
+---+--------
+ 1 | two
+ 2 | twotwo
+(2 rows)
+
+SELECT * FROM atestv2; -- fail
+ERROR: permission denied for relation atestv2
+SELECT * FROM atestv3; -- ok
+ one | two | three
+-----+-----+-------
+(0 rows)
+
+CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view
+SELECT * FROM atestv4; -- ok
+ one | two | three
+-----+-----+-------
+(0 rows)
+
+GRANT SELECT ON atestv4 TO regressuser2;
+SET SESSION AUTHORIZATION regressuser2;
+-- Two complex cases:
+SELECT * FROM atestv3; -- fail
+ERROR: permission denied for relation atestv3
+SELECT * FROM atestv4; -- ok (even though regressuser2 cannot access underlying atestv3)
+ one | two | three
+-----+-----+-------
+(0 rows)
+
+SELECT * FROM atest2; -- ok
+ col1 | col2
+------+------
+ bar | t
+(1 row)
+
+SELECT * FROM atestv2; -- fail (even though regressuser2 can access underlying atest2)
+ERROR: permission denied for relation atest2
+-- privileges on functions, languages
+-- switch to superuser
+\c -
+REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
+GRANT USAGE ON LANGUAGE sql TO regressuser1; -- ok
+GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail
+ERROR: language "c" is not trusted
+HINT: Only superusers can use untrusted languages.
+SET SESSION AUTHORIZATION regressuser1;
+GRANT USAGE ON LANGUAGE sql TO regressuser2; -- fail
+WARNING: no privileges were granted for "sql"
+CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql CONTAINS SQL;
+CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql CONTAINS SQL;
+REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC;
+GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regressuser2;
+GRANT USAGE ON FUNCTION testfunc1(int) TO regressuser3; -- semantic error
+ERROR: invalid privilege type USAGE for function
+GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regressuser4;
+GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regressuser4;
+ERROR: function testfunc_nosuch(integer) does not exist
+CREATE FUNCTION testfunc4(boolean) RETURNS text
+ AS 'select col1 from atest2 where col2 = $1;'
+ LANGUAGE sql SECURITY DEFINER READS SQL DATA;
+GRANT EXECUTE ON FUNCTION testfunc4(boolean) TO regressuser3;
+SET SESSION AUTHORIZATION regressuser2;
+SELECT testfunc1(5), testfunc2(5); -- ok
+ testfunc1 | testfunc2
+-----------+-----------
+ 10 | 15
+(1 row)
+
+CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql CONTAINS SQL; -- fail
+ERROR: permission denied for language sql
+SET SESSION AUTHORIZATION regressuser3;
+SELECT testfunc1(5); -- fail
+ERROR: permission denied for function testfunc1
+SELECT col1 FROM atest2 WHERE col2 = true; -- fail
+ERROR: permission denied for relation atest2
+ERROR: GPDB exception. Aborting GPORCA plan generation. (CGPOptimizer.cpp:66)
+SELECT testfunc4(true); -- ok
+ testfunc4
+-----------
+ bar
+(1 row)
+
+SET SESSION AUTHORIZATION regressuser4;
+SELECT testfunc1(5); -- ok
+ testfunc1
+-----------
+ 10
+(1 row)
+
+DROP FUNCTION testfunc1(int); -- fail
+ERROR: must be owner of function testfunc1
+\c -
+DROP FUNCTION testfunc1(int); -- ok
+-- restore to sanity
+GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;
+-- truncate
+SET SESSION AUTHORIZATION regressuser5;
+TRUNCATE atest2; -- ok
+TRUNCATE atest3; -- fail
+ERROR: permission denied for relation atest3
+-- has_table_privilege function
+-- bad-input checks
+select has_table_privilege(NULL,'pg_authid','select');
+ has_table_privilege
+---------------------
+
+(1 row)
+
+select has_table_privilege('pg_shad','select');
+ has_table_privilege
+---------------------
+
+(1 row)
+
+select has_table_privilege('nosuchuser','pg_authid','select');
+ERROR: role "nosuchuser" does not exist
+select has_table_privilege('pg_authid','sel');
+ERROR: unrecognized privilege type: "sel"
+select has_table_privilege(-999999,'pg_authid','update');
+ERROR: role with OID 4293967297 does not exist
+select has_table_privilege(1,'select');
+ has_table_privilege
+---------------------
+
+(1 row)
+
+-- superuser
+\c -
+select has_table_privilege(current_user,'pg_authid','select');
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(current_user,'pg_authid','insert');
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,'pg_authid','update')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,'pg_authid','delete')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+-- 'rule' privilege no longer exists, but for backwards compatibility
+-- has_table_privilege still recognizes the keyword and says FALSE
+select has_table_privilege(current_user,t1.oid,'rule')
+from (select oid from pg_class where relname = 'pg_authid') as t1;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(current_user,t1.oid,'references')
+from (select oid from pg_class where relname = 'pg_authid') as t1;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'select')
+from (select oid from pg_class where relname = 'pg_authid') as t1,
+ (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'insert')
+from (select oid from pg_class where relname = 'pg_authid') as t1,
+ (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege('pg_authid','update');
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege('pg_authid','delete');
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege('pg_authid','truncate');
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t1.oid,'select')
+from (select oid from pg_class where relname = 'pg_authid') as t1;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t1.oid,'trigger')
+from (select oid from pg_class where relname = 'pg_authid') as t1;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+-- non-superuser
+SET SESSION AUTHORIZATION regressuser3;
+select has_table_privilege(current_user,'pg_class','select');
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(current_user,'pg_class','insert');
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,'pg_class','update')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,'pg_class','delete')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(current_user,t1.oid,'references')
+from (select oid from pg_class where relname = 'pg_class') as t1;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'select')
+from (select oid from pg_class where relname = 'pg_class') as t1,
+ (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'insert')
+from (select oid from pg_class where relname = 'pg_class') as t1,
+ (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('pg_class','update');
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('pg_class','delete');
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('pg_class','truncate');
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t1.oid,'select')
+from (select oid from pg_class where relname = 'pg_class') as t1;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t1.oid,'trigger')
+from (select oid from pg_class where relname = 'pg_class') as t1;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(current_user,'atest1','select');
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(current_user,'atest1','insert');
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,'atest1','update')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,'atest1','delete')
+from (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(current_user,t1.oid,'references')
+from (select oid from pg_class where relname = 'atest1') as t1;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'select')
+from (select oid from pg_class where relname = 'atest1') as t1,
+ (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t2.oid,t1.oid,'insert')
+from (select oid from pg_class where relname = 'atest1') as t1,
+ (select oid from pg_roles where rolname = current_user) as t2;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('atest1','update');
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('atest1','delete');
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege('atest1','truncate');
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+select has_table_privilege(t1.oid,'select')
+from (select oid from pg_class where relname = 'atest1') as t1;
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+select has_table_privilege(t1.oid,'trigger')
+from (select oid from pg_class where relname = 'atest1') as t1;
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+-- Grant options
+SET SESSION AUTHORIZATION regressuser1;
+CREATE TABLE atest4 (a int);
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+GRANT SELECT ON atest4 TO regressuser2 WITH GRANT OPTION;
+GRANT UPDATE ON atest4 TO regressuser2;
+GRANT SELECT ON atest4 TO GROUP regressgroup1 WITH GRANT OPTION;
+SET SESSION AUTHORIZATION regressuser2;
+GRANT SELECT ON atest4 TO regressuser3;
+GRANT UPDATE ON atest4 TO regressuser3; -- fail
+WARNING: no privileges were granted for "atest4"
+SET SESSION AUTHORIZATION regressuser1;
+REVOKE SELECT ON atest4 FROM regressuser3; -- does nothing
+NOTICE: no privileges could be revoked from role regressuser3 on object atest4
+SELECT has_table_privilege('regressuser3', 'atest4', 'SELECT'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+REVOKE SELECT ON atest4 FROM regressuser2; -- fail
+ERROR: dependent privileges exist
+HINT: Use CASCADE to revoke them too.
+REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regressuser2 CASCADE; -- ok
+SELECT has_table_privilege('regressuser2', 'atest4', 'SELECT'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+SELECT has_table_privilege('regressuser3', 'atest4', 'SELECT'); -- false
+ has_table_privilege
+---------------------
+ f
+(1 row)
+
+SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
+ has_table_privilege
+---------------------
+ t
+(1 row)
+
+-- Admin options
+SET SESSION AUTHORIZATION regressuser4;
+CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS
+ 'GRANT regressgroup2 TO regressuser5';
+GRANT regressgroup2 TO regressuser5; -- ok: had ADMIN OPTION
+SET ROLE regressgroup2;
+GRANT regressgroup2 TO regressuser5; -- fails: SET ROLE suspended privilege
+ERROR: must have admin option on role "regressgroup2"
+SET SESSION AUTHORIZATION regressuser1;
+GRANT regressgroup2 TO regressuser5; -- fails: no ADMIN OPTION
+ERROR: must have admin option on role "regressgroup2"
+SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN
+NOTICE: role "regressuser5" is already a member of role "regressgroup2"
+CONTEXT: SQL function "dogrant_ok" statement 1
+ dogrant_ok
+------------
+
+(1 row)
+
+SET ROLE regressgroup2;
+GRANT regressgroup2 TO regressuser5; -- fails: SET ROLE did not help
+ERROR: must have admin option on role "regressgroup2"
+SET SESSION AUTHORIZATION regressgroup2;
+GRANT regressgroup2 TO regressuser5; -- ok: a role can self-admin
+NOTICE: role "regressuser5" is already a member of role "regressgroup2"
+CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS
+ 'GRANT regressgroup2 TO regressuser5';
+SELECT dogrant_fails(); -- fails: no self-admin in SECURITY DEFINER
+ERROR: must have admin option on role "regressgroup2"
+CONTEXT: SQL function "dogrant_fails" statement 1
+DROP FUNCTION dogrant_fails();
+SET SESSION AUTHORIZATION regressuser4;
+DROP FUNCTION dogrant_ok();
+REVOKE regressgroup2 FROM regressuser5;
+-- test that dependent privileges are revoked (or not) properly
+\c -
+set session role regressuser1;
+create table dep_priv_test (a int);
+grant select on dep_priv_test to regressuser2 with grant option;
+grant select on dep_priv_test to regressuser3 with grant option;
+set session role regressuser2;
+grant select on dep_priv_test to regressuser4 with grant option;
+set session role regressuser3;
+grant select on dep_priv_test to regressuser4 with grant option;
+set session role regressuser4;
+grant select on dep_priv_test to regressuser5;
+\dp dep_priv_test
+ Access privileges for database "regression"
+ Schema | Name | Type | Access privileges
+--------+---------------+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ public | dep_priv_test | table | {regressuser1=arwdDxt/regressuser1,regressuser2=r*/regressuser1,regressuser3=r*/regressuser1,regressuser4=r*/regressuser2,regressuser4=r*/regressuser3,regressuser5=r/regressuser4}
+(1 row)
+
+set session role regressuser2;
+revoke select on dep_priv_test from regressuser4 cascade;
+\dp dep_priv_test
+ Access privileges for database "regression"
+ Schema | Name | Type | Access privileges
+--------+---------------+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------
+ public | dep_priv_test | table | {regressuser1=arwdDxt/regressuser1,regressuser2=r*/regressuser1,regressuser3=r*/regressuser1,regressuser4=r*/regressuser3,regressuser5=r/regressuser4}
+(1 row)
+
+set session role regressuser3;
+revoke select on dep_priv_test from regressuser4 cascade;
+\dp dep_priv_test
+ Access privileges for database "regression"
+ Schema | Name | Type | Access privileges
+--------+---------------+-------+-----------------------------------------------------------------------------------------------
+ public | dep_priv_test | table | {regressuser1=arwdDxt/regressuser1,regressuser2=r*/regressuser1,regressuser3=r*/regressuser1}
+(1 row)
+
+set session role regressuser1;
+drop table dep_priv_test;
+-- clean up
+\c regression
+DROP FUNCTION testfunc2(int);
+DROP FUNCTION testfunc4(boolean);
+DROP VIEW atestv1;
+DROP VIEW atestv2;
+-- this should cascade to drop atestv4
+DROP VIEW atestv3 CASCADE;
+NOTICE: drop cascades to rule _RETURN on view atestv4
+NOTICE: drop cascades to view atestv4
+-- this should complain "does not exist"
+DROP VIEW atestv4;
+ERROR: view "atestv4" does not exist
+DROP TABLE atest1;
+DROP TABLE atest2;
+DROP TABLE atest3;
+DROP TABLE atest4;
+DROP GROUP regressgroup1;
+DROP GROUP regressgroup2;
+REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
+DROP USER regressuser1;
+DROP USER regressuser2;
+DROP USER regressuser3;
+DROP USER regressuser4;
+DROP USER regressuser5;
+reset optimizer;
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f6b76472/src/test/regress/expected/role_optimizer.out
----------------------------------------------------------------------
diff --git a/src/test/regress/expected/role_optimizer.out b/src/test/regress/expected/role_optimizer.out
new file mode 100644
index 0000000..0f75ca9
--- /dev/null
+++ b/src/test/regress/expected/role_optimizer.out
@@ -0,0 +1,107 @@
+--
+-- ROLE
+--
+-- MPP-15479: ALTER ROLE SET statement
+DROP ROLE IF EXISTS role_112911;
+NOTICE: role "role_112911" does not exist, skipping
+NOTICE: role "role_112911" does not exist, skipping (seg0 localhost:12001 pid=20806)
+NOTICE: role "role_112911" does not exist, skipping (seg1 localhost:12002 pid=20807)
+CREATE ROLE role_112911 WITH LOGIN;
+NOTICE: resource queue required -- using default resource queue "pg_default"
+CREATE SCHEMA common_schema;
+/* Alter Role Set statement_mem */
+ALTER ROLE role_112911 SET statement_mem TO '150MB';
+SELECT gp_segment_id, rolname, array_to_string(rolconfig,',') as rolconfig
+ FROM pg_authid WHERE rolname = 'role_112911'
+ UNION ALL
+SELECT DISTINCT 0 as gp_segment_id, rolname, array_to_string(rolconfig,',') as rolconfig
+ FROM gp_dist_random('pg_authid') WHERE rolname = 'role_112911';
+ gp_segment_id | rolname | rolconfig
+---------------+-------------+---------------------
+ -1 | role_112911 | statement_mem=150MB
+ 0 | role_112911 | statement_mem=150MB
+(2 rows)
+
+/* Alter Role Set search_path */
+ALTER ROLE role_112911 SET search_path = common_schema;
+SELECT gp_segment_id, rolname, array_to_string(rolconfig,',') as rolconfig
+ FROM pg_authid WHERE rolname = 'role_112911'
+ UNION ALL
+SELECT DISTINCT 0 as gp_segment_id, rolname, array_to_string(rolconfig,',') as rolconfig
+ FROM gp_dist_random('pg_authid') WHERE rolname = 'role_112911';
+ gp_segment_id | rolname | rolconfig
+---------------+-------------+-----------------------------------------------
+ -1 | role_112911 | statement_mem=150MB,search_path=common_schema
+ 0 | role_112911 | statement_mem=150MB,search_path=common_schema
+(2 rows)
+
+/* Alter Role Reset statement_mem */
+ALTER ROLE role_112911 RESET statement_mem;
+SELECT gp_segment_id, rolname, array_to_string(rolconfig,',') as rolconfig
+ FROM pg_authid WHERE rolname = 'role_112911'
+ UNION ALL
+SELECT DISTINCT 0 as gp_segment_id, rolname, array_to_string(rolconfig,',') as rolconfig
+ FROM gp_dist_random('pg_authid') WHERE rolname = 'role_112911';
+ gp_segment_id | rolname | rolconfig
+---------------+-------------+---------------------------
+ -1 | role_112911 | search_path=common_schema
+ 0 | role_112911 | search_path=common_schema
+(2 rows)
+
+/* Alter Role Set statement_mem */
+ALTER ROLE role_112911 SET statement_mem = 100000;
+SELECT gp_segment_id, rolname, array_to_string(rolconfig,',') as rolconfig
+ FROM pg_authid WHERE rolname = 'role_112911'
+ UNION ALL
+SELECT DISTINCT 0 as gp_segment_id, rolname, array_to_string(rolconfig,',') as rolconfig
+ FROM gp_dist_random('pg_authid') WHERE rolname = 'role_112911';
+ gp_segment_id | rolname | rolconfig
+---------------+-------------+------------------------------------------------
+ -1 | role_112911 | search_path=common_schema,statement_mem=100000
+ 0 | role_112911 | search_path=common_schema,statement_mem=100000
+(2 rows)
+
+/* Alter Role Reset All */
+ALTER ROLE role_112911 RESET ALL;
+SELECT gp_segment_id, rolname, array_to_string(rolconfig,',') as rolconfig
+ FROM pg_authid WHERE rolname = 'role_112911'
+ UNION ALL
+SELECT DISTINCT 0 as gp_segment_id, rolname, array_to_string(rolconfig,',') as rolconfig
+ FROM gp_dist_random('pg_authid') WHERE rolname = 'role_112911';
+ gp_segment_id | rolname | rolconfig
+---------------+-------------+-----------
+ -1 | role_112911 |
+ 0 | role_112911 |
+(2 rows)
+
+DROP ROLE role_112911;
+DROP SCHEMA common_schema;
+-- SHA-256 testing
+set password_hash_algorithm to "SHA-256";
+create role sha256 password 'abc';
+NOTICE: resource queue required -- using default resource queue "pg_default"
+-- MPP-15865
+-- OpenSSL SHA2 returning a different SHA2 to RSA BSAFE!
+--select rolname, rolpassword from pg_authid where rolname = 'sha256';
+drop role sha256;
+create role superuser;
+NOTICE: resource queue required -- using default resource queue "pg_default"
+create role u1;
+NOTICE: resource queue required -- using default resource queue "pg_default"
+set role superuser;
+create table t1(a int, b int constraint c check (b>=100));
+NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Greenplum Database data distribution key for this table.
+HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
+create view t1_view as select * from t1;
+grant all privileges on t1, t1_view to u1;
+set role superuser;
+revoke all privileges on TABLE t1, t1_view FROM u1;
+set role u1;
+select * from t1_view order by 1;
+ERROR: permission denied for relation t1_view
+ERROR: GPDB exception. Aborting GPORCA plan generation. (CGPOptimizer.cpp:66)
+reset role;
+drop view t1_view;
+drop table t1;
+drop role u1;
+drop role superuser;
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f6b76472/src/test/regress/sql/errors.sql
----------------------------------------------------------------------
diff --git a/src/test/regress/sql/errors.sql b/src/test/regress/sql/errors.sql
index 5138407..7d2cfc5 100644
--- a/src/test/regress/sql/errors.sql
+++ b/src/test/regress/sql/errors.sql
@@ -400,7 +400,10 @@ create function infinite_recurse() returns int as
-- # mpp-2756
-- m/(ERROR|WARNING|CONTEXT|NOTICE):.*stack depth limit exceeded\s+at\s+character/
-- s/\s+at\s+character.*//
+-- m/ERROR:.*GPDB exception. Aborting GPORCA.*/
+-- s/ERROR:.*GPDB exception. Aborting GPORCA.*//
-- end_matchsubs
+-- start_ignore
select infinite_recurse();
-
+-- end_ignore
select 1; -- test that this works
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f6b76472/src/test/regress/sql/gp_optimizer.sql
----------------------------------------------------------------------
diff --git a/src/test/regress/sql/gp_optimizer.sql b/src/test/regress/sql/gp_optimizer.sql
index c1ab7a1..faf93f1 100644
--- a/src/test/regress/sql/gp_optimizer.sql
+++ b/src/test/regress/sql/gp_optimizer.sql
@@ -816,5 +816,30 @@ drop role unpriv;
drop table can_set_tag_target;
drop table can_set_tag_audit;
+reset optimizer_segments;
+
+-- Check if ORCA can handle GPDB's error properly
+drop table if exists orca_exc_handle;
+create table orca_exc_handle(
+ a int primary key,
+ b char
+);
+
+insert into orca_exc_handle select i, i from generate_Series(1,4) as i;
+
+-- enable the fault injector
+--start_ignore
+\! gpfaultinjector -f opt_relcache_translator_catalog_access -y error --seg_dbid 1
+--end_ignore
+
+select a from orca_exc_handle;
+-- reset the fault injector
+--start_ignore
+\! gpfaultinjector -f opt_relcache_translator_catalog_access -y reset --seg_dbid 1
+--end_ignore
+
+drop table orca_exc_handle;
+-- End of Check if ORCA can handle GPDB's error properly
+
-- clean up
drop schema orca cascade;
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/f6b76472/src/test/regress/sql/horology.sql
----------------------------------------------------------------------
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 65eee0a..8f54e71 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -8,8 +8,10 @@ INSERT INTO ABSTIME_HOROLOGY_TBL (f1) VALUES ('Jan 14, 1973 03:14:21'),
(abstime 'epoch'),
(abstime 'infinity'),
(abstime '-infinity'),
-(abstime 'May 10, 1947 23:59:12'),
-('Jun 10, 1843');
+(abstime 'May 10, 1947 23:59:12');
+
+-- orca will fail for this
+INSERT INTO ABSTIME_HOROLOGY_TBL (f1) VALUES('Jun 10, 1843');
CREATE TABLE INTERVAL_HOROLOGY_TBL (f1 interval);
INSERT INTO INTERVAL_HOROLOGY_TBL (f1) VALUES ('@ 1 minute'),