Permission denied on storage location when using copy as pseudosuperuser

Luc_Lecocq

Hi,

I am using Vertica 7, Enterprise Edition
I have a user to which I gave the pseudosuperuser role.
This user wants to use the COPY command to load the DB with csv files.
These files are on an NFS server, the directory on this server is NFS mounted by all nodes of my cluster, under the same name.
Initially, I did not create a storage location for this diectory, I just make sure that all my nodes are able to access in read mode to the files in this directory.

But all my COPY commands issued by this user fail with the same error, mentionned in the title of the POST.
My commands look like:

COPY MY_SCHEMA.MY_TABLE(COL1,COL2) FROM 'MY_DIR/MY_FILE' ON NODE v_db_node0001 DELIMITER ',' EXCEPTIONS 'MY_DIR/EXCEPTIONS' REJECTED_DATA 'MY_DIR/REJECTED_DATA'

Aparently, giving to my user the pseudosuperuser role is not sufficient to make it work.

But what else can I do ?
Thanks for helping.

Daniel_Leybovic

Hi!

Did you defined ADD_LOCATION for NFS? (mandatory with USAGE=USER)

If it will not help run separately GRANT on:

• database
• schema
• table
• location
  

and check for errors.

Luc_Lecocq

I did:
SELECT ADD_LOCATION('MY_DIR', '', 'USER');
GRANT ALL ON LOCATION 'MY_DIR' TO MY_PSEUDOSUPERUSER;


When I list the storage locations, it shows that the storage location exists on all nodes, with a location_usage set to USER. The directory itself is in rw mode for anyone, and the files I store are in read mode for anyone.

Could you give me more detail on what you ask me to do about the GRANT, not sure to understand what you meant

Daniel_Leybovic

Hi!

GRANT ALL on DATABASE 'dbname' to <ROLE> WITH GRANT OPTION;
GRANT ALL on SCHEMA 'schema' to <ROLE> WITH GRANT OPTION;
GRANT ALL on TABLE 'table' to <ROLE> WITH GRANT OPTION;
GRANT ALL on LOCATION 'path' to <ROLE> WITH GRANT OPTION;

Luc_Lecocq

OK, I found my problem, my user did not have the pseudosuperuser role, mistakes in my setup scripts, sorry for that.
Now I wonder, could I achieve the same result but without being pseudosuperuser, with the appropriate grant ?

Daniel_Leybovic

Hi!

Yes, you can achieve it (please review COPY LOCAL statement also).

Luc_Lecocq

Hi,

We do not want to use the COPY LOCAL for performance reasons.
In fact, it seems that being pseudosuperuser, you don't even need to define location storage, well, this is what we observe after fixing our setup, do you confirm ?

Could you please advise on how we should proceed if we want to achieve the same without being pseudosuperuser ?
Our Lab tried it but never succeeded to make it work, this is why they finally gave this pseudosuperuser role.

Daniel_Leybovic

COPY

Permissions

You must connect to the HP Vertica database as a superuser, or, as a non-superuser, have a USER-accessible storage location, and applicable READ or WRITE privileges granted to the storage location from which files are read or written to. COPY LOCAL users must have INSERT privileges to copy data from the STDIN pipe, as well as USAGE privileges on the schema.