How to connect AWS Lambda to a Database in a Private subnet in a VPC ?
Tags: aws, lambda, database, vpc
Published: 2023-07-24 09:56:07

**<u>Overview</u>**
By Default, lambda function is launched outside our VPC. Therefore it can not access resources such as RDS, ElastiCache, internal ALB, etc in the VPC. So in order to allow lambda access our private resources, we have to define VPC ID, subnets, Security groups. Lambda will create elastic network interface(ENI) in private subnet which allows it connectivity.

In this small project we will understand
1)AWS lambda connectivity with the database in a private subnet.
2)How to install and use MariaDB database server on EC2 instance.
3)How to store and use the Database credentials securely in AWS secret Manager.
4)How to add pymysql library as lambda layer.

**Note:** You can simple launch RDS databse in private subnet instead of installing it on EC2 instance. But here I wanted to try it on EC2(less costly also).

**<u>Prerequisite</u>**
1)AWS Account.
2)Little experience with Python.
3)Understanding of VPC,Subnets,Security Groups,etc.
4)Familiarity with AWS lambda.
Thats it..Lets Start.

**<u>Step1: Set up VPC:</u>**
To create a VPC use the terraform script. 
_git clone https://github.com/pramodbs543/Terraform/tree/main/Simple_VPC_
Here You have to just 1)store the Access key and Secret key as environment variables.(Shown in providers.tf file) 2)Change the Region in terraform.tfvars file.**3)By default the backend.tf file stores tfstate file in S3 backend. You have to create respective S3 bucket and Dynamo DB for this. But if you want to skip this, simply delete the backend.tf file.** Apply following commands.

terraform init
terraform apply -auto-approve

VPC is ready.

**<u>Step 2 Creat an IAM Role: </u>**
For this We need to create an IAM role first with AmazonSSMManagedInstanceCore policy attached. This IAM role allows us to connect with EC2(For installing DB) even if the EC2 is in private subnet and has no key pair nor public IP.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/e3pe1c2u8jvuezno33cd.png)


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/acwwc3o1g2018c17t24n.png)

Search for AmazonSSMManagedInstanceCore policy and attach this to the role. 

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3sds71fpc9rshmi7j0wb.png)
Give a name to the role and create role.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/yqp4u2h0cixsi6zg4qaf.png)

**<u>Step 3: Create Security groups:</u>**
1)Security group for Lambda:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/f16xntn3wr5yztwjwpfo.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/im098skyaslowe992x7b.png)
2)Security group for DB.
In inbound rule, for MYSQL/Aurora type give source as lambda SG.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a988n4ow5d9md61pfbs8.png)


<u>**Step 4:Launch an EC2 instance in private subnet of VPC:**</u>

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ydxsnx0f7hmdsd0xja0m.png)
Select ubuntu as an AMI, t2.medium, Proceed without a key pair. Select db-sg as security group we created. Keep Public IP disabed. Select the VPC in network section and select the private subnet.
in Advance Details select the IAm role we created.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wot44u10xknhl1v6kga2.png)
And launch the instance.

**<u>Step 5: install MariaDB server on the EC2:</u>**
Connect to the EC2.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tw2u3745kuerxsv2gsj2.png)
Click in Session manager and click connect.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/79csfbw8x9rn3nq476eg.png)
Change to root directory.
`$ cd /`
This command updates all packages to the latest version

`sudo apt update -y `

This command installs MySQL server on your machine, it also creates a systemd service
`sudo apt install -y mariadb-server`

This command enables the service created in previous step
`sudo systemctl enable mariadb`

This command starts the MySQL server service on your Linux instance
`sudo systemctl start mariadb`

This command helps you to set root user password and improve your DB security
`sudo mysql_secure_installation`

Here, just hit enter as we have not set any password yet
`Enter current password for root (enter for none): `

Here Reply Y
`Switch to unix_socket authentication [Y/n]`

Here, reply with Y
`Change the root password? [Y/n] `

Enter new password
`New Password:`

Re-enter new password
`Re-enter new Password:`

Say Y
`Remove anonymous users? [Y/n]`

