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.
3 Answers
14
votes
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.')
4
votes
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))
