Restricting File Protocol for Non-Superusers in Greenplum external table

Products

• VMware Tanzu Data
• VMware Tanzu Greenplum
• VMware Tanzu Greenplum / Gemfire
• VMware Tanzu Data Suite

Issue/Introduction

Non-superusers with CREATEEXTTABLE privileges cannot use the file:// protocol to access segment files. This built-in security measure prevents unauthorized access to sensitive system files like /etc/passwd.

Environment

A non-superuser attempting to create a `file://` external table will receive an error:

gpadmin=> CREATE EXTERNAL TABLE read_passwd (line text)
gpadmin-> LOCATION
gpadmin-> ('file://<segment_host1>:<port1>/etc/passwd', 'file://<segment_host2>:<port2>/etc/passwd')
gpadmin-> FORMAT 'text';
ERROR: must be superuser to create an external table with a file protocol

Cause

Greenplum classifies protocols as trusted or untrusted. The file:// protocol is untrusted and restricted to superusers because it accesses the host filesystem. The CREATEEXTTABLE privilege only enables the use of trusted protocols like gpfdist 

Resolution

To safely allow external table creation without compromising security:

1. Assign Standard Privileges: Grant the user the ability to use trusted protocols:

    ALTER ROLE <username> WITH CREATEEXTTABLE;

2. Use gpfdist: Direct users to use the `gpfdist` protocol for data loading, as it restricts access to a specific directory.
3. Maintain Non-Superuser Status: Do not grant superuser status to accounts that only require data loading capabilities.