src/test/isolation2/output/parallel_retrieve_cursor/security.source

-- @Description Tests retrieve session functionality restriction for security.
--
DROP TABLE IF EXISTS t1;
DROP
CREATE TABLE t1 (a INT) DISTRIBUTED by (a);
CREATE
insert into t1 select generate_series(1,100);
INSERT 100

CREATE OR REPLACE FUNCTION myappend(anyarray, anyelement) RETURNS anyarray AS $$ SELECT $1 || $2 $$ LANGUAGE SQL;
CREATE

-- Test: Retrieve login without valid token.
1: @pre_run 'export RETRIEVE_TOKEN="123" ; echo $RAW_STR' : SELECT 1;
 ?column? 
----------
 1        
(1 row)
0R: SELECT 1;
#0retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid

0Rq:Sessions not started cannot be quit

-- Test: Declare a cursor
1: BEGIN;
BEGIN
1: DECLARE c1 PARALLEL RETRIEVE CURSOR FOR SELECT * FROM t1;
DECLARE
1: @post_run 'parse_endpoint_info 1 1 2 3 4' : SELECT endpointname,auth_token,hostname,port,state FROM gp_get_endpoints() WHERE cursorname='c1';
 endpoint_id1 | token_id | host_id | port_id | READY
 endpoint_id1 | token_id | host_id | port_id | READY
 endpoint_id1 | token_id | host_id | port_id | READY
(3 rows)

-- Test: Should not change gp_role in retrieve mode
0R: set gp_role to 'utility';
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.

-- Test: limit all statement which is supported in retrieve session
0R: show gp_role;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.

0R: begin;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.
0R: declare c1 cursor for select * from t1;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.
0R: fetch 5 from c1;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.
0R: rollback;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.

0R: delete FROM t1;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.
0R: insert into t1 select generate_series(100,110);
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.
0R: update t1 set a=a+100 where a<10;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.
0R: truncate table t1;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.
0R: select * from t1;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.

0R: CREATE TABLE t10 (a INT) DISTRIBUTED by (a);
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.
0R: DROP TABLE t1;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.

-- copy ... on program ... is also disallowed by this retrieve session.
0R: COPY t1 to '/tmp/1.cvs';
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.

-- Test: builtin function can not be allowed
0R: select '12'::int;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.
-- Test: UDF can not be allowed
0R: SELECT myappend(ARRAY[42,6], 21);
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.
-- Test: Create UDF can not allowed
0R: CREATE OR REPLACE FUNCTION myappend1(anyarray, anyelement) RETURNS anyarray AS $$ SELECT $1 || $2 $$ LANGUAGE SQL;
ERROR:  This is a retrieve connection, but the query is not a RETRIEVE.

-- Test: Different illegal endpoints always lead to an error
---- invalid endpoints
1R: RETRIEVE ALL FROM ENDPOINT abc;
ERROR:  the endpoint abc does not exist for session id xxx
1R: RETRIEVE ALL FROM ENDPOINT 123;
ERROR:  syntax error at or near "123"
LINE 1: RETRIEVE ALL FROM ENDPOINT 123;
                                   ^
1R: RETRIEVE ALL FROM ENDPOINT tk1122;
ERROR:  the endpoint tk1122 does not exist for session id xxx
1R: RETRIEVE ALL FROM ENDPOINT tktt223344556677889900112233445566;
ERROR:  the endpoint tktt223344556677889900112233445566 does not exist for session id xxx

-- Retrieve data.
*R: @pre_run 'set_endpoint_variable @ENDPOINT1': RETRIEVE 10 FROM ENDPOINT "@ENDPOINT1";
#-1retrieve> connection to server at "host_id", port port_id failed: FATAL:  retrieve auth token is invalid


 a  
----
 16 
 18 
 19 
 2  
 22 
 24 
 3  
 4  
 7  
 8  
(10 rows)

 a  
----
 1  
 12 
 15 
 20 
 23 
 26 
 30 
 31 
 35 
 36 
(10 rows)

 a  
----
 10 
 11 
 13 
 14 
 17 
 21 
 25 
 5  
 6  
 9  
(10 rows)