Configure the Vertica library for Amazon Web Services

You use the Vertica library for Amazon Web Services (AWS) to export data from Vertica to S3.

You use the Vertica library for Amazon Web Services (AWS) to export data from Vertica to S3. This library does not support IAM authentication. You must configure it to authenticate with S3 by using session parameters containing your AWS access key credentials. You can set your session parameters directly, or you can store your credentials in a table and set them with the AWS_SET_CONFIG function.

Because the AWS library uses session parameters, you must reconfigure the library with each new session.

Set AWS authentication parameters

The following AWS authentication parameters allow you to access AWS and work with the data in your Vertica database:

  • aws_id: The 20-character AWS access key used to authenticate your account.

  • aws_secret: The 40-character AWS secret access key used to authenticate your account.

  • aws_session_token: The AWS temporary security token generated by running the AWS STS command get-session-token. This AWS STS command generates temporary credentials you can use to implement multi-factor authentication for security purposes. See Implementing Multi-factor Authentication.

Implement multi-factor authentication

Implement multi-factor authentication as follows:

  1. Run the AWS STS command get-session-token, this returns the following:

    $ Credentials": {
"SecretAccessKey": "bQid6jNuSWRqUzkIJCFG7c71gDHZY3h7aDSW2DU6",
"SessionToken":
"FQoDYXdzEBcaDKM1mWpeu88nDTTFICKsAbaiIDTWe4BTh33tnUvo9F/8mZicKKLLy7WIcpT4FLfr6ltIm242/U2CI9G/
XdC6eoysUi3UGH7cxdhjxAW4fjgCKKYuNL764N2xn0issmIuJOku3GTDyc4U4iNlWyEng3SlshdiqVlk1It2Mk0isEQXKtx
F9VgfncDQBxjZUCkYIzseZw5pULa9YQcJOzl+Q2JrdUCWu0iFspSUJPhOguH+wTqiM2XdHL5hcUcomqm41gU=",
"Expiration": "2018-04-12T01:58:50Z",
"AccessKeyId": "ASIAJ4ZYGTOSVSLUIN7Q"
 }
}

    For more information on get-session-token, see the AWS documentation.

  2. Using the SecretAccessKey returned from get-sessiontoken, set your temporary aws_secret:

    => ALTER SESSION SET UDPARAMETER FOR awslib aws_secret='bQid6jNuSWRqUzkIJCFG7c71gDHZY3h7aDSW2DU6';

  3. Using the SessionToken returned from get-session-token, set your temporary aws_session_token:

    => ALTER SESSION SET UDPARAMETER FOR awslib aws_session_token='FQoDYXdzEBcaDKM1mWpeu88nDTTFICKsAbaiIDTWe4B
Th33tnUvo9F/8mZicKKLLy7WIcpT4FLfr6ltIm242/U2CI9G/XdC6eoysUi3UGH7cxdhjxAW4fjgCKKYuNL764N2xn0issmIuJOku3GTDy
c4U4iNlWyEng3SlshdiqVlk1It2Mk0isEQXKtxF9VgfncDQBxjZUCkYIzseZw5pULa9YQcJOzl+Q2JrdUCWu0iFspSUJPhOguH+wTq
iM2XdHL5hcUcomqm41gU=';

  4. Using the AccessKeyID returned from get-session-token, set your temporary aws_id:

    => ALTER SESSION SET UDPARAMETER FOR awslib aws_id='ASIAJ4ZYGTOSVSLUIN7Q';

The Expiration value returned indicates when the temporary credentials expire. In this example expiration occurs April 12, 2018 at 01:58:50.

These examples show how to implement multifactor authentication using session parameters. You can use either of the following methods to securely set and store your AWS account credentials:

AWS access key requirements

To communicate with AWS, your access key must have the following permissions:

  • s3:GetObject

  • s3:PutObject

  • s3:ListBucket

For security purposes, Vertica recommends that you create a separate access key with limited permissions specifically for use with the Vertica Library for AWS.

Configure session parameters directly

These examples show how to set the session parameters for AWS using your own credentials. Parameter values are case sensitive:

  • aws_id: This value is your AWS access key ID.

    => ALTER SESSION SET UDPARAMETER FOR awslib aws_id='AKABCOEXAMPLEPKPXYZQ';

  • aws_secret: This value is your AWS secret access key.

    => ALTER SESSION SET UDPARAMETER FOR awslib aws_secret='CEXAMPLE3tEXAMPLE1wEXAMPLEFrFEXAMPLE6+Yz';

  • aws_region: This value is the AWS region associated with the S3 bucket you intend to access. Left unconfigured, aws_region will default to us-east-1. It identifies the default server used by Amazon S3. 

    => ALTER SESSION SET UDPARAMETER FOR awslib aws_region='us-east-1';

When using ALTER SESSION:

  • Using ALTER SESSION to change the values of S3 parameters also changes the values of corresponding UDParameters.

  • Setting a UDParameter changes only the UDParameter.

  • Setting a configuration parameter changes both the AWS parameter and UDParameter.

Configure session parameters using credentials stored in a table

You can place your credentials in a table and secure them with a row-level access policy. You can then call your credentials with the AWS_SET_CONFIG scalar meta-function. This approach allows you to store your credentials on your cluster for future session parameter configuration. You must have dbadmin access to create access policies.

  1. Create a table with rows or columns corresponding with your credentials:

    => CREATE TABLE keychain(accesskey varchar, secretaccesskey varchar);

  2. Store your credentials in the corresponding columns:

    => COPY keychain FROM STDIN;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> AEXAMPLEI5EXAMPLEYXQ|CCEXAMPLEtFjTEXAMPLEiEXAMPLE6+Yz
>> \.

  3. Set a row-level access policy appropriate to your security situation.

  4. With each new session, configure your session parameters by calling the AWS_SET_CONFIG parameter in a SELECT statement:

    => SELECT AWS_SET_CONFIG('aws_id', accesskey), AWS_SET_CONFIG('aws_secret', secretaccesskey)
   FROM keychain;
 aws_set_config | aws_set_config
----------------+----------------
 aws_id         | aws_secret
(1 row)

  5. After you have configured your session parameters, verify them:

    => SHOW SESSION UDPARAMETER ALL;