How to use IAM Role for redshift_connector connection string in Glue job?

[dburtsev]

Hello
I have a Glue job with an attached Role (arn:aws:iam:...:role/...). I can connect to Redshift with boto3.client('redshift-data')
I can get an attached role name in runtime.
Is it possible to connect to Redshift using redshift_connector with Role?

awsconn = redshift_connector.connect(
    iam=True,
    host='xyz.redshift.amazonaws.com',
    cluster_identifier='xyz',
    database='qwe',
    db_user='qwe,
    role_session_name = role,
    port = 5439,
    ssl = True,
    region = 'us-east-1'
 )

InterfaceError: Invalid connection property setting. Credentials provider, AWS credentials, Redshift auth profile or AWS profile must be provided when IAM is enabled

redshift_connector-2.0.886
Thanks,
Dmitriy

[Brooke-white]

Hey Dmitriy, thanks for reaching out. AFAIK we should be picking up this role when we establish a boto client for redshift.Could you please share the redshift_connector log so I can look into this more?

[dburtsev]

How can I get redshift_connector log from my Glue job?

[dburtsev]

22/08/01 14:13:50 ERROR GlueExceptionAnalysisListener: [Glue Exception Analysis] {
"Event": "GlueETLJobExceptionEvent",
"Timestamp": 1659363230170,
"Failure Reason": "Traceback (most recent call last):\n File "/tmp/role_demo.py", line 65, in \n region = 'us-east-1'\n File "/tmp/redshift_connector-2.0.886-py3-none-any.whl/redshift_connector/init.py", line 326, in connect\n IamHelper.set_iam_properties(info)\n File "/tmp/redshift_connector-2.0.886-py3-none-any.whl/redshift_connector/iam_helper.py", line 124, in set_iam_properties\n "Invalid connection property setting. Credentials provider, AWS credentials, Redshift auth profile "\nredshift_connector.error.InterfaceError: Invalid connection property setting. Credentials provider, AWS credentials, Redshift auth profile or AWS profile must be provided when IAM is enabled",
"Stack Trace": [
{
"Declaring Class": "set_iam_properties",
"Method Name": ""Invalid connection property setting. Credentials provider, AWS credentials, Redshift auth profile "",
"File Name": "/tmp/redshift_connector-2.0.886-py3-none-any.whl/redshift_connector/iam_helper.py",
"Line Number": 124
},
{
"Declaring Class": "connect",
"Method Name": "IamHelper.set_iam_properties(info)",
"File Name": "/tmp/redshift_connector-2.0.886-py3-none-any.whl/redshift_connector/init.py",
"Line Number": 326
},
{
"Declaring Class": "",
"Method Name": "region = 'us-east-1'",
"File Name": "/tmp/role_demo.py",
"Line Number": 65
}
],
"Last Executed Line number": 65,
"script": "role_demo.py"
}

[dburtsev]

@Brooke-white
Hi
Any update on this issue?
Thanks

[Brooke-white]

Hey @dburtsev ,

Apologies for the delayed response, I appreciate you for following up on this.

You can enable logging using the instructions here in README.

While this may not apply to your use case's permissions, is redshift_connector able to connect without the role present? I ask just as this is a measure to assure the credentials are being picked up from your Glue runtime environment.

Aside from that, I'd recommend to follow the instructions here to place the name of your role in an ~./aws/config file. Then the name of this role can be passed to redshift_connector as shown in this example. this should allow redshift_connector's calls to boto3 to utilize this profile which contains the desired role name.

[dburtsev]

Hi @Brooke-white

1. My Glue job use boto3.client('redshift-data') and redshift_connector. Boto3 can use the attached Role and connect to Redshift but redshift_connector can't. The role is OK because boto3 works.
2. Without a role boto3 and redshift_connector can't connect to RedShift
3. There is no /aws/config file in Glue
4. When I add role_arn = 'arn:aws:iam::685276224587:role/XYZ', to redshift_connector.connect I get the same error.
5. The previous version of this script runs from EC2 using the attached Role without issues.
   Thanks

[Brooke-white]

