Cannot connect to redshift using IAM credentials

[aseem-poloniex]

Driver version

2.0.888

Redshift version

1.0.31802

Client Operating System

Mac os

Python version

3.8

Problem description

Unable to connect to redshift cluster using IAM role from my local machine using redshift-connector package.

Connection is successful when using normal username & password to connect :

conn = redshift_connector.connect(
                host=<my_redshift_host>,
                database='dev',
                user='redshift-admin',
                password='iajsdoiajsdoijaosdijaojsd'
            )

Error - raise InterfaceError("communication error", e) redshift_connector.error.InterfaceError: ('communication error', gaierror(8, 'nodename nor servname provided, or not known'))

Reproduction code

conn = redshift_connector.connect(
                iam=True,
                database='dev',  
                db_user='redshift-admin', 
                password='', # tried using aws user password 
                user='', # tried using aws username
                cluster_identifier='redshift-cluster-1',
                profile='default'
            )

[Brooke-white]

Thank you for reporting. The team will work to reproduce this and follow up shortly.

[Brooke-white]

Could you please provide a debug log showing this issue?

[aseem-poloniex]

Traceback (most recent call last):
  File "/Users/ahegshetye/Code/data-warehouse/rs_vacuum/venv/lib/python3.8/site-packages/redshift_connector/core.py", line 539, in __init__
    self._usock.connect((host, port))
socket.gaierror: [Errno 8] nodename nor servname provided, or not known

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/Users/ahegshetye/Code/data-warehouse/rs_vacuum/vacuum_analyze_rs.py", line 45, in <module>
    VacuumAnalyzeRedshift().vacuum_analyze()
  File "/Users/ahegshetye/Code/data-warehouse/rs_vacuum/vacuum_analyze_rs.py", line 16, in __init__
    self.conn = redshift_connector.connect(
  File "/Users/ahegshetye/Code/data-warehouse/rs_vacuum/venv/lib/python3.8/site-packages/redshift_connector/__init__.py", line 335, in connect
    return Connection(
  File "/Users/ahegshetye/Code/data-warehouse/rs_vacuum/venv/lib/python3.8/site-packages/redshift_connector/core.py", line 585, in __init__
    raise InterfaceError("communication error", e)
redshift_connector.error.InterfaceError: ('communication error', gaierror(8, 'nodename nor servname provided, or not known'))

Process finished with exit code 1

[Brooke-white]

Thank you @aseem-poloniex ,

I've been unable to reproduce this failure locally. I took the following steps to setup using IAM from the default profile.

1. Place temporary IAM credentials in ~/.aws/credentials
2. Executed the following script.

Also, you shouldn't need to pass user or password to redshift_connector.connect(...), assuming you don't intend to pass and values.

import redshift_connector

with redshift_connector.connect(
        iam=True,
        database='dev',
        db_user='awsuser',
        cluster_identifier='redshift-cluster-1',
        profile='default'
) as conn:
    with conn.cursor() as cursor:
        cursor.execute('select 1')
        data = cursor.fetchall()
        print(data)

>>> ([1],)

The contents of ~/.aws/credentials

[default]
region=us-east-2
AWS_ACCESS_KEY_ID=xxx
AWS_SECRET_ACCESS_KEY=xxx
AWS_SESSION_TOKEN=xxx

Based on where this error is occurring, the IAM authentication has completed (i.e. hostname, port, and IAM user have been retrieved) and and the problem is with creating a connection to the hostname for cluster (which is returned during the authentication process), which makes me think this is an issue with the IAM role's configuration or the DNS responder on your Mac. Usually when there is an issue with the IAM role, an error about connection timeout is populated, but I figure this would be good to double check.

I'd recommend trying the following:

1. verify the IAM role has the proper definition to provide your machine access
2. (if possible), try connecting to the cluster as a native DB user (i.e. using DB username/password host and port). If you're unable to do so, this indicates there's another issue at play here that's preventing you from accessing the cluster (e.g. misconfigured IAM, VPN required, etc.).
3. (if possible), set a breakpoint in core.py where the exception is raised. Look to the variables host and port. Are they as expected? What happens if you execute telent <host> <port>?
4. Restart your Mac's DNS responder

If you continue to see issue, please generate the debug logs using the instructions here and provide them in this issue so I can investigate further.

[aseem-poloniex]

1. Yes
2. Yes it works with raw username and password
3. Putting breakpoint revealed that host in core.py was different from actual redshift host. Further investigation showed that region in my ~/.aws/credentials and ~/.aws/config was different from the region where redshift cluster exist. So I set those regions in my credentials and config file to match the redshift region and it worked.