Posted By in Uncategorized

Problem:
Need to reduce the overhead of user access management on AWS Redshift clusters.

Solution:

  • User commits DDL execution files to an AWS Codecommit repository.
  • The update to the Codecommit repository sends an event to AWS Lambda.
  • The Lambda function pulls down the source code and requests the connection information for the AWS Redshift Cluster from AWS Secret Manager.
  • The Lambda function will establish a connection to the Redshift cluster and run each DDL script in the order it was given via the commit message

For integration between Codecommit and Lambda we used external packages including pygit2

The logic we employed was simple, by reading the commit log we could tell which files were added/removed but we could not figure out what sequence the files had to be run in. To solve this issue, we enforced a strict commit message syntax to indicate the sequence in which to run the files.

Example of Commit message main text

-[ORDER]-
sql/files/ddl1.sql
sql/files/ddl2.sql
sql/files/ddl3.sql

We now have the run order of the files and attempt to run them using psycopg2.