[SAM] Try using RDS Proxy with Lambda (Python) [user/pass, IAM authentication]

RDS Proxy, which was announced in July 2020 and read a big topic, but surprisingly there was not much information such as IAM authentication and setting method using CloudFormation, so I wrote it as an article.

This time, I used Python (pymysql) to build both user/pass authentication and IAM authentication with CloudFormation using AWS SAM.

If you explain everything, it will be long, so please refer to the following repository for the parts omitted in the article. https://github.com/yumemi-dendo/sample-aws-rds-proxy

environment

SAM CLI:1.15.0 Python:3.8

Authentication with user/pass

Authentication with user/pass is the connection method mainly used when the requirements for IAM authentication are not met. If you expect to create more than 20-200 new connections per second, you should do this.

Also, since it is the same as the normal connection method to RDS, it is also a method with information if you look for it. Simply get the DB credentials from Secrets Manager and use them to connect to RDS.

RDS Proxy setup

See the Sample Repository (https://github.com/yumemi-dendo/sample-aws-rds-proxy/blob/main/sample-aws-rds-proxy/template.yaml#L91) for network and RDS setup.

You need to allow the RDS Proxy to access the Secrets Manager that stores your DB's credentials. So, in addition to AWS :: RDS :: DBProxy, create an IAM role and grant read permissions to Secrets Manager, which stores the credentials of the MySQL user who grants access to RDS Proxy. This time, we allow two types of users, root user and lambda user.

Link the last defined RDS Proxy to RDS with AWS :: RDS :: DBProxyTargetGroup and you're done.

I'll omit it in this article, but don't forget to pass the Security Group path between RDS Proxy-> RDS.

template.yaml


  RDSProxyRole:
    Type: AWS::IAM::Role
    Properties:
      AssumeRolePolicyDocument:
        Version: 2012-10-17
        Statement:
          - Effect: Allow
            Principal:
              Service: rds.amazonaws.com
            Action: sts:AssumeRole
      Policies:
        - PolicyName: AllowGetSecretValue
          PolicyDocument:
            Version: 2012-10-17
            Statement:
              - Effect: Allow
                Action:
                  - secretsmanager:GetSecretValue
                  - secretsmanager:DescribeSecret
                Resource:
                  - !Ref RDSSecretAttachment
                  - !Ref RDSLambdaUserPassword

  RDSProxy:
    Type: AWS::RDS::DBProxy
    Properties:
      DBProxyName: sample-rds-proxy-for-mysql
      EngineFamily: MYSQL
      RoleArn: !GetAtt RDSProxyRole.Arn
      Auth:
        - AuthScheme: SECRETS
          SecretArn: !Ref RDSSecretAttachment
          IAMAuth: DISABLED
        - AuthScheme: SECRETS
          SecretArn: !Ref RDSLambdaUserPassword
          IAMAuth: DISABLED
      VpcSecurityGroupIds:
        - !Ref RDSProxySecurityGroup
      VpcSubnetIds:
        - !Ref PrivateSubnet1
        - !Ref PrivateSubnet2

  RDSProxyTargetGroup:
    Type: AWS::RDS::DBProxyTargetGroup
    DependsOn:
      - RDSInstance
    Properties:
      DBProxyName: !Ref RDSProxy
      DBInstanceIdentifiers:
        - !Ref RDSInstance
      TargetGroupName: default
      ConnectionPoolConfigurationInfo:
        ConnectionBorrowTimeout: 120
        MaxConnectionsPercent: 90
        MaxIdleConnectionsPercent: 10

Lambda setup

Obtain the DB authentication information from Secrets Manager and use it to configure Lambda that can access the RDS Proxy.

First, set VpcConfig and AWS LambdaVPCAccessExecutionRole to run Lambda on your VPC, and give Policies read permissions for Secrets Manager.

Also, since Lambda is executed on the VPC this time, it is necessary to prepare a VPC endpoint and configure the network settings such as Security Group in order to access Secrets Manager from the VPC. (See Repository because this is also omitted.)

All you have to do is add the Lambda Security Group to the RDS Proxy Security Group and you're done.

template.yaml


  RDSProxySecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      VpcId: !Ref VPC
      GroupDescription: "Security Group for RDS Proxy"
      SecurityGroupIngress:
        - IpProtocol: "tcp"
          FromPort: !Ref RDSDBPort
          ToPort: !Ref RDSDBPort
          SourceSecurityGroupId: !Ref EC2SecurityGroup
        - IpProtocol: "tcp"
          FromPort: !Ref RDSDBPort
          ToPort: !Ref RDSDBPort
          SourceSecurityGroupId: !Ref FunctionSecurityGroup

  #
  # Lambda
  #
  FunctionSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    Properties:
      GroupDescription: "Lambda Function Security Group"
      VpcId: !Ref VPC

  GetUserWithDBPassFunction:
    Type: AWS::Serverless::Function
    Properties:
      CodeUri: functions/get_user_with_db_pass/
      VpcConfig:
        SecurityGroupIds:
          - !Ref FunctionSecurityGroup
        SubnetIds:
          - !Ref PrivateSubnet1
          - !Ref PrivateSubnet2
      Environment:
        Variables:
          RDS_PROXY_ENDPOINT: !GetAtt RDSProxy.Endpoint
          RDS_SECRET_NAME: "sample-rds-lambda-user"
          DB_NAME: "sample_rds_proxy"
      Policies:
        - Version: 2012-10-17
          Statement:
            - Effect: Allow
              Action: secretsmanager:GetSecretValue
              Resource: !Ref RDSLambdaUserPassword
        - AWSLambdaVPCAccessExecutionRole

Lambda code implementation

Implement a Lambda that gets the DB credentials from Secrets Manager and uses them to access the RDS via the RDS Proxy.

When using user/pass, just set the username and password as you would normally access RDS. Don't forget to point the host to the RDS Proxy endpoint. (I'm using pymysql this time, but the same is true for mysql.connector.)

