Pivotal Knowledge Base

Follow

gptransfer failed with "ERROR: must be superuser to create an EXECUTE external web table"

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.x
OS RHEL 6.x
Others  

Symptom

When attempting data migration between two databases with gptransfer, it failed with error message “ERROR:  must be superuser to create an EXECUTE external web table”.

gptransfer utility was started with below command.

gptransfer -f tablefile_partition_table_1  —dest-user=scott1 --source-user=qin --source-port=3000 --dest-port=3000 --work-base-dir=/data/scott/temp/transfer --truncate --dest-database=test2 -a -v

Error Message:

Note: These log entries have been seen when verbose mode of gptransfer is enabled with option "-v":

20160726:01:14:13:016703 gptransfer:mdw:gpadmin-[ERROR]:-Failed to transfer table ub.public.searches_weekly
20160726:01:14:13:016703 gptransfer:mdw:gpadmin-[INFO]:-Remaining 1 of 1 tables
20160726:01:14:13:016703 gptransfer:mdw:gpadmin-[DEBUG]:-[worker0] finished cmd: transfer of ub.public.searches_weekly cmdStr='None'
had result: cmd had rc=1 completed=False halted=False
stdout='error 'ERROR: must be superuser to create an EXECUTE external web table
' in 'CREATE WRITABLE EXTERNAL WEB TABLE gptransfer.w_ext_searches_weekly_24333159039ac045fdd23a678dd21d7a (LIKE "public"."searches_we
ekly")
EXECUTE 'cat > /data/scott/temp/suya/transfer/gptransfer_16703/ub.public.searches_weekly/ub.public.searches_weekly.
pipe.$GP_SEGMENT_ID'
FORMAT 'CSV'
(DELIMITER AS ',' QUOTE AS E'^A') ENCODING 'UTF8' DISTRIBUTED BY (hw_id)''
stderr='None'
20160726:01:14:13:016703 gptransfer:mdw:gpadmin-[WARNING]:-1 tables failed to transfer. A list of these tables

Cause

As shown in logs gptransfer will try to create a writable external table with EXECUTE protocol in the source database, SUPERUSER role is required for the user, which gptransfer will use to connect source database. You need to check option "--source-user" if it's specified or it will be gpadmin by default.

Following is an excerpt from GPDB Adminitrator Guide about this requirement.

For a role with the CREATEEXTTABLE attribute, the default external table type is readable 
and the default protocol is gpfdist. Note that external tables that use the file or execute
protocols can only be created by superusers.

Resolution

Grant SUPERUSER role to the user that is specified with "--source-user" option in gptransfer.

ub=# alter role qin superuser;
ALTER ROLE

Additional Information 

 

Comments

Powered by Zendesk