How do I grant users permission to run AWS_SET_CONFIG?

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

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.

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)

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

Thanks again for your help.

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

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.

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!