app.py


import sys
import os
import boto3
from botocore.client import Config
import json
import pymysql
import logging

logger = logging.getLogger(__name__)

RDS_SECRET_NAME = os.environ['RDS_SECRET_NAME']
RDS_PROXY_ENDPOINT = os.environ['RDS_PROXY_ENDPOINT']
DB_NAME = os.environ['DB_NAME']


def lambda_handler(event, context):
    """Get data from RDS using DB credentials stored in SecretsManager.
    """
    try:
        config = Config(connect_timeout=5, retries={'max_attempts': 0})
        client = boto3.client(service_name='secretsmanager',
                              config=config)

        get_secret_value_response = client.get_secret_value(SecretId=RDS_SECRET_NAME)
    except Exception:
        logger.error("An unknown error has occurred.")
        raise

    rds_secret = json.loads(get_secret_value_response['SecretString'])


    try:
        conn = pymysql.connect(
            host=RDS_PROXY_ENDPOINT,
            user=rds_secret['username'],
            passwd=rds_secret['password'],
            db=DB_NAME,
            connect_timeout=5,
            cursorclass=pymysql.cursors.DictCursor
        )
    except Exception as e:
        logger.error("An unknown error has occurred.")
        logger.error(e)
        sys.exit()

    with conn.cursor() as cur:
        cur.execute('SELECT id, name FROM users;')
        results = cur.fetchall()

    return results

IAM certification

IAM authentication eliminates the need to access Secrets Manager on the Lambda side, which is both authoritative and cost effective. However, please note that IAM authentication has a limit on the number of new connections that can be created per second. (There was no description in the document regarding the specific limit number.)

RDS Proxy setup

When performing IAM authentication with RDS Proxy, it is necessary to enable TLS/SSL and enable IAM authentication of the associated Secrets Manager. So set RequireTLS to True and Auth's IAMAuth to REQUIRED.

Other settings are the same as for user/pass.

template.yaml


  RDSProxyWithIam:
    Type: AWS::RDS::DBProxy
    Properties:
      DBProxyName: sample-rds-proxy-for-mysql-with-iam
      EngineFamily: MYSQL
      RequireTLS: True
      RoleArn: !GetAtt RDSProxyRole.Arn
      Auth:
        - AuthScheme: SECRETS
          SecretArn: !Ref RDSLambdaUserPassword
          IAMAuth: REQUIRED
      VpcSecurityGroupIds:
        - !Ref RDSProxySecurityGroup
      VpcSubnetIds:
        - !Ref PrivateSubnet1
        - !Ref PrivateSubnet2

