How to Set up a PostgreSQL Database on Amazon RDS

If you new to AWS RDS or planning to create your first AWS RDS database instance then you are at the right place to learn one of the most popular and widely used Database engine PostgreSQL.

In this tutorial you will learn how to set up a PostgreSQL Database on Amazon RDS in the Amazon cloud from scratch and step by step.

Still interested? Lets get into it.

Join 50 other followers

Table of Content

What is Database?

If you want to store all the information of your employees securely and efficiently such as Name, Employee ID, Employee Address, Employee Joining date, Employee benefits, etc then you need a database.

Basic Database diagram
Basic Database diagram

What is AWS RDS?

Amazon Relational Database (AWS RDS) is an Amazon web service that helps in setting up and configuring the relational database in AWS. With AWS RDS you can scale up or down the capacity i.e you can configure different instance sizes, load-balanced, apply fault-tolerant.

AWS RDS also removes tedious management tasks than setting up manually and saving a lot of our time. AWS RDS supports six database engines: Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and SQL Server.

With AWS you don’t need to rely on buying hardware, backups, scalability, availability, and it’s more secure than hosting your own database. In the below snap AWS RDS service contains RDS instances and Instances further contain RDS databases and database users & finally you connect them using database clients such as pgadmin4.

Connecting AWS RDS PostgreSQL database from pgadmin client
Connecting AWS RDS PostgreSQL database from pgadmin client

What is PostgreSQL?

PostgreSQL is an open-source relational database system that has the capability to handle heavy workloads, scale systems easily, runs mostly on all Operating systems, and is highly extensible like you can define your own data types, functions. PostgreSQL is one of the most widely used AWS RDS DB engines.

A DB engine is the specific relational database software that runs on your DB instance.

Some of the features of PostgreSQL are listed below:

  • Security
  • Extensibility
  • Text Search
  • Reliable
  • Data Integrity
  • Good Performance

Prerequisites

This tutorial will be step by step and if you would like to follow along, then you must have.

  • Amazon AWS account. If you dont have AWS account create from here.
  • pgAdmin utility to connect to PostgreSQL database instance. To install pgadmin click here.

Creating a PostgreSQL DB instance in AWS RDS

Now that you have a basic idea of what is Postgres database and the benefits of hosting your database on AWS RDS with a database engine like PostgreSQL. Let’s quickly learn how to create a PostgreSQL DB instance in AWS RDS.

  • Sign into your AWS account and and search for AWS RDS in the search box and click on RDS.
Searching for AWS RDS service in AWS Cloud
Searching for AWS RDS service in AWS Cloud
  • Now, in the AWS RDS page click on Create database.
Creating database in AWS RDS service
Creating database in AWS RDS service
  • Further on Create database page choose database creation method as Standard create , Engine as PostgreSQL and Version as : PostgreSQL 12.5-R1 and select FREE tier from Templates.

The Latest vesion of PostgreSQL is PostgreSQL 14.1-R1

Defining all the parameters to create a AWS RDS database engine
Defining all the parameters to create a AWS RDS database engine
  • Next, provide the database name, master username, master password and keeping all the storage values as default .
Specifing the Configuration of database instance
Specifing the Configuration of database instance
Defining storage for database instance
Defining storage for database instance
  • Further in Connectivity section select the Virtual Private Cloud, Subnet group in which you would like to create the AWS RDS instance, Public access as Yes, and select security group as default.

Make sure to allow 0.0.0.0/0 in the Inbound and Outbound traffic in the default security group and subnet group have route to internet so that you can connect to RDS instance from the database client from your browser or local machine.

Defining network connectivity options in AWS RDS
Defining network connectivity options in AWS RDS
  • Now in the “Database authentication” choose Password authentication and finally click on Create database. It usually takes few mins for RDS instance to be launched in AWS Cloud.
Specifying the database authentication method
Specifying the database authentication method

Verifying AWS RDS Postgres database instance in AWS Cloud

Now that you have created the AWS RDS Postgres database instance in AWS Cloud, which is great but unless you verify in Amazon Management console you cannot be sure enough. Lets navigate to AWS console and verify the Postgres instance in AWS RDS service.

As you can see the specified mydb instance has been created successfully in AWS RDS.

Verifying AWS RDS Postgres database instance in AWS Cloud
Verifying AWS RDS Postgres database instance in AWS Cloud

Connecting to a DB instance running the PostgreSQL database engine

Now that you have verified the DB instance running the PostgresSQL in AWS cloud, its time to connect using pgAdmin client from your machine. To connect

  • Open pgAdmin on your machine and click on Create and further Server.
Connecting to PostgreSQL database instance from pgadmin
Connecting to PostgreSQL database instance from pgadmin
  • In the Create-Server Pageunder General tab select name as “myrds”. Next, navigate to Connection tab and provide the all the details such as Host i.e endpoint URL of your database instance, Port, , username and passsword as shown below.
Defining Name of database to connect
Defining Name of database to connect
Defining connection details of the PostgreSQL database instance
Defining connection details of the PostgreSQL database instance
  • After you provide all the details and click on save button, the newly created database will be visible under the severs as shown below.
