Pivotal Knowledge Base

Follow

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

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=>

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

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=#

 

 

Comments

Powered by Zendesk