COPY command that need superuser privileges in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

gpadmin=> copy xxxx to '/tmp/test.out';

ERROR:  must be superuser to COPY to or from a file

HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
 

 

Environment

Product Version: 5.21

Resolution

You can use \copy (psql command) to perform copy.

It will copy out to a file as gpadmin and based on OS level permissions for gpadmin, it might not have permission to perform the \copy. Make sure that unix user gpadmin has access to the output directory.
 

$ psql -U xxxx
psql (8.3.23)
Type "help" for help.
gpadmin=>create table yyy (field1 smallint) distributed by (field1);
CREATE TABLE
gpadmin=> insert into yyy values(1),(2);
INSERT 0 2

gpadmin=> copy yyy to '/tmp/yyy.out';
ERROR: must be superuser to COPY to or from a file
HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

gpadmin=> \copy yyy to '/tmp/yyy.out';
gpadmin=> \!more /tmp/yyy.out
1
2
gpadmin=> \!ls -ltr /tmp/yyy.out
-rw-rw-r--. 1 gpadmin gpadmin 4 Jun 16 17:52 /tmp/yyy.out

----- Directory permission not granted to unix user gpadmin

gpadmin=> \copy yyy to '/yyy.out';
/yyy.out: Permission denied