ERROR: permission denied for storage location

Moderator: NorbertKrupa

User avatar
Julie
Master
Master
Posts: 221
Joined: Thu Apr 19, 2012 9:29 pm

ERROR: permission denied for storage location

Post by Julie » Thu May 24, 2012 12:03 pm

Hi guys,

I created a new user in Vertica who needs to be able to run his own COPY commands. When he tries to run a COPY he gets an error saying he does not have access to the storage location containing his file:

Code: Select all

[wdrake@verticatst_01 ~]$ /opt/vertica/bin/vsql -U wdrake -w xxxxxx
Welcome to vsql, the Vertica Analytic Database v5.1.1-0 interactive terminal.

Type:  \h for help with SQL commands
       \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

wdrake=> copy wdrake.copy_test from '/home/wdrake/my.txt' direct no escape null as '\N';
ERROR:  permission denied for storage location [/home/wdrake/my.txt]
It's weird because its the user's home directory!

Do I need to grant some privilege to allow users to read from disk?
Thanks,
Juliette

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR: permission denied for storage location

Post by JimKnicely » Thu May 24, 2012 1:53 pm

Julie,

I believe that only the super user in Vertica can copy directly from a file. Normal users can still use the COPY command, but they have to do so via the STDIN (Standard Input).

Here is an example:

There is a user in Vertica named jknicely who has all privileges on a schema named jknicely. In that schema is a table name copy_test.

Code: Select all

jknicely=> \d jknicely.copy_test;
                                       List of Fields by Tables
  Schema  |   Table   | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
----------+-----------+--------+--------------+------+---------+----------+-------------+-------------
jknicely | copy_test | col1   | int          |    8 |         | f        | f           |
jknicely | copy_test | col2   | varchar(100) |  100 |         | f        | f           |
jknicely | copy_test | col3   | numeric(5,2) |    8 |         | f        | f           |
(3 rows)
In the user jknicely’s home directory there is a text file named jim.txt.

Code: Select all

[jknicely@verticatst01 ~]$ pwd
/home/jknicely
 
[jknicely@verticatst01 ~]$ cat jim.txt
1|Jim Knicely|100.23
2|Jingxuan Li|101.21
3|Philip Marlow|203.23
4|Peter Griffen|234.11
The user wants to copy its contents into the table jknicely.copy_test. He will have to pipe the contents of the file into the STDIN of the COPY command something like this:

Code: Select all

[jknicely@verticatst01 ~]$ cat /home/jknicely/jim.txt | /opt/vertica/bin/vsql -U jknicely -w hamster87 -c "copy jknicely.copy_test from stdin direct no escape null as '\N' exceptions '/home/jknicely/jim.err';"
Then we can check that it worked by querying the table:

Code: Select all

[jknicely@verticatst01 ~]$ /opt/vertica/bin/vsql -U jknicely -w hamster87
Welcome to vsql, the Vertica Analytic Database v5.1.1-0 interactive terminal.
 
Type:  \h for help with SQL commands
       \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit
 
jknicely=> select * from jknicely.copy_test;
col1 |     col2      |  col3
------+---------------+--------
    1 | Jim Knicely   | 100.23
    2 | Jingxuan Li   | 101.21
    3 | Philip Marlow | 203.23
    4 | Peter Griffen | 234.11
(4 rows)
I hope this helps!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

seanarcher7
Newbie
Newbie
Posts: 7
Joined: Thu Apr 04, 2013 8:43 pm

Re: ERROR: permission denied for storage location

Post by seanarcher7 » Thu Apr 04, 2013 9:39 pm

Knicely, I too faced the same error and I modified to use stdin/copy command. But I still faced the same error. However, when I removed exceptions from the command it worked just fine. See below.

This worked:

Code: Select all

cat /home/xxxxxx/file.dat | /opt/vertica/bin/vsql -U xxxxxx-w yyyyyy -c "copy xxxxxx.book from stdin direct no escape null as '\N'"
This failed:

Code: Select all

cat /home/xxxxxx/file.dat | /opt/vertica/bin/vsql -U xxxxxx-w yyyyyy -c "copy xxxxxx.book from stdin direct no escape null as '\N' exceptions '/home/xxxxxx/file.err';"
with error: ERROR 4368: Permission denied for storage location [/home/xxxxxx/file.err].
What could possibly be the issue?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR: permission denied for storage location

Post by JimKnicely » Wed Apr 10, 2013 1:36 pm

Hi seanarcher7,

My guess would be that the user does not have the write privilege on the storage location in the database.

The SQL Reference Manual states the write privilege on a storage location:
The Lets users or roles export data from a table to a storage location. WRITE privileges also lets users export COPY statement exceptions and rejected data files from Vertica to the specified storage location.
Try granting the WRITE privilege on the storage location to the user and see if that helps!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
becky
Intermediate
Intermediate
Posts: 118
Joined: Sat Apr 28, 2012 11:37 am

Re: ERROR: permission denied for storage location

Post by becky » Wed Apr 10, 2013 2:11 pm

Jim,

Do you know what system table we can query to view the privileges that have been granted on storage locations to users?
THANKS - BECKSTER

seanarcher7
Newbie
Newbie
Posts: 7
Joined: Thu Apr 04, 2013 8:43 pm

Re: ERROR: permission denied for storage location

Post by seanarcher7 » Thu Apr 11, 2013 2:29 am

Jim,

I too have the same question like becky. Additionally what will be the storage location for a user? How can we know that?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: ERROR: permission denied for storage location

Post by JimKnicely » Fri Apr 12, 2013 11:49 am

seanarcher7,

Make sure the directories you want to use for a storage locations have write permissions for the Vertica process.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Error Codes”