The previous version of this script runs from EC2 using the attached Role without issues.

What differs in the previous version of this script? Dependency versions? Script logic?

There is no /aws/config file in Glue

You can override the location of the config file and create your own, but given the above note we should probably dig into what changed between the two versions of the script.

[dburtsev]

What differs in the previous version of this script?

The previous version of the script was running on EC2 instance with an empty /aws/config file and the Role attached to EC2.
I want to run the same script as the Glue job with attached Role.

[Brooke-white]

Ok thank you for clarifying. I notice you are using an old version of redshift_connector. Could you please update to latest version? I ask because in a newer version than what you're using we fixed #15 which I believe is what you're hitting.

[dburtsev]

I upgrade to redshift_connector-2.0.908-py3-none-any.whl
Now I have InterfaceError: ('communication error', TimeoutError(110, 'Connection timed out'))

[dburtsev]

It may be an issue with Role.

[dburtsev]

I guess the Glue job needs VPC
I add Connection to the job. I don't use the connection, I just add it. Now my job has VPC, Subnet, and Security Groups.
redshift_connector works when I hardcoded the user name and password. I still have a problem with Role.
InterfaceError: ('communication error', NotADirectoryError(20, 'Not a directory'))

[Brooke-white]

Could you enable logging or share more of the error trace so I can try and understand where this error is hit?

[dburtsev]

22/08/10 17:12:45 ERROR ProcessLauncher: Error from Python:Traceback (most recent call last):
File "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/core.py", line 606, in init
ssl_context.load_verify_locations(path)
NotADirectoryError: [Errno 20] Not a directory

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "/tmp/role_demo2.py", line 46, in
region = 'us-east-1'
File "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/init.py", line 360, in connect
numeric_to_float=info.numeric_to_float,
File "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/core.py", line 632, in init
raise InterfaceError("communication error", e)
redshift_connector.error.InterfaceError: ('communication error', NotADirectoryError(20, 'Not a directory'))
22/08/10 17:12:45 ERROR ProcessLauncher: Error from Python:Traceback (most recent call last): File "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/core.py", line 606, in init ssl_context.load_verify_locations(path) NotADirectoryError: [Errno 20] Not a directory During handling of the above exception, another exception occurred: Traceback (most recent call last): File "/tmp/role_demo2.py", line 46, in region = 'us-east-1' File "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/init.py", line 360, in connect numeric_to_float=info.numeric_to_float, File "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/core.py", line 632, in init raise InterfaceError("communication error", e) redshift_connector.error.InterfaceError: ('communication error', NotADirectoryError(20, 'Not a directory'))

[Brooke-white]

how are you bringing redshift_connector into glue environment? also please enable redshift_connector logging

You can enable logging using the instructions here in README.

[Brooke-white]

this looks like some path issue reading in redshift cert

"Method Name": "ssl_context.load_verify_locations(path)",
"File Name": "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/core.py",
"Line Number": 606

[dburtsev]

22/08/10 17:12:45 ERROR GlueExceptionAnalysisListener: [Glue Exception Analysis] {
"Event": "GlueETLJobExceptionEvent",
"Timestamp": 1660151565946,
"Failure Reason": "Traceback (most recent call last):\n File "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/core.py", line 606, in init\n ssl_context.load_verify_locations(path)\nNotADirectoryError: [Errno 20] Not a directory\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n File "/tmp/role_demo2.py", line 46, in \n region = 'us-east-1'\n File "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/init.py", line 360, in connect\n numeric_to_float=info.numeric_to_float,\n File "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/core.py", line 632, in init\n raise InterfaceError("communication error", e)\nredshift_connector.error.InterfaceError: ('communication error', NotADirectoryError(20, 'Not a directory'))",
"Stack Trace": [
{
"Declaring Class": "init",
"Method Name": "raise InterfaceError("communication error", e)",
"File Name": "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/core.py",
"Line Number": 632
},
{
"Declaring Class": "connect",
"Method Name": "numeric_to_float=info.numeric_to_float,",
"File Name": "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/init.py",
"Line Number": 360
},
{
"Declaring Class": "",
"Method Name": "region = 'us-east-1'",
"File Name": "/tmp/role_demo2.py",
"Line Number": 46
},
{
"Declaring Class": "init",
"Method Name": "ssl_context.load_verify_locations(path)",
"File Name": "/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/core.py",
"Line Number": 606
}
],
"Last Executed Line number": 46,
"script": "role_demo2.py"
}