Lambda setup

It's basically the same as user/pass, but instead of Secrets Manager it grants an IAM policy for the database. https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html

Action allows rds-db: connect and specifies the database user as the resource.

The naming convention is as follows. arn:aws:rds-db:{region}:{account-id}:dbuser:{DbiResourceId}/{db-user-name}

If you actually enter the value, it will be as follows. arn:aws:rds-db:eu-west-1:414867676510:dbuser:prx-07af81c332474cf27/lambda

template.yaml


  GetUserWithIamFunction:
    Type: AWS::Serverless::Function
    Properties:
      CodeUri: functions/get_user_with_iam/
      VpcConfig:
        SecurityGroupIds:
          - !Ref FunctionSecurityGroup
        SubnetIds:
          - !Ref PrivateSubnet1
          - !Ref PrivateSubnet2
      Environment:
        Variables:
          RDS_PROXY_ENDPOINT: !GetAtt RDSProxyWithIam.Endpoint
          RDS_PROXY_PORT: !Ref RDSDBPort
          RDS_USER: "lambda"
          DB_NAME: "sample_rds_proxy"
      Policies:
        - Version: 2012-10-17
          Statement:
            - Effect: Allow
              Action: rds-db:connect
              Resource: "arn:aws:rds-db:eu-west-1:414867676510:dbuser:prx-07af81c332474cf27/lambda"
        - AWSLambdaVPCAccessExecutionRole

Lambda code implementation

Use generate_db_auth_token instead of getting credentials from Secrets Manager. generate_db_auth_token Generates an authentication token used to connect to the database using IAM credentials.

Also, IAM authentication requires a TLS/SSL connection, so save the certificate in a location that can be referenced from app.py. https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/rds-proxy.html#rds-proxy-security.tls

When using RDS Proxy, Amazon root CA 1 trust store is required, so obtain it from the following URL. https://www.amazontrust.com/repository/AmazonRootCA1.pem

Finally, load the authentication token and certificate when connecting with pymysql and you're done.

app.py


import sys
import os
import boto3
import pymysql
import logging

logger = logging.getLogger(__name__)

RDS_PROXY_ENDPOINT = os.environ['RDS_PROXY_ENDPOINT']
RDS_PROXY_PORT = os.environ['RDS_PROXY_PORT']
RDS_USER = os.environ['RDS_USER']
REGION = os.environ['AWS_REGION']
DB_NAME = os.environ['DB_NAME']

rds = boto3.client('rds')


def lambda_handler(event, context):
    """Get data from RDS via RDS Proxy with IAM authentication.
    https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.Python.html

    TLS/See the following URL for SSL
    https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/rds-proxy.html#rds-proxy-security.tls
    """
    password = rds.generate_db_auth_token(
        DBHostname=RDS_PROXY_ENDPOINT,
        Port=RDS_PROXY_PORT,
        DBUsername=RDS_USER,
        Region=REGION
    )

    try:
        conn = pymysql.connect(
            host=RDS_PROXY_ENDPOINT,
            user=RDS_USER,
            passwd=password,
            db=DB_NAME,
            connect_timeout=5,
            cursorclass=pymysql.cursors.DictCursor,
            ssl={'ca': 'AmazonRootCA1.pem'}
        )
    except Exception as e:
        logger.error("An unknown error has occurred.")
        logger.error(e)
        sys.exit()

    with conn.cursor() as cur:
        cur.execute('SELECT id, name FROM users;')
        results = cur.fetchall()

    return results

By the way, if you use mysql.connector, you don't need a certificate and you can connect as it is. (I haven't been able to find out why, but maybe the certificate is also included in the library ...?) https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.Python.html

Finally

It was really hard to find because there was no information around IAM authentication and CloudFormation. In the end, I read every corner of the official documentation and managed to get it working.

Although the performance is slightly lower than connecting directly to RDS, it is easy unless you solve the problem of connection with Lambda, which has been a problem until now, and use a super-large RDS instance type in terms of price, so other than API Gateway etc. I think that it is at a level that can be used even for event execution and short batch processing such as every 5 to 10 minutes.