checking the database instance
checking the database instance
  • Finally under myrds database instance create a database by right clicking on Databases and select Create ➔ Database and provide the name of the database you wish to create.
Creating database instance AWS RDS database instance
Creating database instance AWS RDS database instance
  • As you can see below the testing database is created successfully. 
Viewing the newly launched database in AWS RDS database instance
Viewing the newly launched database in AWS RDS database instance

Conclusion

In this tutorial you learned about one of the most widely used AWS RDS database Postrgres and how to create it in Amazon management console.

So what do you plan to store in this newly created database instance.

Advertisement

How to create Secrets in AWS Secrets Manager using Terraform in Amazon account.

While deploying in the Amazon AWS cloud, are you saving your passwords in the text files, configuration files, or deployment files? That’s very risky and can expose your password to attackers. Still, no worries, you have come to the right place to learn and use AWS secrets in the AWS Secrets Manager, which solves all your security concerns, encrypts all of your stored passwords, and decrypts only while retrieving them.

In this tutorial, you will learn how to create Secrets in AWS Secrets Manager using Terraform in the Amazon account. Let’s get started.

Join 50 other followers

Table of Content

  1. What are AWS Secrets and AWS Secrets Manager?
  2. Prerequisites
  3. Terraform files and Terraform directory structure
  4. Building Terraform Configuration to create AWS Secrets and Secrets versions on AWS
  5. Creating Postgres database using Terraform with AWS Secrets in AWS Secret Manager
  6. Conclusion

What are AWS Secrets and AWS Secrets Manager?

There was a time when all the passwords of databases or applications were kept in configuration files. Although they are kept secure simultaneously, they can be compromised if not taken care of. If you are required to update the credentials, it used to take tons of hours to apply those changes to every single file, and if you miss any of the files, it can cause the entire application to get down immediately.

AWS Secrets Manager service manages all the above issues with AWS Secrets Manager by retrieving the AWS secrets or passwords programmatically. Another major benefit of using AWS secrets is that it rotates your credentials at the schedule you define. AWS Secrets Manager keeps the important user information passwords safe and secure.

The application connects with Secret Manager to retrieve secrets and then connects with database
The application connects with Secret Manager to retrieve secrets and then connects with the database.
Admin retrieving the secrets from the AWS Secret Manager and applying in the database
Admin retrieving the secrets from the AWS Secret Manager and applying in the database

Prerequisites

  • Ubuntu machine 20.04 version would be great , if you don’t have any machine you can create a AWS EC2 instance on AWS account with recommended 4GB RAM and at least 5GB of drive space.
  • Ubuntu machine should have IAM role attached with full access to create AWS secrets in the AWS Secret Manager or administrator permissions.
  • Terraform installed on the Ubuntu Machine. Refer How to Install Terraform on an Ubuntu machine.

You may incur a small charge for creating an EC2 instance on Amazon Managed Web Service.

Terraform files and Terraform directory structure

Now that you have Terraform installed. Let’s now dive into Terraform files and Terraform directory structure that will help you write the Terraform configuration files later in this tutorial.

Terraform code, that is, Terraform configuration files, are written in a tree-like structure to ease the overall understanding of code with .tf format or .tf.json or .tfvars format. These configuration files are placed inside the Terraform modules.

Terraform modules are on the top level in the hierarchy where configuration files reside. Terraform modules can further call another child to terraform modules from local directories or anywhere in disk or Terraform Registry.

Terraform contains mainly five files as main.tf , vars.tf , providers.tf , output.tf and terraform.tfvars.

  1. main.tf – Terraform main.tf file contains the main code where you define which resources you need to build, update or manage.
  2. vars.tf – Terraform vars.tf file contains the input variables which are customizable and defined inside the main.tf configuration file.
  3. output.tf : The Terraform output.tf file is the file where you declare what output paraeters you wish to fetch after Terraform has been executed that is after terraform apply command.
  4. .terraform: This directory contains cached provider , modules plugins and also contains the last known backend configuration. This is managed by terraform and created after you run terraform init command.
  5. terraform.tfvars files contains the values which are required to be passed for variables that are refered in main.tf and actually decalred in vars.tf file.
  6. providers.tf – The povider.tf is the most important file whrere you define your terraform providers such as terraform aws provider, terraform azure provider etc to authenticate with the cloud provider.

Building Terraform Configuration to create AWS Secrets and Secrets versions on AWS

Now that you have sound knowledge of what Terraform configuration files look like and the purpose of each of the Terraform configuration files. So, let’s create Terraform configuration files required to create AWS secrets.

  • Log in to the Ubuntu machine using your favorite SSH client.
  • Create a folder in opt directory named terraform-demo-secrets and switch to that folder.
mkdir /opt/terraform-demo-secrets
cd /opt/terraform-demo-secrets
  • Create a file and name it as main.tf in the /opt/terraform-demo-secrets and copy/paste the below content. The below file creates the below components:
    • Creates random password for user adminaccount in AWS secret(Masteraccoundb)
    • Creates a secret named Masteraccoundb
    • Creates a secret version that will contain AWS secret(Masteraccoundb)
