DEV Community

Cover image for Connect Postgres Database Using Lambda Function
🚀 Vu Dao 🚀
🚀 Vu Dao 🚀

Posted on • Edited on

Connect Postgres Database Using Lambda Function

#aws #devops #postgres #lambda

Use slackbot to send SQS message which trigger lambda function for executing SQL command on database

Alt Text

This post focuses on creating SQS queue and lambda function (not how to create a slackbot)

1. Create SQS queue using CDK (Cloud Development Toolkit)

Source: https://github.com/vumdao/create-db-user/tree/master/sqs_stack 

└── sqs_stack
    ├── app.py
    ├── cdk.json
    ├── README.md
    ├── requirements.txt
    ├── setup.py
    ├── source.bat
    └── sqs_stack
        ├── __init__.py
        └── sqs_stack_stack.py
Enter fullscreen mode Exit fullscreen mode

Notes:

  • SQS name: create-db-account
  • SQS Default visibility timeout must be equal or higher than the timeout of the lambda function which is subscribed to
  • Deploy SQS 
cdk deploy
Enter fullscreen mode Exit fullscreen mode

Alt Text

2. Create Lambda function which is triggered by the SQS queue using AWS Chalice

Source: https://github.com/vumdao/create-db-user/tree/master/lambda 

├── lambda
│   ├── app.py
│   ├── policy
│   ├── region.env
│   └── requirements.txt
Enter fullscreen mode Exit fullscreen mode

The lambda function receive SQS message to execute SQL of creating new user on RDS 

chalice new-project create-db-user
Enter fullscreen mode Exit fullscreen mode

Set VPC for Lambda Function same with RDS, AWS Chalice bases on current region and the subnet ID to detect VPC, security is a must for using subnet-ID 

      "subnet_ids": [
         "subnet-0f6ea4292ab9a63db",
         "subnet-00d29b42b2e17b5b5"
      ],
      "security_group_ids": ["sg-00668399e4bdd462e"]
Enter fullscreen mode Exit fullscreen mode

Disable mange IAM role from AWS Chalice to control the policy of the role 

      "manage_iam_role": false,
      "iam_role_arn": "arn:aws:iam::111111111111:role/create-db-user-dev",
Enter fullscreen mode Exit fullscreen mode

Policy 

    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogGroup",
                "logs:CreateLogStream",
                "logs:PutLogEvents"
            ],
            "Resource": "arn:*:logs:*:*:*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "sqs:ReceiveMessage",
                "sqs:DeleteMessage",
                "sqs:GetQueueAttributes"
            ],
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "ec2:DescribeNetworkInterfaces",
                "ec2:CreateNetworkInterface",
                "ec2:DeleteNetworkInterface",
                "ec2:DescribeInstances",
                "ec2:AttachNetworkInterface"
            ],
            "Resource": "*"
        }
    ]
}
Enter fullscreen mode Exit fullscreen mode

Lambda does not resolve hostname directly so RDS hostname is resolved by using socket function 

host=socket.gethostbyname(DB_HOST)
Enter fullscreen mode Exit fullscreen mode

Deploy the lambda 

chalice deploy
Enter fullscreen mode Exit fullscreen mode

Alt Text

Alt Text

Alt Text

Test

Alt Text

Notes:

  • Lambda function will delete SQS message if there's no issue.
  • The SQS message will leave there until it's Default visibility timeout so should handle any exception to delete the message

Top comments (1)

Collapse
 
routinggames profile image
Duy Nguy3n •

cool, thanks for sharing.

Read next

razaulhaque1334 profile image

Software knowledge

Md. Razaul Haque -

tanushree_aggarwal profile image

Dr. Swami Sivasubramanian's Keynote - under 5 minutes

Tanushree Aggarwal -

michael_flanagan_red profile image

Building the Backbone: Entities Part 2, Agent

Michael Flanagan -

dayjournal profile image

Create Map Images Using the Static Map Function of Amazon Location Service v2

Yasunori Kirimoto -