Privileges to load data

[sean]

Hi,

I have a Vertica DB user that has INSERT,SELECT access on a table. When I try to load data into the table, it fails with error:
Failed to create default projections for table, permission denied for schema


Thanks.

[JimKnicely]

Hi Sean,

Did you grant the user the USAGE privilege on the schema too?

[sean]

Yes I did

[JimKnicely]

I'm having trouble duplicating your issue in Vertica 6.0.3.

Code: Select all

dbadmin=> create schema test;
CREATE SCHEMA
dbadmin=> create user jim;
CREATE USER
dbadmin=> grant usage on schema test to jim;
GRANT PRIVILEGE
dbadmin=> create table test.t1 (c1 int);
CREATE TABLE
dbadmin=> grant insert, select on test.t1 to jim;
GRANT PRIVILEGE
dbadmin=> \c mydb jim
You are now connected to database "mydb" as user "jim".
intersect=> copy test.t1 from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> \.
intersect=> select * from test.t1;
 c1
----
  1
(1 row)

I didn't have to grant any other privileges to the user JIM...

From page 159 of the 6.0.3 Administrator's Guide:

Implicit projection creation and privileges
Projections get implicitly created when you insert data into a table, an operation that automatically creates a superprojection for the table.
Implicitly-created projections do not require any additional privileges to create or drop, other than privileges for table creation. Users who can create a table or drop a table can also create and drop the associated superprojection.

Who is the owner of the table you are having an issue with and in which schema is it?

[sean]

I'm on Vertica version 5.1.4.

My user has USAGE privileges on the schema but i still wasn't able to create projections on the tables. The DBA gave CREATE privileges to the user and it worked then. But glad to know that version 6 does need any explicit grants for projections. we are in the process of moving to the latest version.

Thanks for your help knicely.
Sean