Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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.

Comments

  • 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.
  • 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
  • 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;
  • 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 ?

  • Hi!

    Yes, you can achieve it (please review COPY LOCAL statement also).
  • 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.
  • 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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.