Creating a Public Postgres RDS Instance with Secrets Manager in CDK

7 min readFeb 13, 2024

Introduction

In this article we will show how to create a Public Postgres RDS instance with Secrets Manager in CDK.

We will be using AWS CDK in Java to create this project but similar steps can be done in the language of your choice as well. The CDK code presented can be leveraged for other languages as the steps should be very similar.

Placing an RDS instance in a Public Subnet can still be protected by the Secrets Manager authentication and also the database security group can be updated to only allow access to certain IP addresses. If one places the RDS instance in a private subnet, it is likely that this will require a bastion host or some other mechanism to gain access to the database.

Business Case

The leprechauns love gold and to hide their treasure(sometimes at the end of rainbows). The want to build a treasure database to record their treasures and locations. This way they will not lose their treasure and can find it when they need it!

Architecture Diagram

PostgreSQL RDS instance deployed into a custom VPC

The above represents what will be deployed in this article. A custom VPC will be deployed with a public subnet and private subnet across 3 availability zones.

We are deploying a PostgreSQL instance within one public subnet. It will be configured to use Secrets Manager for it’s credentials. Additionally a VPC Endpoint will be deployed so that Lambdas can reach the Secrets Manager credentials to authenticate to the RDS instance. An article will be written to show how to deploy a Spring Boot Application on AWS Lambda once we have deployed the above configuration.

Building the CDK Project

Validate the Prerequisites (See the Appendix)

2. Create the CDK Project

cd c:\projects
mkdir javacdk-public_postgres
cd javacdk-public_postgres
cdk init app --language java

2. Open the project up in Eclipse

File -> Open Projects from File System

Your CDK Project should have the following structure based on the code found at https://github.com/collin-smith/javacdk-public_postgres. The key class to update is JavacdkPublicPostgresStack.java which can be found in the repo just listed.

Make sure you have your environment setup properly to deploy to the correct account, region etc.

cd c:\projects\javacdk-public_postgres
cdk bootstrap
cdk synth
cdk deploy

After deploying the CDK app with “cdk deploy” you will see something like the following:

Looking in the AWS Console

We can see that a postgres RDS instance has been created as follows:

Take note of the endpoint above as you will need that for when you want to log into the database with pgAdmin.

We can also see that a Secrets Manager Secret was created that holds the credentials to the RDS instance

Accessing the RDS instance with pgAdmin

Since we have allowed public access to the Postgres RDS instance we can log in from our desktops(See code block below). One can create more security for the RDS instance by placing it in a Private subnet but we will look at that in a later blog. In this situation, the endpoint can be protected by configuring the security group to certain IPS if required. Additionally, the Secret offers a password that this pretty hard to guess.

databaseSecurityGroup.addIngressRule(Peer.anyIpv4(), Port.tcp(5432), "PostgresSQL from anywhere");

Open up pgAdmin, Right click Server/Register/Server… and enter in the the name of the server leprechaun

Click on Connection and enter the host name as the RDS endpoint that you can see in the console or in the output of your CDK console.

Enter the RDS Endpoint in the Host name/address field which you can find in the RDS information in the AWS Console(See above). Enter in the username and password from the Secret above which you can get from the AWS Console.

Then you should press Save

If all works well, you should now have access to the instance

Let’s now create a database leprechaun by right-clicking Database(s)/Create/Database… and type in leprechaun and press save

Right click the leprechaun database and open up the Query Tool

You can now run the following command to create the Treasure table

CREATE TABLE TREASURE
(
ID SERIAL PRIMARY KEY,
DESCRIPTION VARCHAR(50),
LOCATION VARCHAR(600),
CREATED TIMESTAMP
);

Press the Execute button to run the script and Successfully create the table

Then you can INSERT a record into the table

INSERT INTO TREASURE (DESCRIPTION, LOCATION, CREATED) VALUES ('POT OF GOLD', 'UNDER THE RAINBOW', NOW());

Then you can Query to see if the record is now in the database

SELECT * FROM TREASURE;

Now you have successfully created an RDS instance that you created with CDK and have created a database with a table.

If you wanted to create a spring boot lambda application to interact with this database please continue on to https://collin-smith.medium.com/creating-a-spring-boot-java-21-application-with-a-public-postgres-rds-230ea9ac14bc

Tidying up

Once you have finished with your PostgreSQL RDS instance please execute a “cdk destroy” to remove the resources.

cdk destroy

Conclusion

In this article, you have seen how you can create a Public Postgres RDS Instance with Secrets Manager with CDK. This was done using Java but the same principle can be done for any of the languages supported by CDK. With a Custom VPC you can create the right networking to help support the AWS workload that you want to create rather than rely on the default VPC.

As previously mentioned you can create a Spring Boot application to interact with this database at https://collin-smith.medium.com/creating-a-spring-boot-java-21-application-with-a-public-postgres-rds-230ea9ac14bc

Now if you want to read on to other related to the concept of Java 21 Spring Boot with database implementations, please consider the following articles as well:

Appendix

Prerequisites

Access to an AWS Account

Java 21 — The latest Java version to date can be downloaded from here

Confirm that you have the right version in your command line