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'),
