Error: "permission denied: no privilege to create a writable gpfdist(s) external table"

Products

• VMware Tanzu Greenplum

Issue/Introduction

How can you solve error: "permission denied: no privilege to create a writable gpfdist(s) external table".

For READABLE external table privilege granting, see Attempt to create an external table by non-superuser leads to "ERROR: permission denied".

Environment

Product Version: 6.25

Cause

The error message indicates you do not have the privilege to create WRITABLE external table.

Resolution

Grant writable external table creation privileges to the user using the syntax such as:

alter user <user_name> createexttable(type='writable');

Below is an example:

gpadmin=> \c
You are now connected to database "gpadmin" as user "user1".

gpadmin=> CREATE WRITABLE EXTERNAL table wext_test                                                                                                                                           
(
c1 text,
c2 varchar
)
LOCATION (
    'gpfdist://<ip:port>/test.txt'
)
FORMAT 'CSV' (DELIMITER '|' NULL ' ')
ENCODING 'UTF8';
ERROR:  permission denied: no privilege to create a writable gpfdist(s) external table
Time: 0.390 ms

gpadmin=# alter user user1 createexttable(type='writable');
ALTER ROLE

gpadmin=> CREATE WRITABLE EXTERNAL table wext_test
(
c1 text,
c2 varchar
)
LOCATION (
    'gpfdist://<ip:port>/test.txt'
)
FORMAT 'CSV' (DELIMITER '|' NULL ' ')
ENCODING 'UTF8';
CREATE EXTERNAL TABLE
Time: 7.915 ms