Repository: incubator-hawq Updated Branches: refs/heads/master 31aff875c -> 35ed3ad38
HAWQ-1312. Forbid partial grant/revoke command in HAWQ side once Ranger is configured. Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/35ed3ad3 Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/35ed3ad3 Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/35ed3ad3 Branch: refs/heads/master Commit: 35ed3ad38c7ad0207f4e594fdbcdf4e324449c38 Parents: 31aff87 Author: Wen Lin <[email protected]> Authored: Wed Feb 8 10:05:56 2017 +0800 Committer: Wen Lin <[email protected]> Committed: Wed Feb 8 10:05:56 2017 +0800 ---------------------------------------------------------------------- src/backend/catalog/aclchk.c | 41 + src/test/feature/UDF/TestUDF.cpp | 24 +- .../UDF/ans/function_basics.ranger.ans.orca | 1088 ++++++++++++++++++ .../UDF/ans/function_basics.ranger.ans.planner | 1076 +++++++++++++++++ 4 files changed, 2225 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/35ed3ad3/src/backend/catalog/aclchk.c ---------------------------------------------------------------------- diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index 667aa61..3ab3248 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -285,6 +285,31 @@ restrict_and_check_grant(bool is_grant, AclMode avail_goptions, bool all_privs, } /* + * Check if this object's ACL is checked natively. + */ +bool checkACLNative(GrantObjectType type, Oid oid) +{ + AclObjectKind kind = MAX_ACL_KIND; + if (type == ACL_OBJECT_RELATION) + { + kind = ACL_KIND_CLASS; + } + else if (type == ACL_OBJECT_NAMESPACE) + { + kind = ACL_KIND_NAMESPACE; + } + else if (type == ACL_OBJECT_FUNCTION) + { + kind = ACL_KIND_PROC; + } + else + { + return false; + } + return fallBackToNativeCheck(kind, oid, GetUserId()); +} + +/* * Called to execute the utility commands GRANT and REVOKE */ void @@ -304,6 +329,22 @@ ExecuteGrantStmt(GrantStmt *stmt) istmt.objtype = stmt->objtype; istmt.objects = objectNamesToOids(stmt->objtype, stmt->objects); + /* + * Don't allow GRANT/REVOKE for objects managed by Ranger + * if in ranger mode. + */ + if (aclType == HAWQ_ACL_RANGER) + { + foreach(cell, istmt.objects) + { + Oid oid = lfirst_oid(cell); + if (!checkACLNative(stmt->objtype, oid)) + { + elog(ERROR, "GRANT/REVOKE is not allowed for this object in ranger mode"); + } + } + } + /* If this is a GRANT/REVOKE on a table, expand partition references */ if (istmt.objtype == ACL_OBJECT_RELATION) { http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/35ed3ad3/src/test/feature/UDF/TestUDF.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/TestUDF.cpp b/src/test/feature/UDF/TestUDF.cpp index 7107b63..95509fb 100755 --- a/src/test/feature/UDF/TestUDF.cpp +++ b/src/test/feature/UDF/TestUDF.cpp @@ -18,13 +18,29 @@ TEST_F(TestUDF, TestUDFBasics) hawq::test::SQLUtility util; if (util.getGUCValue("optimizer") == "on") { - util.execSQLFile("UDF/sql/function_basics.sql", - "UDF/ans/function_basics.ans.orca"); + if (util.getGUCValue("hawq_acl_type") == "standalone") + { + util.execSQLFile("UDF/sql/function_basics.sql", + "UDF/ans/function_basics.ans.orca"); + } + else + { + util.execSQLFile("UDF/sql/function_basics.sql", + "UDF/ans/function_basics.ranger.ans.orca"); + } } else { - util.execSQLFile("UDF/sql/function_basics.sql", - "UDF/ans/function_basics.ans.planner"); + if (util.getGUCValue("hawq_acl_type") == "standalone") + { + util.execSQLFile("UDF/sql/function_basics.sql", + "UDF/ans/function_basics.ans.planner"); + } + else + { + util.execSQLFile("UDF/sql/function_basics.sql", + "UDF/ans/function_basics.ranger.ans.planner"); + } } } http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/35ed3ad3/src/test/feature/UDF/ans/function_basics.ranger.ans.orca ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/ans/function_basics.ranger.ans.orca b/src/test/feature/UDF/ans/function_basics.ranger.ans.orca new file mode 100644 index 0000000..5523093 --- /dev/null +++ b/src/test/feature/UDF/ans/function_basics.ranger.ans.orca @@ -0,0 +1,1088 @@ +-- start_ignore +SET SEARCH_PATH=TestUDF_TestUDFBasics; +SET +-- end_ignore +-- SETUP +DROP TABLE IF EXISTS foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:5: NOTICE: table "foo" does not exist, skipping +DROP TABLE +CREATE TABLE foo AS SELECT * FROM generate_series(1, 10) x; +SELECT 10 +CREATE FUNCTION f(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +-- DDL, CREATE FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proname FROM pg_proc WHERE proname = 'g'; + proname +--------- + g +(1 row) + +SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + proname +--------- +(0 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, CREATE OR REPLACE FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proname FROM pg_proc WHERE proname = 'g'; + proname +--------- + g +(1 row) + +SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + proname +--------- +(0 rows) + +CREATE OR REPLACE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN (-1) * x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT proname, prosrc FROM pg_proc WHERE proname = 'g'; + proname | prosrc +---------+------------------ + g | + : BEGIN + : RETURN (-1) * x; + : END + : +(1 row) + +SELECT proname, prosrc FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + proname | prosrc +---------+-------- +(0 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, DROP FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +DROP FUNCTION g(int); +DROP FUNCTION +SELECT oid, proname FROM pg_proc WHERE proname = 'g'; + oid | proname +-----+--------- +(0 rows) + +SELECT oid, proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + oid | proname +-----+--------- +(0 rows) + +-- DDL, DROP FUNCTION, NEGATIVE +DROP FUNCTION g(int); +psql:/tmp/TestUDF_TestUDFBasics.sql:47: ERROR: function g(integer) does not exist +-- DDL, CREATE FUNCTION, RECORD +CREATE FUNCTION foo(int) RETURNS record AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; +CREATE FUNCTION +SELECT foo(5); + foo +----- + (5) +(1 row) + +DROP FUNCTION foo(int); +DROP FUNCTION +CREATE FUNCTION foo(int) RETURNS foo AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; +CREATE FUNCTION +SELECT foo(5); + foo +----- + (5) +(1 row) + +DROP FUNCTION foo(int); +DROP FUNCTION +-- DDL, CREATE FUNCTION, SRF +CREATE FUNCTION g(x setof int) RETURNS INT + AS $$ SELECT 1 $$ LANGUAGE SQL; +CREATE FUNCTION +DROP FUNCTION g(setof int); +DROP FUNCTION +CREATE FUNCTION g() RETURNS setof INT + AS $$ SELECT 1 $$ LANGUAGE SQL; +CREATE FUNCTION +DROP FUNCTION g(); +DROP FUNCTION +-- DDL, CREATE FUNCTION, TABLE, NEGATIVE +CREATE FUNCTION g() RETURNS TABLE(x int) + AS $$ SELECT * FROM foo $$ LANGUAGE SQL; +CREATE FUNCTION +DROP FUNCTION g(); +DROP FUNCTION +CREATE FUNCTION g(anytable) RETURNS int + AS 'does_not_exist', 'does_not_exist' LANGUAGE C; +psql:/tmp/TestUDF_TestUDFBasics.sql:76: ERROR: TABLE functions not supported +-- DDL, CREATE FUNCTION, SECURITY DEFINER +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE SECURITY DEFINER; +CREATE FUNCTION +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, ALTER FUNCTION +-- DDL, STRICT +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT g(NULL); + g +--- + 1 +(1 row) + +ALTER FUNCTION g(int) STRICT; +ALTER FUNCTION +SELECT g(NULL); + g +--- + +(1 row) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, ALTER FUNCTION, OWNER +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:97: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +SET ROLE superuser; +SET +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; + rolname +----------- + superuser +(1 row) + +ALTER FUNCTION g(int) OWNER TO u1; +ALTER FUNCTION +SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; + rolname +--------- + u1 +(1 row) + +DROP FUNCTION g(int); +DROP FUNCTION +RESET ROLE; +RESET +DROP ROLE u1; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DDL, ALTER FUNCTION, RENAME +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT g(0); + g +--- + 1 +(1 row) + +ALTER FUNCTION g(int) RENAME TO h; +ALTER FUNCTION +SELECT h(0); + h +--- + 1 +(1 row) + +DROP FUNCTION h(int); +DROP FUNCTION +-- DDL, ALTER FUNCTION, SET SCHEMA +CREATE SCHEMA bar; +CREATE SCHEMA +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT g(0); + g +--- + 1 +(1 row) + +ALTER FUNCTION g(int) SET SCHEMA bar; +ALTER FUNCTION +SELECT bar.g(0); + g +--- + 1 +(1 row) + +DROP SCHEMA bar CASCADE; +psql:/tmp/TestUDF_TestUDFBasics.sql:125: NOTICE: drop cascades to function bar.g(integer) +DROP SCHEMA +-- DDL, ALTER FUNCTION, SECURITY DEFINER +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +ALTER FUNCTION g(int) SECURITY DEFINER; +ALTER FUNCTION +DROP FUNCTION g(int); +DROP FUNCTION +-- DCL, GRANT/REVOKE +-- GRANT { EXECUTE | ALL [ PRIVILEGES ] } +-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] +-- TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] +-- REVOKE [ GRANT OPTION FOR ] +-- { EXECUTE | ALL [ PRIVILEGES ] } +-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] +-- FROM { username | GROUP groupname | PUBLIC } [, ...] +-- [ CASCADE | RESTRICT ] +-- DCL, GRANT/REVOKE, EXECUTE +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:149: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:150: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:153: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:158: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:159: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT EXECUTE ON FUNCTION g(int) TO u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:161: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:164: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:165: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE EXECUTE ON FUNCTION g(int) FROM u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:167: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:170: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:171: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:174: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +DROP ROLE u1; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DCL, GRANT/REVOKE, PUBLIC +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:183: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:184: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:187: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:192: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:193: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT EXECUTE ON FUNCTION g(int) TO PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:195: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:198: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:199: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE EXECUTE ON FUNCTION g(int) FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:201: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:204: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:205: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:208: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +DROP ROLE u1; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DCL, GRANT/REVOKE, Groups +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:217: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +CREATE ROLE u2 IN GROUP u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:218: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:219: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:222: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:227: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:228: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT EXECUTE ON FUNCTION g(int) TO u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:230: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:233: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:234: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE EXECUTE ON FUNCTION g(int) FROM u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:236: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:239: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:240: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:243: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +DROP ROLE u1; +DROP ROLE +DROP ROLE u2; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DCL, GRANT/REVOKE, WITH GRANT OPTION +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:253: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +CREATE ROLE u2; +psql:/tmp/TestUDF_TestUDFBasics.sql:254: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:255: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:258: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:263: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:264: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT ALL ON FUNCTION g(int) TO u1 WITH GRANT OPTION; +psql:/tmp/TestUDF_TestUDFBasics.sql:266: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SET ROLE u1; +SET +GRANT ALL ON FUNCTION g(int) TO u2; +psql:/tmp/TestUDF_TestUDFBasics.sql:268: ERROR: function g(integer) does not exist +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:271: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:272: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:274: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:275: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE ALL ON FUNCTION g(int) FROM u1 CASCADE; +psql:/tmp/TestUDF_TestUDFBasics.sql:277: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:280: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:281: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:283: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:284: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:287: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +DROP ROLE u1; +DROP ROLE +DROP ROLE u2; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DML, CaseExpr +SELECT CASE WHEN x % 2 = 0 THEN f(x) ELSE 0 END FROM foo ORDER BY x; + case +------ + 0 + 2 + 0 + 4 + 0 + 6 + 0 + 8 + 0 + 10 +(10 rows) + +-- DML, OpExpr +SELECT f(x) + f(x) FROM foo ORDER BY x; + ?column? +---------- + 2 + 4 + 6 + 8 + 10 + 12 + 14 + 16 + 18 + 20 +(10 rows) + +SELECT f(x) + f(x) + f(x) FROM foo ORDER BY x; + ?column? +---------- + 3 + 6 + 9 + 12 + 15 + 18 + 21 + 24 + 27 + 30 +(10 rows) + +SELECT f(x) + f(x) - f(x) FROM foo ORDER BY x; + ?column? +---------- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, FuncExpr +CREATE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT g(f(x)) FROM foo ORDER BY x; + g +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DML, BoolExpr +SELECT x % 2 = 0 AND f(x) % 2 = 1 FROM foo ORDER BY x; + ?column? +---------- + f + f + f + f + f + f + f + f + f + f +(10 rows) + +-- DML, DistinctExpr +SELECT x IS DISTINCT FROM f(x) from foo ORDER BY x; + ?column? +---------- + f + f + f + f + f + f + f + f + f + f +(10 rows) + +-- DML, PercentileExpr +SELECT MEDIAN(f(x)) FROM foo; + median +-------- + 5.5 +(1 row) + +-- DML, Complex Expression +CREATE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT CASE + WHEN x % 2 = 0 THEN g(g(x)) + g(g(x)) + WHEN f(x) % 2 = 1 THEN g(g(x)) - g(g(x)) + END FROM foo ORDER BY x; + case +------ + 0 + 4 + 0 + 8 + 0 + 12 + 0 + 16 + 0 + 20 +(10 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DML, Qual +SELECT x FROM foo WHERE f(x) % 2 = 0 ORDER BY x; + x +---- + 2 + 4 + 6 + 8 + 10 +(5 rows) + +-- DML, FROM +SELECT * FROM f(5); + f +--- + 5 +(1 row) + +-- DML, Grouping +SELECT DISTINCT f(x) FROM foo ORDER BY f(x); + f +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +SELECT f(x) FROM foo GROUP BY f(x) ORDER BY f(x); + f +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, Join +SELECT a.x FROM foo a, foo b WHERE f(a.x) = f(b.x) ORDER BY x; + x +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +SELECT a.x FROM foo a JOIN foo b ON f(a.x) = f(b.x) ORDER BY x; + x +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, Windowing +SELECT avg(x) OVER (PARTITION BY f(x)) FROM foo ORDER BY x; + avg +----- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, CTE +WITH t AS (SELECT x from foo) + SELECT f(x) from t ORDER BY x; + f +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, InitPlan +SELECT UNNEST(ARRAY(SELECT x FROM foo)) ORDER BY 1; + unnest +-------- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +SELECT UNNEST(ARRAY(SELECT f(1))); + unnest +-------- + 1 +(1 row) + +-- PROPERTIES, VOLATILITY, IMMUTABLE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; + ?column? +---------- + f +(1 row) + +DROP FUNCTION g(); +DROP FUNCTION +-- PROPERTIES, VOLATILITY, STABLE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL STABLE; +CREATE FUNCTION +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; + ?column? +---------- + f +(1 row) + +DROP FUNCTION g(); +DROP FUNCTION +-- PROPERTIES, VOLATILITY, VOLATILE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL VOLATILE; +CREATE FUNCTION +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; + ?column? +---------- + t +(1 row) + +DROP FUNCTION g(); +DROP FUNCTION +----------------- +-- NEGATIVE TESTS +----------------- +SELECT h(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:405: ERROR: function h(integer) does not exist +LINE 1: SELECT h(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +-- DML, InitPlan +SELECT UNNEST(ARRAY(SELECT f(x) from foo)); + unnest +-------- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- LANGUAGES not yet supported +-- CREATE LANGUAGE plr; +-- CREATE LANGUAGE plpython; +-- CREATE LANGUAGE pljava; +-- CREATE LANGUAGE plperl; +-- NESTED FUNCTION +CREATE FUNCTION inner(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +CREATE FUNCTION outer(x INT) RETURNS INT AS $$ +BEGIN +RETURN inner(x); +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT outer(0); + outer +------- + 1 +(1 row) + +SELECT outer(0) FROM foo; + outer +------- + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 + 1 +(10 rows) + +DROP FUNCTION outer(int); +DROP FUNCTION +DROP FUNCTION inner(int); +DROP FUNCTION +-- TEARDOWN +DROP TABLE foo; +DROP TABLE +-- HAWQ-510 +drop table if exists testEntryDB; +psql:/tmp/TestUDF_TestUDFBasics.sql:435: NOTICE: table "testentrydb" does not exist, skipping +DROP TABLE +create table testEntryDB(key int, value int) distributed randomly; +CREATE TABLE +insert into testEntryDB values(1, 0); +INSERT 0 1 +select t2.key, t2.value +from (select key, value from testEntryDB where value = 0) as t1, + (select generate_series(1,2)::int as key, 0::int as value) as t2 +where t1.value=t2.value; + key | value +-----+------- + 1 | 0 + 2 | 0 +(2 rows) + +drop table testEntryDB; +DROP TABLE http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/35ed3ad3/src/test/feature/UDF/ans/function_basics.ranger.ans.planner ---------------------------------------------------------------------- diff --git a/src/test/feature/UDF/ans/function_basics.ranger.ans.planner b/src/test/feature/UDF/ans/function_basics.ranger.ans.planner new file mode 100644 index 0000000..818f424 --- /dev/null +++ b/src/test/feature/UDF/ans/function_basics.ranger.ans.planner @@ -0,0 +1,1076 @@ +-- start_ignore +SET SEARCH_PATH=TestUDF_TestUDFBasics; +SET +-- end_ignore +-- SETUP +DROP TABLE IF EXISTS foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:5: NOTICE: table "foo" does not exist, skipping +DROP TABLE +CREATE TABLE foo AS SELECT * FROM generate_series(1, 10) x; +SELECT 10 +CREATE FUNCTION f(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +-- DDL, CREATE FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proname FROM pg_proc WHERE proname = 'g'; + proname +--------- + g +(1 row) + +SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + proname +--------- +(0 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, CREATE OR REPLACE FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proname FROM pg_proc WHERE proname = 'g'; + proname +--------- + g +(1 row) + +SELECT proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + proname +--------- +(0 rows) + +CREATE OR REPLACE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN (-1) * x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT proname, prosrc FROM pg_proc WHERE proname = 'g'; + proname | prosrc +---------+------------------ + g | + : BEGIN + : RETURN (-1) * x; + : END + : +(1 row) + +SELECT proname, prosrc FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + proname | prosrc +---------+-------- +(0 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, DROP FUNCTION +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +DROP FUNCTION g(int); +DROP FUNCTION +SELECT oid, proname FROM pg_proc WHERE proname = 'g'; + oid | proname +-----+--------- +(0 rows) + +SELECT oid, proname FROM gp_dist_random('pg_proc') WHERE proname = 'g'; + oid | proname +-----+--------- +(0 rows) + +-- DDL, DROP FUNCTION, NEGATIVE +DROP FUNCTION g(int); +psql:/tmp/TestUDF_TestUDFBasics.sql:47: ERROR: function g(integer) does not exist +-- DDL, CREATE FUNCTION, RECORD +CREATE FUNCTION foo(int) RETURNS record AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; +CREATE FUNCTION +SELECT foo(5); + foo +----- + (5) +(1 row) + +DROP FUNCTION foo(int); +DROP FUNCTION +CREATE FUNCTION foo(int) RETURNS foo AS 'SELECT * FROM foo WHERE x=$1' LANGUAGE SQL; +CREATE FUNCTION +SELECT foo(5); + foo +----- + (5) +(1 row) + +DROP FUNCTION foo(int); +DROP FUNCTION +-- DDL, CREATE FUNCTION, SRF +CREATE FUNCTION g(x setof int) RETURNS INT + AS $$ SELECT 1 $$ LANGUAGE SQL; +CREATE FUNCTION +DROP FUNCTION g(setof int); +DROP FUNCTION +CREATE FUNCTION g() RETURNS setof INT + AS $$ SELECT 1 $$ LANGUAGE SQL; +CREATE FUNCTION +DROP FUNCTION g(); +DROP FUNCTION +-- DDL, CREATE FUNCTION, TABLE, NEGATIVE +CREATE FUNCTION g() RETURNS TABLE(x int) + AS $$ SELECT * FROM foo $$ LANGUAGE SQL; +CREATE FUNCTION +DROP FUNCTION g(); +DROP FUNCTION +CREATE FUNCTION g(anytable) RETURNS int + AS 'does_not_exist', 'does_not_exist' LANGUAGE C; +psql:/tmp/TestUDF_TestUDFBasics.sql:76: ERROR: TABLE functions not supported +-- DDL, CREATE FUNCTION, SECURITY DEFINER +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE SECURITY DEFINER; +CREATE FUNCTION +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, ALTER FUNCTION +-- DDL, STRICT +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT g(NULL); + g +--- + 1 +(1 row) + +ALTER FUNCTION g(int) STRICT; +ALTER FUNCTION +SELECT g(NULL); + g +--- + +(1 row) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DDL, ALTER FUNCTION, OWNER +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:97: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +SET ROLE superuser; +SET +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; + rolname +----------- + superuser +(1 row) + +ALTER FUNCTION g(int) OWNER TO u1; +ALTER FUNCTION +SELECT a.rolname FROM pg_proc p, pg_authid a where p.proowner = a.oid and proname = 'g'; + rolname +--------- + u1 +(1 row) + +DROP FUNCTION g(int); +DROP FUNCTION +RESET ROLE; +RESET +DROP ROLE u1; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DDL, ALTER FUNCTION, RENAME +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT g(0); + g +--- + 1 +(1 row) + +ALTER FUNCTION g(int) RENAME TO h; +ALTER FUNCTION +SELECT h(0); + h +--- + 1 +(1 row) + +DROP FUNCTION h(int); +DROP FUNCTION +-- DDL, ALTER FUNCTION, SET SCHEMA +CREATE SCHEMA bar; +CREATE SCHEMA +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT g(0); + g +--- + 1 +(1 row) + +ALTER FUNCTION g(int) SET SCHEMA bar; +ALTER FUNCTION +SELECT bar.g(0); + g +--- + 1 +(1 row) + +DROP SCHEMA bar CASCADE; +psql:/tmp/TestUDF_TestUDFBasics.sql:125: NOTICE: drop cascades to function bar.g(integer) +DROP SCHEMA +-- DDL, ALTER FUNCTION, SECURITY DEFINER +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +ALTER FUNCTION g(int) SECURITY DEFINER; +ALTER FUNCTION +DROP FUNCTION g(int); +DROP FUNCTION +-- DCL, GRANT/REVOKE +-- GRANT { EXECUTE | ALL [ PRIVILEGES ] } +-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] +-- TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ] +-- REVOKE [ GRANT OPTION FOR ] +-- { EXECUTE | ALL [ PRIVILEGES ] } +-- ON FUNCTION funcname ( [ [ argmode ] [ argname ] argtype [, ...] ] ) [, ...] +-- FROM { username | GROUP groupname | PUBLIC } [, ...] +-- [ CASCADE | RESTRICT ] +-- DCL, GRANT/REVOKE, EXECUTE +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:149: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:150: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:153: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:158: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:159: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT EXECUTE ON FUNCTION g(int) TO u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:161: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:164: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:165: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE EXECUTE ON FUNCTION g(int) FROM u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:167: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:170: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:171: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:174: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +DROP ROLE u1; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DCL, GRANT/REVOKE, PUBLIC +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:183: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:184: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:187: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:192: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:193: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT EXECUTE ON FUNCTION g(int) TO PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:195: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:198: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:199: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE EXECUTE ON FUNCTION g(int) FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:201: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:204: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:205: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:208: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +DROP ROLE u1; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DCL, GRANT/REVOKE, Groups +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:217: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +CREATE ROLE u2 IN GROUP u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:218: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:219: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:222: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:227: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:228: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT EXECUTE ON FUNCTION g(int) TO u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:230: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:233: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:234: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE EXECUTE ON FUNCTION g(int) FROM u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:236: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:239: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:240: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:243: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +DROP ROLE u1; +DROP ROLE +DROP ROLE u2; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DCL, GRANT/REVOKE, WITH GRANT OPTION +CREATE ROLE superuser SUPERUSER; +CREATE ROLE +SET ROLE superuser; +SET +CREATE ROLE u1; +psql:/tmp/TestUDF_TestUDFBasics.sql:253: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +CREATE ROLE u2; +psql:/tmp/TestUDF_TestUDFBasics.sql:254: NOTICE: resource queue required -- using default resource queue "pg_default" +CREATE ROLE +GRANT SELECT ON TABLE foo TO PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:255: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +CREATE FUNCTION g(int) RETURNS INTEGER AS 'SELECT $1' LANGUAGE SQL IMMUTABLE STRICT; +CREATE FUNCTION +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +REVOKE ALL ON FUNCTION g(int) FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:258: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SELECT g(1); + g +--- + 1 +(1 row) + +SELECT count(g(x)) FROM foo; + count +------- + 10 +(1 row) + +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:263: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:264: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +GRANT ALL ON FUNCTION g(int) TO u1 WITH GRANT OPTION; +psql:/tmp/TestUDF_TestUDFBasics.sql:266: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SET ROLE u1; +SET +GRANT ALL ON FUNCTION g(int) TO u2; +psql:/tmp/TestUDF_TestUDFBasics.sql:268: ERROR: function g(integer) does not exist +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:271: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:272: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:274: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:275: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE superuser; +SET +REVOKE ALL ON FUNCTION g(int) FROM u1 CASCADE; +psql:/tmp/TestUDF_TestUDFBasics.sql:277: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +SELECT proacl FROM pg_proc where proname = 'g'; + proacl +-------- + +(1 row) + +SET ROLE u1; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:280: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:281: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +SET ROLE u2; +SET +SELECT g(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:283: ERROR: function g(integer) does not exist +LINE 1: SELECT g(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +SELECT count(g(x)) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:284: ERROR: relation "foo" does not exist +LINE 1: SELECT count(g(x)) FROM foo; + ^ +RESET ROLE; +RESET +DROP FUNCTION g(int); +DROP FUNCTION +REVOKE SELECT ON TABLE foo FROM PUBLIC; +psql:/tmp/TestUDF_TestUDFBasics.sql:287: ERROR: GRANT/REVOKE is not allowed for this object in ranger mode (aclchk.c:343) +DROP ROLE u1; +DROP ROLE +DROP ROLE u2; +DROP ROLE +DROP ROLE superuser; +DROP ROLE +-- DML, CaseExpr +SELECT CASE WHEN x % 2 = 0 THEN f(x) ELSE 0 END FROM foo ORDER BY x; + case +------ + 0 + 2 + 0 + 4 + 0 + 6 + 0 + 8 + 0 + 10 +(10 rows) + +-- DML, OpExpr +SELECT f(x) + f(x) FROM foo ORDER BY x; + ?column? +---------- + 2 + 4 + 6 + 8 + 10 + 12 + 14 + 16 + 18 + 20 +(10 rows) + +SELECT f(x) + f(x) + f(x) FROM foo ORDER BY x; + ?column? +---------- + 3 + 6 + 9 + 12 + 15 + 18 + 21 + 24 + 27 + 30 +(10 rows) + +SELECT f(x) + f(x) - f(x) FROM foo ORDER BY x; + ?column? +---------- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, FuncExpr +CREATE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT g(f(x)) FROM foo ORDER BY x; + g +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DML, BoolExpr +SELECT x % 2 = 0 AND f(x) % 2 = 1 FROM foo ORDER BY x; + ?column? +---------- + f + f + f + f + f + f + f + f + f + f +(10 rows) + +-- DML, DistinctExpr +SELECT x IS DISTINCT FROM f(x) from foo ORDER BY x; + ?column? +---------- + f + f + f + f + f + f + f + f + f + f +(10 rows) + +-- DML, PercentileExpr +SELECT MEDIAN(f(x)) FROM foo; + median +-------- + 5.5 +(1 row) + +-- DML, Complex Expression +CREATE FUNCTION g(x INT) RETURNS INT AS $$ +BEGIN +RETURN x; +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT CASE + WHEN x % 2 = 0 THEN g(g(x)) + g(g(x)) + WHEN f(x) % 2 = 1 THEN g(g(x)) - g(g(x)) + END FROM foo ORDER BY x; + case +------ + 0 + 4 + 0 + 8 + 0 + 12 + 0 + 16 + 0 + 20 +(10 rows) + +DROP FUNCTION g(int); +DROP FUNCTION +-- DML, Qual +SELECT x FROM foo WHERE f(x) % 2 = 0 ORDER BY x; + x +---- + 2 + 4 + 6 + 8 + 10 +(5 rows) + +-- DML, FROM +SELECT * FROM f(5); + f +--- + 5 +(1 row) + +-- DML, Grouping +SELECT DISTINCT f(x) FROM foo ORDER BY f(x); + f +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +SELECT f(x) FROM foo GROUP BY f(x) ORDER BY f(x); + f +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, Join +SELECT a.x FROM foo a, foo b WHERE f(a.x) = f(b.x) ORDER BY x; + x +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +SELECT a.x FROM foo a JOIN foo b ON f(a.x) = f(b.x) ORDER BY x; + x +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, Windowing +SELECT avg(x) OVER (PARTITION BY f(x)) FROM foo ORDER BY x; + avg +----- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, CTE +WITH t AS (SELECT x from foo) + SELECT f(x) from t ORDER BY x; + f +---- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- DML, InitPlan +SELECT UNNEST(ARRAY(SELECT x FROM foo)) ORDER BY 1; + unnest +-------- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +SELECT UNNEST(ARRAY(SELECT f(1))); + unnest +-------- + 1 +(1 row) + +-- PROPERTIES, VOLATILITY, IMMUTABLE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; + ?column? +---------- + f +(1 row) + +DROP FUNCTION g(); +DROP FUNCTION +-- PROPERTIES, VOLATILITY, STABLE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL STABLE; +CREATE FUNCTION +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; + ?column? +---------- + f +(1 row) + +DROP FUNCTION g(); +DROP FUNCTION +-- PROPERTIES, VOLATILITY, VOLATILE +CREATE FUNCTION g() RETURNS float AS 'SELECT random();' LANGUAGE SQL VOLATILE; +CREATE FUNCTION +SELECT COUNT(DISTINCT(g())) > 1 FROM foo; + ?column? +---------- + t +(1 row) + +DROP FUNCTION g(); +DROP FUNCTION +----------------- +-- NEGATIVE TESTS +----------------- +SELECT h(1); +psql:/tmp/TestUDF_TestUDFBasics.sql:405: ERROR: function h(integer) does not exist +LINE 1: SELECT h(1); + ^ +HINT: No function matches the given name and argument types. You may need to add explicit type casts. +-- DML, InitPlan +SELECT UNNEST(ARRAY(SELECT f(x) from foo)); + unnest +-------- + 1 + 2 + 3 + 4 + 5 + 6 + 7 + 8 + 9 + 10 +(10 rows) + +-- LANGUAGES not yet supported +-- CREATE LANGUAGE plr; +-- CREATE LANGUAGE plpython; +-- CREATE LANGUAGE pljava; +-- CREATE LANGUAGE plperl; +-- NESTED FUNCTION +CREATE FUNCTION inner(int) RETURNS INTEGER AS 'SELECT 1' LANGUAGE SQL IMMUTABLE; +CREATE FUNCTION +CREATE FUNCTION outer(x INT) RETURNS INT AS $$ +BEGIN +RETURN inner(x); +END +$$ LANGUAGE PLPGSQL; +CREATE FUNCTION +SELECT outer(0); + outer +------- + 1 +(1 row) + +SELECT outer(0) FROM foo; +psql:/tmp/TestUDF_TestUDFBasics.sql:423: ERROR: function inner(integer) does not exist +DETAIL: PL/pgSQL function "outer" line 2 at return +DROP FUNCTION outer(int); +DROP FUNCTION +DROP FUNCTION inner(int); +DROP FUNCTION +-- TEARDOWN +DROP TABLE foo; +DROP TABLE +-- HAWQ-510 +drop table if exists testEntryDB; +psql:/tmp/TestUDF_TestUDFBasics.sql:435: NOTICE: table "testentrydb" does not exist, skipping +DROP TABLE +create table testEntryDB(key int, value int) distributed randomly; +CREATE TABLE +insert into testEntryDB values(1, 0); +INSERT 0 1 +select t2.key, t2.value +from (select key, value from testEntryDB where value = 0) as t1, + (select generate_series(1,2)::int as key, 0::int as value) as t2 +where t1.value=t2.value; + key | value +-----+------- + 1 | 0 + 2 | 0 +(2 rows) + +drop table testEntryDB; +DROP TABLE
