Attempt to create an external table by non-superuser leads to "ERROR: permission denied"

Products

• VMware Tanzu Greenplum

Issue/Introduction

When a non-superuser is attempting to create an external table, the error message "ERROR: permission denied" is produced:

[gpadmin@mdw faisal]$ psql -U a1
Password for user a1:
psql (8.2.15)
Type "help" for help.

flightdata=>  CREATE EXTERNAL TABLE ext_expenses ( name text,
flightdata(>       date date, amount float4, category text, desc1 text )
flightdata->       LOCATION ('gpfdist://etlhost-1:8081/*',
flightdata(>                 'gpfdist://etlhost-1:8082/*')
flightdata->       FORMAT 'TEXT' (DELIMITER '|');
ERROR:  permission denied: no privilege to create a readable gpfdist(s) external table

Cause

The non-superuser does not have the necessary permission to create the external table.

Resolution

Assign the CREATEEXTTABLE permission to the user. Attempt to create the external table again:

alter user <user-name> CREATEEXTTABLE;