Options
Permission denied on storage location when using copy as pseudosuperuser
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.
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.
0
Comments
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.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
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;
Now I wonder, could I achieve the same result but without being pseudosuperuser, with the appropriate grant ?
Yes, you can achieve it (please review COPY LOCAL statement also).
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.