[dburtsev]

how are you bringing redshift_connector into glue environment?

Each Glue job can use external Python libraries. I download scramp-1.4.1-py3-none-any.whl, asn1crypto-1.5.1-py2.py3-none-any.whl and redshift_connector-2.0.908-py3-none-any.whl into s3 bucket. Glue job has property: Libraries
"Specify Amazon S3 file locations for any Python libraries, referenced files, or dependent jar files required by your job script. If there are multiple files of a specific type, you can enter multiple Amazon S3 paths separated by commas. Only pure Python libraries can be used."

[dburtsev]

this looks like some path issue reading in redshift cert

I can connect when I use the user name and password.

[Brooke-white]

@dburtsev please look to this response I've given in past that shows code snippit for enable logging.

[Brooke-white]

thank you :)

2022-08-10T15:52:11.157-04:00	2022-08-10 19:52:11,134 - redshift_connector.iam_helper - DEBUG - boto3.client(service_name=redshift) being used for IAM auth
...
...
2022-08-10T15:52:11.212-04:00	2022-08-10 19:52:11,211 - botocore.credentials - DEBUG - Found credentials from IAM Role: dummy
...
2022-08-10 19:52:11,396 - redshift_connector.iam_helper - DEBUG - Using temporary aws credentials with expiration: 2022-08-10 20:07:11.385000+00:00

I've picked out a few lines from the log you posted (I went ahead and deleted the log since some sensitive information is being logged by boto)

but my main take away here is

1. redshift_connector picks up your role, dummy, and uses it to fetch temporary credentials

Unless you are manually setting connection parameter ssl=False when you connect using user/password, I don't understand why the redshift certificate wouldn't be picked up (this error ~L606 of core.py) when authenticating using the role.

Could you please confirm that you can connect to Redshift with ssl=True when using user/password from Glue?

[dburtsev]

Unless you are manually setting connection parameter ssl=False when you connect using user/password,

Yes, I did.

Could you please confirm that you can connect to Redshift with ssl=True when using user/password from Glue?

No, I can't. InterfaceError: ('communication error', NotADirectoryError(20, 'Not a directory'))

[dburtsev]

Do you need log?

[Brooke-white]

Ok, seeing you had SSL disabled when using user/password provides some context here. The issue at hand has to do with SSL, as when SSL is enabled we read the redshift-ca-bundle.crt cert from redshift_connector/files. It should be at the location
'/tmp/redshift_connector-2.0.908-py3-none-any.whl/redshift_connector/files/redshift-ca-bundle.crt' in your case.

Is the cert present there?

[Brooke-white]

also, wondering if redshift_connector-2.0.908-py3-none-any.whl is actually a folder, because if not that could cause issue with loading the cert

[dburtsev]

There is no certificate. I download only *.whl files. Where should I get the certificate file?

[dburtsev]

if redshift_connector-2.0.908-py3-none-any.whl is actually a folder,

No, it is a file.

[dburtsev]

Correction:
*.whl files are zip archives. These files may have folders inside.

[Brooke-white]

Ok I see. You mention you set this Glue Job property "Libraries" and pass S3 URIs for mentioned packages. I'm not seeing this parameter in the docs: https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-glue-arguments.html could you clarify which parameter is used?

[dburtsev]

It is --extra-py-files

[Brooke-white]

I ask because I'm seeing another parameter recommended here: https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-python-libraries.html#addl-python-modules-support --additional-python-modules

[dburtsev]

We assign this property at design time

[Brooke-white]

If you're not doing so already, I'd recommend to try using this --additional-python-modules argument to pass the S3 URI for the redshift_connector whl

[dburtsev]

We assign this property at design time.