S3 : regular user to use external tables

Products

• VMware Tanzu Greenplum

Issue/Introduction

Based on "Configuring the s3 Protocol:"
source: https://techdocs.broadcom.com/us/en/vmware-tanzu/data-solutions/tanzu-greenplum/5/greenplum-database/admin_guide-external-g-s3-protocol.html

 

When non superuser is trying to use external table on S3:

create WRITABLE EXTERNAL TABLE public.test_write (like public.my_table)
LOCATION('s3://myhost.com:9020/gpdev/dataset4/normal/tables config=/home/gpadmin/s3/s3.conf')
FORMAT 'csv';

Error:
ERROR [42501] ERROR: permission denied for external protocol S3;

If you try to grant him rights:

prod=# grant all on protocol S3 to "my_user";
ERROR: protocol "s3" is not trusted
HINT: Only superusers may use untrusted protocols.

 

 

Environment

Product Version: 6.19

Resolution

When you create a new protocol it is by default untrusted. You have to specify it is trusted if you want non super users to use it.

DROP PROTOCOL s3
CREATE TRUSTED  PROTOCOL s3 (writefunc = write_to_s3, readfunc = read_from_s3);

If you already have some external tables depending on s3, the above will fail with 

dedwh=# DROP PROTOCOL s3
dedwh-# ;
ERROR: cannot drop protocol s3 because other objects depend on it
DETAIL: external table s3writ depends on protocol s3
external table s3write depends on protocol s3
(...)

In order to recreate S3 you have to drop all external tables that depend on it
Once the protocol is trusted, grant rights to non super user and the user can create external tables with the protocol.
 

prod=# alter user "my_user" CREATEEXTTABLE (type='writable');
ALTER ROLE
prod=# grant all on protocol S3 to "my_user";
GRANT
prod=#