Page 1 of 2
ERROR: permission denied for storage location
Posted: Thu May 24, 2012 12:03 pm
by Julie
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?
Re: ERROR: permission denied for storage location
Posted: Thu May 24, 2012 1:53 pm
by JimKnicely
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!
Re: ERROR: permission denied for storage location
Posted: Thu Apr 04, 2013 9:39 pm
by seanarcher7
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?
Re: ERROR: permission denied for storage location
Posted: Wed Apr 10, 2013 1:36 pm
by JimKnicely
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!
Re: ERROR: permission denied for storage location
Posted: Wed Apr 10, 2013 2:11 pm
by becky
Jim,
Do you know what system table we can query to view the privileges that have been granted on storage locations to users?
Re: ERROR: permission denied for storage location
Posted: Thu Apr 11, 2013 2:29 am
by seanarcher7
Jim,
I too have the same question like becky. Additionally what will be the storage location for a user? How can we know that?
Re: ERROR: permission denied for storage location
Posted: Fri Apr 12, 2013 11:49 am
by JimKnicely
seanarcher7,
Make sure the directories you want to use for a storage locations have write permissions for the Vertica process.