Blog Cover

How to create a publicly accesible RDS PostgreSQL instance using CloudFormation

Author profile image
Aitor Alonso

Aug 30, 2022

8 min read

Lately, I'm starting to work more with AWS from a DevOps perspective, both professionally and for side projects. The last challenge I faced was to create, using CloudFormation, an RDS PostgreSQL instance that could be accessible from the internet. I'm new to CloudFormation, so before starting work on this, I had to do some research work. I used the knowledge acquired in years of work experience and continuous training in Computer Science and Engineering to achieve the most important and difficult part of our profession: to search on Google (sorry for the bad joke).

So after a quick search on Google, I didn't find anything really useful. Most articles and examples were too complex, with lots of layers, services, permissions and networking. I just wanted to create the simplest architecture for a bare-bones RDS instance that could be accessible from the internet.

It took me more time that I would like to admit to achieve the following working architecture, and just want to share with you my solution. Hopefully, I'll help others save the time I spent on this.

Cloudformation template

Here is the .yaml file for the CloudFormation template. I would explain each resource in detail below.

Warning!
This is not a typical three-tier AWS architecture, wherein the RDS instance would be placed in private subnets (data tier) and accessible only by the application tier. That represents best practices for AWS infrastructure and is not the case here. The following architecture is not recommended for production use, from a security standpoint.
Resources:
  PostgresRDSVPC:
    Type: AWS::EC2::VPC
    Properties:
      CidrBlock: "10.0.0.0/24"
      EnableDnsHostnames: true
      EnableDnsSupport: true
      Tags:
        - Key: Description
          Value: "VPC for postgres RDS"
  PostgresRDSInternetGateway:
    Type: AWS::EC2::InternetGateway
    Properties:
      Tags:
        - Key: Description
          Value: "Internet Gateway for accessing postgres RDS from internet"
  PostgresRDSInternetGatewayAttachment:
    Type: AWS::EC2::VPCGatewayAttachment
    Properties:
      InternetGatewayId: !Ref PostgresRDSInternetGateway
      VpcId: !Ref PostgresRDSVPC
  PostgresRDSMainVPCSubnet:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref PostgresRDSVPC
      AvailabilityZone: !Select [0, !GetAZs ""]
      CidrBlock: "10.0.0.0/28"
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Description
          Value: "Main public subnet for postgres RDS VPC"
  PostgresRDSSecondaryVPCSubnet:
    Type: AWS::EC2::Subnet
    Properties:
      VpcId: !Ref PostgresRDSVPC
      AvailabilityZone: !Select [1, !GetAZs ""]
      CidrBlock: "10.0.0.16/28"
      MapPublicIpOnLaunch: true
      Tags:
        - Key: Description
          Value: "Secondary public subnet for postgres RDS VPC"
  PostgresRDSPublicRouteTable:
    Type: AWS::EC2::RouteTable
    Properties:
      VpcId: !Ref PostgresRDSVPC
      Tags:
        - Key: Description
          Value: "Public Route Table for postgres RDS VPC"
  PostgresRDSDefaultPublicRoute:
    Type: AWS::EC2::Route
    DependsOn: PostgresRDSInternetGatewayAttachment
    Properties:
      RouteTableId: !Ref PostgresRDSPublicRouteTable
      DestinationCidrBlock: 0.0.0.0/0
      GatewayId: !Ref PostgresRDSInternetGateway
  PostgresRDSMainSubnetRouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PostgresRDSPublicRouteTable
      SubnetId: !Ref PostgresRDSMainVPCSubnet
  PostgresRDSSecondarySubnetRouteTableAssociation:
    Type: AWS::EC2::SubnetRouteTableAssociation
    Properties:
      RouteTableId: !Ref PostgresRDSPublicRouteTable
      SubnetId: !Ref PostgresRDSSecondaryVPCSubnet
  PostgresRDSDBSubnetGroup:
    Type: AWS::RDS::DBSubnetGroup
    Properties:
      DBSubnetGroupDescription: "DBSubnetGroup for postgres RDS"
      SubnetIds:
        - !Ref PostgresRDSMainVPCSubnet
        - !Ref PostgresRDSSecondaryVPCSubnet
  PostgresRDSVPCSecurityGroup:
    Type: AWS::EC2::SecurityGroup
    DependsOn: PostgresRDSVPC
    Properties:
      GroupName: "Access RDS from internet"
      GroupDescription: "Allows accessing RDS from internet"
      VpcId: !Ref PostgresRDSVPC
      SecurityGroupIngress:
        - IpProtocol: tcp
          FromPort: 0
          ToPort: 5432
          CidrIp: 0.0.0.0/0
      SecurityGroupEgress:
        - IpProtocol: tcp
          FromPort: 0
          ToPort: 5432
          CidrIp: 0.0.0.0/0
  PostgresRDS:
    Type: AWS::RDS::DBInstance
    Properties:
      AllocatedStorage: "20"
      BackupRetentionPeriod: 7
      CopyTagsToSnapshot: true
      DBInstanceClass: db.t4g.micro
      DBName: mydatabase
      DBSubnetGroupName: !Ref PostgresRDSDBSubnetGroup
      EnablePerformanceInsights: true
      Engine: postgres
      EngineVersion: "14.2"
      MasterUsername: databaseusername # Use SSM for a better security!!
      MasterUserPassword: mysecretpassword # Use SSM for a better security!!
      PerformanceInsightsRetentionPeriod: 7
      Port: "5432"
      PreferredBackupWindow: 02:00-03:59
      PreferredMaintenanceWindow: Mon:04:00-Mon:06:00
      PubliclyAccessible: true
      StorageType: gp2
      Tags:
        - Key: Description
          Value: "Postgres simple database"
      VPCSecurityGroups:
        - !Ref PostgresRDSVPCSecurityGroup


