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.
Permission denied on storage location when using copy as pseudosuperuser
+2
+1Hi!
Did you defined ADD_LOCATION for NFS? (mandatory with USAGE=USER)
If it will not help run separately GRANT on:
Did you defined ADD_LOCATION for NFS? (mandatory with USAGE=USER)
If it will not help run separately GRANT on:
- database
- schema
- table
- location
+2I 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
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
+1Hi!
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;
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;
+2OK, 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 ?
Now I wonder, could I achieve the same result but without being pseudosuperuser, with the appropriate grant ?
+1Hi!
Yes, you can achieve it (please review COPY LOCAL statement also).
Yes, you can achieve it (please review COPY LOCAL statement also).
+2Hi,
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.
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.
+1COPY
PermissionsYou must connect to the HP Vertica database as a superuser, or, as a non-superuser, have a USER-accessible storage location, and applicable
READorWRITEprivileges granted to the storage location from which files are read or written to.COPY LOCALusers must haveINSERTprivileges to copy data from theSTDINpipe, as well asUSAGEprivileges on the schema.
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
