How do I grant users permission to run AWS_SET_CONFIG?

nchammas-mm

I want non-dbadmin users to be able to set AWS configs and then use the s3() source to load data.

I tried the following with a non-dbadmin user:

SELECT 
    AWS_SET_CONFIG('aws_id', '...'),
    AWS_SET_CONFIG('aws_secret', '...')
;

However, when I do, I get an error:

[Vertica][VJDBC](3457) ERROR: Function AWS_SET_CONFIG(unknown, unknown) does not exist, or permission is denied for AWS_SET_CONFIG(unknown, unknown)

I don't get this error if I run the same query as dbadmin. So I tried to grant permission on the function to the user I'm interested in, but it doesn't work.

For example, if I try this as dbadmin:

GRANT EXECUTE ON FUNCTION AWS_SET_CONFIG() TO my_user;

I get this error:

[Vertica][VJDBC](2059) ROLLBACK: Function with specified name and parameters does not exist: AWS_SET_CONFIG

I've tried a bunch of variations, including stuff like AWS_SET_CONFIG(param VARCHAR), but they all yield the same error.

I also tried this variation with my_user:

ALTER SESSION SET UDPARAMETER FOR awslib aws_id='...';

This yields a different error:

[Vertica][VJDBC](6902) ROLLBACK: Permission denied on [public.awslib]

I'm not sure how to grant permission on public.awslib. It doesn't appear to be a visible object.

How do I allow non-dbadmin users to run AWS_SET_CONFIG() or the UDPARAMETER equivalent? I'm running Vertica 8.1.1.

Jim_Knicely

Try "grant usage on library public.awslib to USER;"

Example:

[dbadmin@s18384357 ~]$ vsql -U jim
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

jim=> ALTER SESSION SET UDPARAMETER FOR awslib aws_id='XXXXXXXXXXXXXXXXXXXXXXXXXXX';
ROLLBACK 6902:  Permission denied on [public.awslib]
jim=> \q

[dbadmin@s18384357 ~]$ vsql -U dbadmin
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

dbadmin=> grant usage on library public.awslib to jim;
GRANT PRIVILEGE

dbadmin=> \q
[dbadmin@s18384357 ~]$ vsql -U jim
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

jim=> ALTER SESSION SET UDPARAMETER FOR awslib aws_id='XXXXXXXXXXXXXXXXXXXXXXXXXXX';
ALTER SESSION

nchammas-mm

Thanks Jim, that worked for me! I didn't know about GRANT USAGE ON LIBRARY.

It looks like that grant only affects the ALTER SESSION method, by the way. AWS_SET_CONFIG() is still not accessible by the non-dbadmin user. It's not a big deal since there is an alternative with ALTER SESSION, but I'm curious: Is there any way to get a non-dbadmin user to run AWS_SET_CONFIG() specifically? A different grant statement, perhaps?

Anyway, thanks again for your quick and helpful response.

Jim_Knicely

Hi,

Try this: "grant execute on function AWS_SET_CONFIG(varchar, varchar) to jim;"

Example:

[dbadmin@s18384357 ~]$ vsql -U jim
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

jim=> SELECT AWS_SET_CONFIG('aws_id', 'XXXXXXXXXXXXXXXXXXXXXXXXXXX');
ERROR 3457:  Function AWS_SET_CONFIG(unknown, unknown) does not exist, or permission is denied for AWS_SET_CONFIG(unknown, unknown)
HINT:  No function matches the given name and argument types. You may need to add explicit type casts
jim=> \q

[dbadmin@s18384357 ~]$ vsql
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

dbadmin=> grant execute on function AWS_SET_CONFIG(varchar, varchar) to jim;
GRANT PRIVILEGE

dbadmin=> \q

[dbadmin@s18384357 ~]$ vsql -U jim
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

jim=> SELECT AWS_SET_CONFIG('aws_id', 'XXXXXXXXXXXXXXXXXXXXXXXXXXX');
 AWS_SET_CONFIG
----------------
 aws_id
(1 row)

nchammas-mm

Excellent. Seems obvious in retrospect (the input is two strings)!

Thanks again for your help.

Jim_Knicely

No problem!

P.S. Be careful handing out the AWS Id and Secret key.

Have you looked at IAM Roles for Amazon EC2? Using these you'll only have to tell Vertica the AWS region. Much more secure...

See:
Specifying COPY FROM Options (Scroll down to IAM section)
https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/AdministratorsGuide/BulkLoadCOPY/SpecifyingCOPYFROMOptions.htm

IAM Roles for Amazon EC2
http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/iam-roles-for-amazon-ec2.html

nchammas-mm

We are using IAM user credentials and not roles because we want to segregate access among the various users connecting to the same Vertica cluster. I believe attaching an IAM role to the cluster would let anybody with access to the cluster access the same data.

With a key chain table and multiple credentials, we can make sure only certain users can see certain data, while others on the same cluster cannot.

Jim_Knicely

Yeah, the role is for the entire cluster. I like your solution and thanks for sharing. I'm sure others will be interested in doing the same for the same reasons!