However, since there is almost no information about quotas around RDS Proxy for practical use, it seems that you need to contact AWS support.

Reference material

Sample Repository

Use Amazon RDS Proxy with AWS Lambda (https://aws.amazon.com/jp/blogs/news/using-amazon-rds-proxy-with-aws-lambda/) Manage connections with Amazon RDS Proxy (https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/rds-proxy.html#rds-proxy-security.tls) Connecting to a DB Instance Using AM Authentication and the AWS SDK for Python (Boto3) (https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.Connecting.Python.html) Create and use an IAM policy for IAM database access (https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/UsingWithRDS.IAMDBAuth.IAMPolicy.html) IAM Role-Based Authentication from Serverless Applications to Amazon Aurora (https://aws.amazon.com/jp/blogs/news/iam-role-based-authentication-to-amazon-aurora-from-serverless-applications/) How do I allow a user to authenticate to an Amazon RDS MySQL DB instance using IAM credentials? ](https://aws.amazon.com/jp/premiumsupport/knowledge-center/users-connect-rds-iam/) [Long-awaited worldwide] Securely connect to RDS of Public Access from Lambda Python with IAM authentication (+ SSL)

Recommended Posts

[SAM] Try using RDS Proxy with Lambda (Python) [user/pass, IAM authentication]
Passwordless authentication with RDS and IAM (Python)
[AWS] Try adding Python library to Layer with SAM + Lambda (Python)
Create API with Python, lambda, API Gateway quickly using AWS SAM
Try IAM Database Authentication from Python
Try mathematical formulas using Σ with python
Try using Python with Google Cloud Functions
Setting up Basic authentication using Python @Lambda
Try assigning or switching with Python: lambda
Setting up Digest authentication using Python @Lambda
Achieve Basic Authentication with CloudFront Lambda @ Edge with Python 3.8
Serverless application with AWS SAM! (APIGATEWAY + Lambda (Python))
Export RDS snapshot to S3 with Lambda (Python)
Try scraping with Python.
Pass the authentication proxy through communication using python urllib3
Try projective transformation of images using OpenCV with Python
Try using Tweepy [Python2.7]
Try encryption / decryption using OpenSSL key with Python3 pow function
Operate TwitterBot with Lambda, Python
Try using PythonTex with Texpad.
[S3] CRUD with S3 using Python [Python]
[Python] Try using Tkinter's canvas
BASIC authentication with Python bottle
Using Quaternion with Python ~ numpy-quaternion ~
Try Python output with Haxe 3.2
[Python] Using OpenCV with Python (Basic)
Try running Python with Try Jupyter
Try face recognition with Python
Try using folium with anaconda
Using OpenCV with Python @Mac
Send using Python with Gmail
I tried ChatOps with Slack x API Gateway x Lambda (Python) x RDS
Complement python with emacs using company-jedi
Harmonic mean with Python Harmonic mean (using SciPy)
Try scraping with Python + Beautiful Soup
[Python] Using OpenCV with Python (Image Filtering)
Summary if using AWS Lambda (Python)
Using Rstan from Python with PypeR
Authentication using tweepy-User authentication and application authentication (Python)
[Python] Using OpenCV with Python (Image transformation)
Try to operate Facebook with Python
Face detection with Lambda (Python) + Rekognition
[Python] Using OpenCV with Python (Edge Detection)
Try singular value decomposition with Python
[Python] Use Basic/Digest authentication with Flask
Try using LevelDB in Python (plyvel)
Using Lambda with AWS Amplify with Go
Try face recognition with python + OpenCV
Try using Python argparse's action API
Notes on using rstrip with python.
Try using the Python Cmd module
Try frequency control simulation with Python
Notify HipChat with AWS Lambda (Python)
Use PostgreSQL with Lambda (Python + psycopg2)
Try using Python's networkx with AtCoder
Try using Leap Motion in Python
When using MeCab with virtualenv python
Precautions when using six with Python 2.5
Try using Amazon DynamoDB from Python
Try to make foldl and foldr with Python: lambda. Also time measurement
Try to poke DB on IBM i with python + JDBC using JayDeBeApi