Vertica COPY Command Restriction

[shiva]

Hi ,
I tried to do bulk copy (from file) using COPY command and i am a normal DB user, and vertica allows that operation only by super user.
Is there a way i can achieve this operation as a normal user? Please through some light.

Copied this data from SQL Reference:
Note: You must connect as the database superuser to copy from a file. Any user with INSERT privileges can copy data from the STDIN pipe.

Thanks in advance,
~Shiva.

[JimKnicely]

Hi Shiva,

I am able to use the COPY LOCAL command from a file as a regular user.

Simple example...

Set up:

Code: Select all

dbadmin=> create schema insert_schema;
CREATE SCHEMA

dbadmin=> create user insert_user;
CREATE USER

dbadmin=> grant usage on schema insert_schema to insert_user;
GRANT PRIVILEGE

dbadmin=> create table insert_schema.insert_table (col1 int);
CREATE TABLE

dbadmin=> grant insert on insert_schema.insert_table to insert_user;
GRANT PRIVILEGE

Now logged in as the intersect_user I can add rows to the insert_schema.insert_table table via the COPY LOCAL command:

Code: Select all

bash-3.2$ vsql -U insert_user
SET
Welcome to vsql, the Vertica Analytic Database interactive terminal.

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

insert_user=> COPY insert_schema.insert_table FROM LOCAL '/usr/home/dbadmin/test_file.txt';
 Rows Loaded
-------------
           3
(1 row)

insert_user=> select user;
 current_user
--------------
 insert_user
(1 row)

insert_user=> select version();
              version
------------------------------------
 Vertica Analytic Database v6.0.1-0
(1 row)

Am I doing something different than you?

[weikang]

You can use command below to add storage location and grant permission to users need copy

select add_location ('/myvertica/Test/KMM/SSD','','USER','SSD');

GRANT ALL ON LOCATION '/myvertica/Test/KMM/SSD' TO Bob;

For reference:


https://my.vertica.com/docs/6.1.x/HTML/ ... #11759.htm