Connect to Redshift using Python using IAM Role

Question

I'am using sqlalchemy and psycopg2 to connect python to redshift.

engine = create_engine('postgresql://user:password@hostname:port/database_name')

I want to avoid using my password to connect to redshift and using IAM Role.

Mccarty

Answer 1

AWS offers a way to request temporary credentials for access to Redshift clusters. Boto3 implements get_cluster_credentials, allowing you to do something like the following. Ensure that you have followed the instructions here on setting up your IAM Users and Roles.

def db_connection():
    logger = logging.getLogger(__name__)

    RS_PORT = 5439
    RS_USER = 'myDbUser'
    DATABASE = 'myDb'
    CLUSTER_ID = 'myCluster'
    RS_HOST = 'myClusterHostName'

    client = boto3.client('redshift')

    cluster_creds = client.get_cluster_credentials(DbUser=RS_USER,
                                               DbName=DATABASE,
                                          ClusterIdentifier=CLUSTER_ID,
                                               AutoCreate=False)

    try:
      conn = psycopg2.connect(
        host=RS_HOST,
        port=RS_PORT,
        user=cluster_creds['DbUser'],
        password=cluster_creds['DbPassword'],
        database=DATABASE
      )
      return conn
    except psycopg2.Error:
      logger.exception('Failed to open database connection.')

Pancreatin

Answer 2

AWS provides no convenient wrapper for IAM creds in python like they do for their JDBC driver. You need to make a call to the GetClusterCredentials endpoint manually and then pass in the returned username and password to create_engine. Looks something like:

def get_redshift_credentials():
    role_creds = get_role_credentials()
    client = boto3.client(
        'redshift',
        region_name=CLUSTER_REGION,
        aws_access_key_id=role_creds['AccessKeyId'],
        aws_secret_access_key=role_creds['SecretAccessKey'],
        aws_session_token=role_creds['SessionToken'],
    )
    response = client.get_cluster_credentials(
        DbUser=PGUSER,
        ClusterIdentifier=CLUSTER_IDENTIFIER,
    )
    return response

creds = get_redshift_credentials()
engine = create_engine('postgresql://{creds.DbUser}:{creds.DbPassword}@hostname:port/database_name'.format(creds))

Minaret

The credential user are like ; IAM:username, and then create_engine seems to always regard that IAM is the username, is there any solution for this ? – Goles

I am getting the same issue – Colfin

Answer 3

Since some time ago AWS has a native Redshift connector for Python.

It supports connecting using IAM, given your IAM credentials allows you to call get-cluster-credentials.

Example:

import redshift_connector

conn = redshift_connector.connect(
    iam=True,
    database='dev',
    db_user='<username>', # the database user in call to get-cluster-credentials
    cluster_identifier='my-redshift-cluster', # identifier of your cluster
    profile='redshift_profile' # profile in ~./aws/config with correct permissions
 )

cursor = redshift_connector.Cursor = conn.cursor()
cursor.execute('SELECT 1;')

A nice feature of this connector is that it calls describe-clusters internally using the cluster_identifier, so you don't even need to specify host and port.

Gerbil

any ideas how you'd do this if your redshift cluster was in a different account to the one you're profile is in? – Revisal

Unfortunately not, have not worked with Redshift for some time and have no access to any accounts or clusters to try it out. But I would assume it works out of the box as long you have the correct permissions (including describe-clusters) on the cluster. – Gerbil

Answer 4

AWS IAM users are different from Redshift database users. Although Redshift is a (very distant) relative of postgres, it doesn't allow passwordless connections yet, afaik.

EDIT:

My answer is no longer applicable, check other answers for relevant code snippets.

Bryner

This is incorrect. You can (as of January 2018) call get_cluster_credentials to get temporary credentials to access a Redshift cluster. boto3.readthedocs.io/en/latest/reference/services/… – Pancreatin

Thanks, I added an edit to indicate my answer no longer applying. When I was answering back then, I didn't find this function. Either I'm bad at searching or it was added to boto some time later. – Bryner