Using IAM Authentication with Redshift

Managing user access to your Amazon Redshift database can be a headache, especially when it comes to maintaining individual user accounts and passwords. However, there’s a better way – you can leverage your existing AWS Identity and Access Management (IAM) permissions to generate temporary database credentials for your users.

By configuring your system to authenticate users based on their IAM credentials, you can eliminate the need to create and manage individual database user accounts. Users can simply log in to your Redshift database using their IAM credentials, and your system will generate the necessary database credentials on-the-fly.

This approach offers several benefits. It streamlines user management, as you can leverage your existing IAM permissions structure to control access to the database. It also enhances security, as you don’t have to worry about users sharing or mishandling database passwords.

The official AWS documentation is at Using IAM authentication to generate database user credentials.

In my particular use case, I had to connect to a Redshift database with temporary IAM authentication and there is one small wrinkle that cost me a couple of hours. I am documenting this together with the code in the hope that I might save someone else some time in the future.

Below is my python code

import boto3
from botocore.exceptions import ClientError
import psycopg
psycopg._encodings._py_codecs["UNICODE"] = "utf-8"
psycopg._encodings.py_codecs.update(
    (k.encode(), v) for k, v in psycopg._encodings._py_codecs.items()
)
from psycopg import OperationalError

def connect_to_the_database(db_user, cluster_identifier, db_name, db_host, db_port):

    print('Original DB user     : ', db_user)

    redshift = boto3.client('redshift', 'us-west-1')
    try:
        credentials = redshift.get_cluster_credentials(
            DbUser = db_user,
            DbName = db_name,
            ClusterIdentifier = cluster_identifier,
            DurationSeconds = 900,
            AutoCreate = False,
        )
    except ClientError as e:
        print('Error getting cluster credentials')
    
    db_password = credentials.get("DbPassword")
    db_user     = credentials.get("DbUser")

    print('Modified IAM DB user : ', db_user)
    print('IAM DB password      : ', db_password)

    connection_args = 'host={} port={} dbname={} user={} password={}'.format(
        db_host,
        db_port,
        db_name,
        db_user,
        db_password
    )
    try:
        conn = psycopg.connect(connection_args)
    except psycopg.Error as e:
        print('Error connecting to the database')

    return conn

#
## main processing
#
if __name__ == '__main__':
    db_user='my_db_user'
    cluster_identifier='my-redshift-cluster'
    db_name='test'
    db_host='my-redshift-cluster.xxx.us-west-1.redshift.amazonaws.com'
    db_port=5439
    conn = connect_to_the_database(db_user, cluster_identifier, db_name, db_host, db_port)

Note that in the main processing the user name is “my_db_user” and this is the name passed to the function “connect_to_the_database”. This is confirmed by the print statement at the start of the function:

print('Original DB user     : ', db_user)

My expectation was that the call to BOTO3’s get_cluster_credentials would return a password that I would use to connect to the database with. However, you also have to extract the modified user name from the returned “credentials” dictionary object to get the modified user name. This modified user name and temporary credentials are used to connect to the database.

The output of the program’s print statements is below:

Original DB user     :  my_db_user
Modified IAM DB user :  IAM:my_db_user
IAM DB password      :  JmRCSmqxEyw2Seo89mWUg2H1PG9pONROZJBXA==

Note the difference between the original DB user and the modified IAM DB user. The user that you want to connect to the database has to be prefixed with the text “IAM:”. This, IAM:my_db_user, is the user name that will appear in the stv_sessions, stv_inflight etc. views.