# Firstly create a random generated password to use in secrets.

resource "random_password" "password" {
  length           = 16
  special          = true
  override_special = "_%@"
}

# Creating a AWS secret for database master account (Masteraccoundb)

resource "aws_secretsmanager_secret" "secretmasterDB" {
   name = "Masteraccoundb"
}

# Creating a AWS secret versions for database master account (Masteraccoundb)

resource "aws_secretsmanager_secret_version" "sversion" {
  secret_id = aws_secretsmanager_secret.secretmasterDB.id
  secret_string = <<EOF
   {
    "username": "adminaccount",
    "password": "${random_password.password.result}"
   }
EOF
}

# Importing the AWS secrets created previously using arn.

data "aws_secretsmanager_secret" "secretmasterDB" {
  arn = aws_secretsmanager_secret.secretmasterDB.arn
}

# Importing the AWS secret version created previously using arn.

data "aws_secretsmanager_secret_version" "creds" {
  secret_id = data.aws_secretsmanager_secret.secretmasterDB.arn
}

# After importing the secrets storing into Locals

locals {
  db_creds = jsondecode(data.aws_secretsmanager_secret_version.creds.secret_string)
}
  • Create another file in the /opt/terraform-demo-secrets and name it as provider.tf. This file allows Terraform to interact with AWS cloud using AWS API.
provider "aws" {
  region = "us-east-2"
}
Checking all the files in terraform-demo-secrets folder
Checking all the files in the terraform-demo-secrets folder
  • Now your files and code are ready for execution. Initialize the terraform using the terraform init command in the /opt/terraform-demo-secrets.
terraform init
Initializing the terraform using the terraform init command.
Initializing the terraform using the terraform init command.
  • Terraform initialized successfully , now its time to run the plan command which provides you the details of the deployment. Run terraform plan command to confirm if correct resources is going to provisioned or deleted.
terraform plan
Running the terraform plan command
Running the terraform plan command
Output of the terraform plan command
The output of the terraform plan command
  • After verification, now its time to actually deploy the code using terraform apply command.
terraform apply
Running the terraform apply command
Running the terraform apply command
  • Great Job; terraform commands were executed succesfully. Now Open your AWS account and navigate to the AWS Secrets Manager.

As you can see, the AWS secret has been created successfully in the AWS account. Click on the secret (Masteraccoundb) and further click on Retrieve secret value button.

Verifying the AWS secret
Verifying the AWS secret
  • Click on Retrieve secret value to see the values stored for the AWS Secret.
Retrieve the AWS secret value
Retrieve the AWS secret value

As you can see, the secret keys and values are successfully added as you defined in Terraform configuration file.

Verifying the AWS secret values
Verifying the AWS secret values

Creating Postgres database using Terraform with AWS Secrets in AWS Secret Manager

Now the secret keys and values are successfully added as you defined in Terraform configuration file using Terraform. The next step is to use these AWS secrets as credentials for the database master account while creating the database.

  • Open the same Terraform configuration file main.tf agaian and copy/paste the below code at the bottom of th file. As you can see the below file creates the database cluster using the AWS secrets master_username = local.db_creds.username and master_password = local.db_creds.password.
resource "aws_rds_cluster" "main" { 
  cluster_identifier = "democluster"
  database_name = "maindb"
  master_username = local.db_creds.username
  master_password = local.db_creds.password
  port = 5432
  engine = "aurora-postgresql"
  engine_version = "11.6"
  db_subnet_group_name = "dbsubntg"  # Make sure you create this before manually
  storage_encrypted = true 
}


resource "aws_rds_cluster_instance" "main" { 
  count = 2
  identifier = "myinstance-${count.index + 1}"
  cluster_identifier = "${aws_rds_cluster.main.id}"
  instance_class = "db.r4.large"
  engine = "aurora-postgresql"
  engine_version = "11.6"
  db_subnet_group_name = "dbsubntg"
  publicly_accessible = true 
}
  • Again execute the terraform init → terraform plan → terraform apply commands.
terraform apply
terraform apply command created the database successfully using the AWS Secrets
terraform apply command created the database successfully using the AWS Secrets
  • Now navigate to the AWS RDS service on Amazon account and check the Postgres cluster that got created recently.
Navigating to the AWS RDS service on Amazon account
Navigating to the AWS RDS service on Amazon account
  • Finallly click on democluster and you should see the AWS secrets created earlier by Terraform are succesfully applied in the Postgres database in AWS RDS.
AWS secrets created earlier by Terraform are successfully applied in the Postgres database in AWS RDS
AWS secrets created earlier by Terraform are successfully applied in the Postgres database in AWS RDS

Join 50 other followers

Conclusion

In this tutorial, you learned what is AWS Secrets and AWS Secrets manager, how to create AWS secrets in the AWS Secrets Manager, and create a Postgres database utilizing AWS secrets as master account credentials.

Now that you have secured your database credentials by storing them in AWS secrets, what do you plan to secure next?