How can I hide or encrypt my database credentials in AWS Data Pipeline?

Last updated: 2021-03-19

I want to hide or encrypt my database credentials in AWS Data Pipeline.

Short description

Data pipeline encrypts the database credentials on transfer. However, when you access the pipeline during export, the pipeline JSON definition password is visible.

You can mask your database credentials in the pipeline by doing one of the following:

  • Create and use tags to restrict AWS Identity and Access Management (IAM) users from viewing your password.
  • Create a custom Amazon Machine Image (AMI) by exporting the database credentials as environment variables.
  • Use AWS Secrets Manager to store your database password in the pipeline.

Resolution

Tagging

You can grant your AWS Identity Access Management (AWS IAM) users full or restricted access to pipelines based on tags.

To restrict IAM users from viewing the password, create and attach an IAM policy that allows the use of read-only Data Pipeline actions, such as Describe, GetPipelineDefinition, and QueryObjects. For more information, see Example policies for AWS Data Pipeline.

Custom AMI

You can secure your database credentials in the pipeline by doing the following:

1.    Export your database user name and password as environment variables on your Amazon Elastic Compute Cloud (Amazon EC2) instance: Run the following command to export the database credentials as environment variables on your Amazon EC2 instance:

sudo vi /etc/profile
export USERNAME=awsuser
export PASSWORD=

2.    Create a custom AMI from the Amazon EC2 instance.

3.    Pass this variable in a PostgreSQL connection method to the database instance as a ShellCommandActivity: Define the following ShellCommandActivity using the Data Pipeline Architect:

psql "host=redshiftendpoint port=5439 dbname=Dbname user=$USERNAME password=$PASSWORD" -c "select 1;"

AWS Secrets Manager

The most secure way to encrypt your database credentials in the pipeline is to do the following:

  1. Use the AWS Secrets Manager to store your password.
  2. Install PostgreSQL to connect to Redshift database instance or SQL Workbench to connect to other database instances.

You can use AWS Secrets Manager to store the password as an environment variable. You can then pass this variable in a PostgreSQL connection method to the database instance as a ShellCommandActivity. For more information, see Connect to your cluster by using the psql tool.

1.    Create and store your Redshift database credentials in AWS Secrets Manager.

2.    Attach the following policy to the DatapipelineDefaultResource role to grant List/Read access to the secret stored in AWS Secret Manager:

{
"Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "SecretPolicytoretrievesecretvalue",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetSecretValue",
                "secretsmanager:DescribeSecret",
                "secretsmanager:ListSecretVersionIds"
            ],
            "Resource": "arn:aws:secretsmanager:us-east-1:1111222233334444:secret:mysecret/secret-10aaPx"
        }
    ]
}

Replace the following in the above policy:

  • us-east-1 with the Region where your secret is present
  • 1111222233334444 with your AWS account ID
  • mysecret with the name of your virtual folder
  • secret-10aaPx with the name of your secret

3.    Define the following ShellCommandActivity using the Data Pipeline Architect:

sudo yum -y install aws-cli jq postgresql95-server.x86_64 postgresql95-contrib.x86_64 postgresql95.x86_64 postgresql95-libs.x86_64 && export USERNAME=`aws secretsmanager get-secret-value --secret-id mysecret/secret --region regionname | jq -r '.SecretString' | cut -f4 -d \"\\\"\"` && export AWS_REGION=region_name && export PASSWORD=`aws secretsmanager get-secret-value --secret-id mysecret/secret --region region_name | jq -r '.SecretString' | cut -f8 -d \"\\\"\"` && psql "host=redshiftendpointname port=5439 dbname=sampledbname user=$USERNAME password=$PASSWORD" -c "select 1";

Did this article help?


Do you need billing or technical support?