Is it possible to access AWS S3 on different AWS account with IAM roles on Vertica machine?

Poslanik

Hi,
We have been using Vertica 9.3 version hosted on AWS EC2 instances on our AWS account A and were able to access S3 bucket on another AWS account B with Bucket policy on that S3 bucket with Principal Role which is assigned to EC2 instances where Vertica is deployed on AWS account A.
AWS account A AWS account B
Vertica 9.3 on EC2 instance with AWS Vertica-role S3 bucket policy Principal Vertica-role

After upgrading to Vertica version 11 we are not able to reach AWS account B S3 bucket with the same setup.
We are told to use ALTER SESSION SET AWSAuth.
For that on AWS account B on S3 bucket we need to have Principal AWS user (instead of a role) and in our SQL queries we need to hardcode accesskey and secretaccesskey of that new AWS user.

ALTER SESSION SET AWSAuth='XXXXXX:XXXXXXXXXXXXXXX';

Vertica documentation says we should use AWS IAM roles to access AWS resources, but we are not able to.

Please tell me there is a way and that I am missing something.

Thank you

SruthiA

@Poslanik : Could you please share me the error message you are receiving?

Poslanik

Hi @SruthiA ,

Sorry for the late answer.

I am receiving this error:
SQL Error [7160] [22023]: [Vertica]VJDBC ERROR: Cannot expand glob pattern due to error: You are trying to access your S3 bucket using the wrong region. If you are using S3 file system please set 'AWSRegion' knob to the region of your bucket. When using AWS UDX you need to set region using aws_set_config('aws_region', '')

Queries I am executing are as follows:

ALTER SESSION SET AWSRegion='eu-west-1';

COPY <table_name>
(<list_of_columns>)
from
's3://<some_file_path>.json.gz'
FILTER GZip() parser fjsonparser(flatten_maps = false) direct;

If I execute
ALTER SESSION SET AWSAuth='************:***********************';
with access key and secret of a specific AWS user
before COPY, I receive no error.

But I find it strange that I have to authenticate with AWS user to access AWS resources on another AWS account.

Thank you

SruthiA

@Poslanik : Please set AWSEndpoint with the value pertaining to the region you are trying to use and try.. If you just set AWSAuth, is it succeeding?

Poslanik

@SruthiA,
If I just set AWSAuth without setting AWSRegion, it also fails with the same error.
Probably because Vertica default AWS region and S3 bucket region from where I try to COPY data are different.

For it to succeed I have to execute:

ALTER SESSION SET AWSRegion='eu-west-1';
ALTER SESSION SET AWSAuth='************:***********************';
COPY <table_name>
(<list_of_columns>)
from
's3://<some_file_path>.json.gz'
FILTER GZip() parser fjsonparser(flatten_maps = false) direct;

Could you please elaborate what do you mean by > Please set AWSEndpoint with the value pertaining to the region you are trying to use
I see AWSEndpoint parameter has default value s3.amazonaws.com, so not sure what I need to set.

Thank you

SruthiA

@Poslanik : please find the below link which contains endpoint URLS for all the regions. Please set it according your region and retry.

https://docs.aws.amazon.com/general/latest/gr/s3.html

Poslanik

Unfortunately I am getting the same error after I set AWSEndpoint to the region of a S3 bucket from where I am trying to COPY data to Vertica.

ALTER SESSION SET AWSEndpoint='s3.eu-west-1.amazonaws.com';

Also tried with other standard endpoints.

SruthiA

@Poslanik : Could you please restart the cluster and try once?