Creating an external web table in Pivotal HDB fails with "ERROR: must be superuser to create an EXECUTE external web table"

Products

• Services Suite

Issue/Introduction

Symptoms:

Environment

Product	Version
Pivotal HDB	1.x / 2.0.x
Greenplum	4.3.x

Symptom

When trying to create an external web table with the EXECUTE option, the following message is seen:
 

gpadmin=> CREATE EXTERNAL WEB TABLE log_output (linenum int, message text) EXECUTE '/var/load_scripts/get_log_data.sh' ON HOST FORMAT 'TEXT' (DELIMITER '|');
ERROR: must be superuser to create an EXECUTE external web table
gpadmin=>

 

Environment

Cause

Cause

As scripts included in the EXECUTE command will be run as "gpadmin" on the Pivotal HDB hosts and user "gpadmin" has elevated privileges, there is a security risk with using the EXECUTE external web table so that ONLY superusers can create EXECUTE external web tables.

To confirm that the current user is not a superuser, the following command can be used:
 

gpadmin=> select current_user;
 current_user
--------------
 adam
(1 row)

gpadmin=> show is_superuser; 
is_superuser -------------- 
off 
(1 row)

 

Resolution

Resolution

1. Confirm that the EXECUTE for Web or Writable External Tables is not disabled.

2. Either log on as a superuser to create the external Web table OR grant superuser permissions to the user:
 

gpadmin=# set role gpadmin;
SET
gpadmin=# alter role adam superuser; 
ALTER ROLE
gpadmin=# set role adam;
SET
gpadmin=# drop external table log_output ;
DROP EXTERNAL TABLE
gpadmin=# CREATE EXTERNAL WEB TABLE log_output (linenum int, message text) EXECUTE '/var/load_scripts/get_log_data.sh' ON HOST FORMAT 'TEXT' (DELIMITER '|');
CREATE EXTERNAL TABLE
gpadmin=#