Outputs:
  PostgresRDSName:
    Value:
      Ref: PostgresRDS
    Export:
      Name: PostgresRDSName
  PostgresRDSEndpoint:
    Description: "Endpoint of the postgres RDS database"
    Value: !GetAtt PostgresRDS.Endpoint.Address
  PostgresRDSPort:
    Description: "Port of the postgres RDS database"
    Value: !GetAtt PostgresRDS.Endpoint.Port

Yep, the file is larger that I would like to, but this is the minimum working architecture I got working on my infrastructure. Let's explain each resource in detail.

The AWS::EC2::VPC is just the VPC where the RDS instance will be placed. We need a VPC in order to enable networking for the instance, and allow it to communicate. The most important parameters here are EnableDnsHostnames and EnableDnsSupport. Enabling these allows the VPC to resolve IPs outside the VPC.

The AWS::EC2::InternetGateway is the internet gateway that will be attached to the VPC. This is needed to allow the instance to communicate with the internet (i.e., to be publicly accessible). Then, we just need to attach this gateway to the previous VPC via the AWS::EC2::VPCGatewayAttachment resource.

Now, comes the VPC subnets. AWS requires the RDS to be placed in a subnet inside the VPC, and to have another VPC ready for replicas in a different availability zone. So here comes the two AWS::EC2::Subnet resources. It is important to enable the MapPublicIpOnLaunch parameter, otherwise the RDS instance there will not be able to be accessed from the internet. Double check the values in CidrBlock if you modified it in the VPN.

Also, a public route table, with a default public route to the internet, is needed to allow the RDS instance to leave the VPC. This way, it knows where to locate the internet gateway and route traffic. This is done via the AWS::EC2::RouteTable and AWS::EC2::Route resources. Then, we must associate the public route table to each subnet via two different AWS::EC2::SubnetRouteTableAssociation resources.

Before defining our RDS instance, we need to define a Security Group that will be attached to the VPC and work as a kind of firewall, allowing or denying connections. So in the AWS::EC2::SecurityGroup resource, we define and inbound and outbound rules (ingress and egress respectively) to allow all traffic, from/to anywhere (0.0.0.0/0) to the port where our PostgreSQL instance will be listening (in my case, the default 5432).

Finally, we can define the parameters for our RDS instance. I set some basic parameters, with the minimum gp2 available storage, and some free tier use and load monitoring. I also set the PreferredBackupWindow and PreferredMaintenanceWindow parameters to allow the RDS instance to be automatically backed up and restarted during maintenance.

As you can see, I directly put the database username and password in the template. This is a bad practice and a security problem, and you should always use AWS SSM to store them in secure parameter. I did it like this just to simplify the template for demonstration purposes. The way to access SSM parameters differs if you are using vanilla CloudFormation or using some framework above it, like serverless.

### Vanilla CloudFormation
MasterUsername: "{{resolve:ssm:postgresRdsUsername:1}}"
MasterUserPassword: "{{resolve:ssm-secure:postgresRdsPassword:1}}"
# We need to indicate the version of the parameter, here we are using version 1
# Also, the resolve differs if the parameter is a secureString

### Serverless Framework
MasterUsername: ${ssm:postgresRdsUsername}
MasterUserPassword: ${ssm:postgresRdsPassword~true}
# We need thee ~true to decrypt secureString parameters

This is everything you need for the simplest publicly accessible RDS infrastructure. I hope it helps you and serves you well, and also to save you time. Don't forget to check the warnings and recommendations I made along the article, as this a very simple demonstration that should be used as a reference, and should be improved from a security standpoint when implementing it.