Say Y
`Disallow root login remotely? [Y/n]`

Say N, as we would need them for verification 
`Remove test database and access to it? [Y/n]` 

Say Y
`Reload privilege tables now? [Y/n]` 

Thanks for using MariaDB!

**Verify the connection with the DB**
`mysql -h localhost -u root -p`
Enter password.
Show Databases:
`show databases;`
Create Database:
`create database mydatabase;`
Switch to this Database:
`use mydatabase;`
Create a Table inside this database:

```
create table employees(eno int(10) primary key,ename varchar(20),esal double(10,2), eaddr varchar(30));

```

`describe employees;`

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rbhcaqrvdx4eij417tb4.png)

`exit;`

**Configure the DB to accept remote connections:**
127.0.0.1(localhost) makes your database accessible locally. We have to make it accessible remotely. 
`$ cd /etc/mysql/mariadb.conf.d`
 `$ sudo vi 50-server.cnf`

set bind-address=0.0.0.0 in place of 127.0.0.1

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ptxayoouqii1afroblqw.png)
`$ sudo systemctl restart mariadb`
`$ sudo ss -nlt`
 
You will see 0.0.0.0:3306
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ilf5mstwwuulh39wymnn.png)

Now, lets create an user which will have permissions to access mydatabase. Wildcard entry (%) allows this user01 to get connected from any host.
`$ mysql -h localhost -u root -p`
Enter root password we set earlier.

`CREATE USER 'user01'@'%' IDENTIFIED BY 'password543';`

```
GRANT ALL PRIVILEGES ON mydatabase.* to user01@'%' IDENTIFIED BY 'password543' WITH GRANT OPTION;
```
`FLUSH PRIVILEGES;`
exit

...Database is all set now.

**<u>Step 6: AWS Secret manager to store Db credentials:</u>**
Select Secrets Manager in AWS.
Store a new secret.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1utsbyjpo38odi95me1p.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/chj3l0y4u76oya9a45os.png)
Now, in the bottom, in server address enter private IP of EC2 instance.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/25c084ktqtbscepnrdsh.png)
Give the secret a name and hit Next.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/jmqbi2yppppu46ytchqg.png)
Hit NEXT again. Lastly hit Store the secret.(Note down your secret name. In this case "mariadbsecret")

**<u>Step 7: Create a Lambda Function.</u>**

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ermsucf52iohu8cbhap0.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fx1mbqlpx9dgenqhtj8g.png)

Attach a role which has AWSsecretmanager policy attached to it.(We need to access the secrets from secret manager)


Select the VPC now.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ltz54a2rzq03117hmzdf.png)
Select both subnets for high availability and lambda security group.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/45r5lgx552385eqbjgdz.png)

Hit on Create function.
**Add pymysql library as lambda layer:**
You will need python installed on your local Windows PC and the python version should match the Runtime version you selected for your Lambda function, in this case Python 3.10. You also need to install pip so that you can download the pymysql package.
Once you have pip installed on your PC, open the command prompt and navigate to your desired directory and run the following command.

`pip install --target ./python pymysql`
This will create a folder called python in the desired directory. You will then need to compress the python folder as a zip file so that we can upload it to AWS.
In the Lambda console window, select the Layers option from the left side menu and click ‘Create layer’. Give your layer a name of ‘pymysql’ and upload the zip file that you just created. Select Python 3.10 as a Compatible runtime and click ‘Create’.

Click on lambda function. Scroll to bottom. There you will find Layers. Add layer pymysql.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/rtzpp54fobfubn8tle0x.png)

Layer source as custom layers. Then from drop down select pymysql.

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/s45ki77c9zp612aeon83.png)

Now copy the code from 
https://github.com/pramodbs543/lambda-mysqlonEC2
employees.py for lambda function. event file for providing json input to the code. The entries in the even file will be stored in database.

Once you Test the code, you will get success message.
![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bpsdwdis6pj5ks6pgbmc.png)

The records in the database:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gcqvrbl9dg5q7djbyu9n.png)
The Architecture:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/8oc9yvmpvy3cmnux6qd7.jpg)