Creating a RDS instance
Introduction
The manual setup steps taken from the AWS docs on Getting started with Amazon RDS.
Have been automated using the CDK.
Examples follows in Python.
3 stacks were created for this example.
-
NetworkStack [ Creates VPC, subnets, security groups, NAT gateway ]
-
EC2Stack [ Creates a new EC2 instance (webserver) ]
-
RDSDBStack [ Creates a new RDS instance ]
Extra resources.
The getting started guide uses a webserver to connect to the DB instance.
The guide provides instructions on manually configuring the webserver.
I’ve used Ansible to automate the configuration.
App.py
#!/usr/bin/env python3
from aws_cdk import core
from rds.RDSDBStack import RDSDBStack
from rds.NetworkStack import NetworkStack
from rds.EC2Stack import EC2Stack
props = {
'namespace':'RDS',
'vpc_name':'vpc-rds',
'instance_name':'rds-webserver',
'instance_type':'t2.small',
'instance_key':'cdk-dev',
'image_id':'ami-031a03cb800ecb0d5',
'wan_ip':'<your ip-address>',
'db_master_username': 'tutorial_user',
'db_instance_identifier':'tutorial-db-instance',
'db_instance_engine':'MYSQL'
}
env = core.Environment(region="<aws-region>",account="<aws-account>")
app = core.App()
network_stack = NetworkStack(app, f"{props['namespace']}-network",props,env=env)
ec2_stack = EC2Stack(app,f"{props['namespace']}-ec2",network_stack.outputs,env=env)
ec2_stack.add_dependency(network_stack)
rds_stack = RDSDBStack(app, f"{props['namespace']}-db",network_stack.output_props,env=env)
rds_stack.add_dependency(network_stack)
app.synth()
NetworkStack
from aws_cdk import (
core,
aws_ec2 as ec2,
aws_rds as rds
)
class NetworkStack(core.Stack):
def __init__(self, scope: core.Construct, id: str, props, **kwargs) -> None:
super().__init__(scope, id, **kwargs)
# Create VPC
vpc = ec2.CfnVPC(
self,
"tutorial-vpc",
cidr_block="10.0.0.0/16"
)
vpc.tags.set_tag(key="Name",value=props['vpc_name'])
core.CfnOutput(
self,
"vpc-id",
value=vpc.ref
)
# Create Routing table for private subnet
route_table_private = ec2.CfnRouteTable(
self,
"rtb-private",
vpc_id=vpc.ref
)
route_table_private.tags.set_tag(key="Name",value="RDS Private Routing Table")
# Create Routing table for public subnet
route_table_public = ec2.CfnRouteTable(
self,
"rtb-public",
vpc_id=vpc.ref
)
route_table_public.tags.set_tag(key="Name",value="RDS Public Routing Table")
# Create public subnet
public_subnet = ec2.CfnSubnet(
self,
"public_subnet_1",
cidr_block="10.0.0.0/24",
vpc_id=vpc.ref,
map_public_ip_on_launch=True,
availability_zone="eu-west-1a"
)
# Create Elastic ip
eip = ec2.CfnEIP(
self,
"elastic_ip",
)
# Create internet gateway
inet_gateway = ec2.CfnInternetGateway(
self,
"rds-igw",
tags=[core.CfnTag(key="Name",value="rds-igw")]
)
# Attach internet gateway to vpc
ec2.CfnVPCGatewayAttachment(
self,
"igw-attachment",
vpc_id=vpc.ref,
internet_gateway_id=inet_gateway.ref
)
# Create NAT gateway, attach elastic-ip, public subnet
nat_gateway = ec2.CfnNatGateway(
self,
"natgateway",
allocation_id=eip.attr_allocation_id,
subnet_id=public_subnet.ref,
)
# Create private subnet 1
private_subnet_1 = ec2.CfnSubnet(
self,
"private-subnet1",
cidr_block="10.0.1.0/24",
vpc_id=vpc.ref,
availability_zone="eu-west-1b"
)
# Create private subnet 2
private_subnet_2 = ec2.CfnSubnet(
self,
"private-subnet2",
cidr_block="10.0.2.0/24",
vpc_id=vpc.ref,
availability_zone="eu-west-1c"
)
public_subnet.tags.set_tag(key="Name",value="subnet-rds-public")
private_subnet_1.tags.set_tag(key="Name",value="subnet-rds-private-1")
private_subnet_2.tags.set_tag(key="Name",value="subnet-rds-private-2")
# Associate private subnet with the created routing table
ec2.CfnSubnetRouteTableAssociation(
self,
"rtb-assoc-priv001",
route_table_id=route_table_private.ref,
subnet_id=private_subnet_1.ref
)
ec2.CfnSubnetRouteTableAssociation(
self,
"rtb-assoc-priv002",
route_table_id=route_table_private.ref,
subnet_id=private_subnet_2.ref
)
ec2.CfnSubnetRouteTableAssociation(
self,
"rtb-assoc-public",
route_table_id=route_table_public.ref,
subnet_id=public_subnet.ref
)
# Create a new public route to use the internet gateway
ec2.CfnRoute(
self,
"public-route",
route_table_id=route_table_public.ref,
gateway_id=inet_gateway.ref,
destination_cidr_block="0.0.0.0/0",
)
# Create security groups
# public web server
webserver_sec_group = ec2.CfnSecurityGroup(
self,
"webserver-sec-group",
group_description="webserver security group",
vpc_id=vpc.ref,
)
# Restrict SSH port access to only yourself
ssh_ingress = ec2.CfnSecurityGroupIngress(
self,
"sec-group-ssh-ingress",
ip_protocol="tcp",
cidr_ip=props['wan_ip']+"/32",
from_port=22,
to_port=22,
group_id=webserver_sec_group.ref
)
# Allow http to internet
http_ingress = ec2.CfnSecurityGroupIngress(
self,
"sec-group-http-ingress",
ip_protocol="tcp",
from_port=80,
to_port=80,
cidr_ip="0.0.0.0/0",
group_id=webserver_sec_group.ref
)
webserver_sec_group.tags.set_tag(key="Name",value="sg-rds-webserver")
# Create security group for the RDS instance
db_sec_group = ec2.CfnSecurityGroup(
self,
"dbserver-sec-group",
group_description="DB Instance Security Group",
vpc_id=vpc.ref
)
db_sec_group.tags.set_tag(key="Name",value="sg-rds-db")
# Allow port 3306 only to the webserver in order to access MySQL
db_ingress = ec2.CfnSecurityGroupIngress(
self,
"sec-group-db-ingress",
ip_protocol="tcp",
from_port=3306,
to_port=3306,
group_id=db_sec_group.ref,
source_security_group_id=webserver_sec_group.ref
)
self.output_props = props.copy()
self.output_props['webserver_sg_id'] = webserver_sec_group.ref
self.output_props['public_subnet_id'] = public_subnet.ref
self.output_props['private_db_sg_id'] = db_sec_group.ref
# Create RDS subnet group
rds.CfnDBSubnetGroup(
self,
"rds_db_subnet_group",
db_subnet_group_description="RDS DB Subnet Group",
db_subnet_group_name="sgp-rds-db",
subnet_ids=[private_subnet_1.ref,private_subnet_2.ref],
)
core.CfnOutput(
self,
"output-db-sg-id",
value=self.output_props['private_db_sg_id']
)
@property
def outputs(self):
return self.output_props
EC2Stack
This stack creates the webserver ec2 instance.
from aws_cdk import (
core,
aws_ec2 as ec2,
)
class EC2Stack(core.Stack):
def __init__(self, scope: core.Construct, id: str, props, **kwargs) -> None:
super().__init__(scope, id, **kwargs)
ec2.CfnInstance(
self,
"ec2instance1",
subnet_id=props['public_subnet_id'],
security_group_ids=[props['webserver_sg_id']],
instance_type=props['instance_type'],
image_id=props['image_id'],
key_name=props['instance_key']
).tags.set_tag(key="Name",value="rds-webserver")
RDSDBStack
This stack creates the RDS instance.
from aws_cdk import (
core,
aws_rds as rds,
aws_ec2 as ec2,
aws_secretsmanager as sm
)
import json
class RDSDBStack(core.Stack):
def getDBEngine(self,engine):
if(engine == 'MYSQL'):
return rds.DatabaseInstanceEngine.MYSQL
if(engine == 'ORACLE-EE'):
return rds.DatabaseInstanceEngine.ORACLE_EE
def __init__(self, scope: core.Construct, id: str, props, **kwargs) -> None:
super().__init__(scope, id, **kwargs)
# The code that defines your stack goes here
engine = self.getDBEngine(props['db_instance_engine'])
db_master_username = {
"db-master-username": props['db_master_username']
}
# create new secret in SecretsManager
secret = sm.Secret(self,
"db-user-password-secret",
description="db master user password",
secret_name="db-master-user-password",
generate_secret_string=sm.SecretStringGenerator(
secret_string_template=json.dumps(db_master_username),
generate_string_key="db-master-user-password"
)
)
# create db instance
db_inst = rds.CfnDBInstance(
self,
"rds-instance",
engine=props['db_instance_engine'],
db_subnet_group_name=props['db_subnet_group_name'],
db_instance_identifier=props['db_instance_identifier'],
db_instance_class="db.t2.micro",
deletion_protection=False,
vpc_security_groups=[props['private_db_sg_id']],
allocated_storage="20",
master_username=props['db_master_username'],
master_user_password=secret.secret_value_from_json("db-master-user-password").to_string(),
db_name=props['db_name']
)
Deploy
cdk deploy RDS*
Ansible
The webserver requires the installation of httpd and php.
I created a playbook to automate the installation and configuration steps.
Tasks
File: web/tasks/main.yml
- include_vars: vars/main.yml
- name: Run yum upgrade
yum:
name: "*"
state: latest
- name: Install apache web server and PHP
yum:
name:
- httpd24
- php56
- php56-mysqlnd
- name: Start httpd service
service:
name: httpd
state: started
- name: Alter httpd service, to start on boot
shell: chkconfig httpd on
- name: Add www group
group:
name: www
state: present
- name: Add ec2-user to www group
user:
name: ec2-user
shell: /bin/bash
group: www
- name: Change ownership of /var/www
file:
path: /var/www
group: www
recurse: true
- name: Add read permissions to /var/www
file:
path: /var/www
recurse: true
mode: '2775'
- name: DB connection setup, step 1
become: true
become_user: ec2-user
file:
path: /var/www/inc
state: directory
- name: DB connection setup, step 2
become: true
become_user: ec2-user
file:
path: /var/www/inc/dbinfo.inc
state: touch
- name: Copy templates/SamplePage.php to /var/www/html
become: true
become_user: ec2-user
template:
src: templates/SamplePage.php
dest: /var/www/html
- name: Copy templates/SamplePage.php to /var/www/html
template:
src: templates/SamplePage.php
dest: /var/tmp
- name: Copy templates/dbinfo.inc.j2 to /var/www/html
template:
src: templates/dbinfo.inc.j2
dest: /var/tmp
Templates
File: web/templates/dbinfo.inc.j2
<?php
define('DB_SERVER', '{{ db_instance_endpoint }}');
define('DB_USERNAME', '{{ db_username }}');
define('DB_PASSWORD', '{{ db_master_password }}');
define('DB_DATABASE', '{{ db_name }}');
?>
File: web/templates/SamplePage.php
<?php include "../inc/dbinfo.inc"; ?>
<html>
<body>
<h1>Sample page</h1>
<?php
/* Connect to MySQL and select the database. */
$connection = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD);
if (mysqli_connect_errno()) echo "Failed to connect to MySQL: " . mysqli_connect_error();
$database = mysqli_select_db($connection, DB_DATABASE);
/* Ensure that the EMPLOYEES table exists. */
VerifyEmployeesTable($connection, DB_DATABASE);
/* If input fields are populated, add a row to the EMPLOYEES table. */
$employee_name = htmlentities($_POST['NAME']);
$employee_address = htmlentities($_POST['ADDRESS']);
if (strlen($employee_name) || strlen($employee_address)) {
AddEmployee($connection, $employee_name, $employee_address);
}
?>
<!-- Input form -->
<form action="<?PHP echo $_SERVER['SCRIPT_NAME'] ?>" method="POST">
<table border="0">
<tr>
<td>NAME</td>
<td>ADDRESS</td>
</tr>
<tr>
<td>
<input type="text" name="NAME" maxlength="45" size="30" />
</td>
<td>
<input type="text" name="ADDRESS" maxlength="90" size="60" />
</td>
<td>
<input type="submit" value="Add Data" />
</td>
</tr>
</table>
</form>
<!-- Display table data. -->
<table border="1" cellpadding="2" cellspacing="2">
<tr>
<td>ID</td>
<td>NAME</td>
<td>ADDRESS</td>
</tr>
<?php
$result = mysqli_query($connection, "SELECT * FROM EMPLOYEES");
while($query_data = mysqli_fetch_row($result)) {
echo "<tr>";
echo "<td>",$query_data[0], "</td>",
"<td>",$query_data[1], "</td>",
"<td>",$query_data[2], "</td>";
echo "</tr>";
}
?>
</table>
<!-- Clean up. -->
<?php
mysqli_free_result($result);
mysqli_close($connection);
?>
</body>
</html>
<?php
/* Add an employee to the table. */
function AddEmployee($connection, $name, $address) {
$n = mysqli_real_escape_string($connection, $name);
$a = mysqli_real_escape_string($connection, $address);
$query = "INSERT INTO EMPLOYEES (NAME, ADDRESS) VALUES ('$n', '$a');";
if(!mysqli_query($connection, $query)) echo("<p>Error adding employee data.</p>");
}
/* Check whether the table exists and, if not, create it. */
function VerifyEmployeesTable($connection, $dbName) {
if(!TableExists("EMPLOYEES", $connection, $dbName))
{
$query = "CREATE TABLE EMPLOYEES (
ID int(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(45),
ADDRESS VARCHAR(90)
)";
if(!mysqli_query($connection, $query)) echo("<p>Error creating table.</p>");
}
}
/* Check for the existence of a table. */
function TableExists($tableName, $connection, $dbName) {
$t = mysqli_real_escape_string($connection, $tableName);
$d = mysqli_real_escape_string($connection, $dbName);
$checktable = mysqli_query($connection,
"SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME = '$t' AND TABLE_SCHEMA = '$d'");
if(mysqli_num_rows($checktable) > 0) return true;
return false;
}
?>
Vars
File: web/vars/main.yml
db_instance_endpoint: <taken from AWS RDS>
db_username: <taken from AWS RDS>
db_master_password: <taken from AWS RDS>
db_name: <taken from AWS RDS>
Inventory
File: inventory
[webserver]
<public-ec2-ip-address> ansible_ssh_user=ec2-user ansible_ssh_private_key_file=<path to pem file>
Playbook
File: playbook.yml
- hosts: webserver
become: yes
become_user: root
roles:
- web
Running the playbook
ansible-playbook -i inventory playbook.yml
Validate
Verify that your web server successfully connects to your DB instance by opening a web browser and browsing to http://EC2 instance endpoint/SamplePage.php, for example: http://ec2-55-122-41-31.us-west-2.compute.amazonaws.com/SamplePage.php.
-
You can use SamplePage.php to add data to your DB instance. The data that you add is then displayed on the page.
-
To make sure that your DB instance is as secure as possible, verify that sources outside of the VPC can’t connect to your DB instance.
Destory / clean up resources
cdk destroy RDS*