How to Set up a PostgreSQL Database on Amazon RDS

Table of Content

  1. What is Database?
  2. What is AWS RDS?
    • Database Created Manually vs Database Created using AWS RDS
  3. What is PostgreSQL?
  4. Creating Amazon RDS with PostgreSQL Manually
    • Prerequisites
    • Important Parameters for setting up Postgres
    • Security Required in Setting up Postgres
    • Connecting to DB instance using Postgres engine with Master Password from pgadmin4
  5. Troubleshooting the PostgreSQL connectivity issues.

What is Database?

All of you might be very well aware of what database is used for , right ? But Let me share few examples of it. Suppose you have an company and you want to store all the information of your employees, such as Name, Employee ID , Employee Address , Employee Joining date , Employee benefits , where do you store them ? So , the Answer is database where you keep your all data securely and efficiently.

This image has an empty alt attribute; its file name is image-44.png

What is AWS RDS?

“Amazon Relational Database” is a web service which helps in setting up and maintaining the relational database in AWS. What I mean by maintaining here is : we can configure our RDS in such a way that it can scale up or scale down whenever required , it has resizable capacity i.e you can configure different instance size and can be load balanced and fault tolerant in nature. All depends on how we would like to configure. The service helps us in solving by removing lots of tedious management tasks than setting up manually and saving lot of our time. RDS supports 6 database engines: Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database and SQL Server.

Database Created ManuallyDatabase Created using AWS RDS
You need to buy SeverNo need to buy any hardware
You need to configure backups manuallyAWS RDS takes care of backups automatically
To make it highly available you need to configure many thingsYou just need to choose highly available option
You cannot utilize IAM if you database is not build using RDS or on AWSYou can configure access using IAM
Less secure , as you get SHELL accessMore secure , as you don’t have access to SHELL




This image has an empty alt attribute; its file name is image-45.png
AWS RDS service contains RDS instances , Instances contains RDS databases and database users & finally you connect them using Clients such as pgadmin4

What is PostgreSQL?

PostgreSQL is an open source relational database system which has capability to handle heavy workload and can scale systems very easily. It can run mostly on all Operating systems. It is an open source but highly extensible like you can define your own data types, functions .

Some of the features of PostgreSQL are:

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

Creating Amazon RDS with PostgreSQL Manually

This image has an empty alt attribute; its file name is image-49.png

Prerequisites: Make sure you have two things

a) Amazon AWS account , you can create your account via https://signin.aws.amazon.com if you don’t have it already.

b) pgAdmin utility to connect to PostgreSQL database instance via https://www.pgadmin.org/download which will be use later in this tutorial once we are done with creation part.

So, Lets go and create our AWS RDS Postgres now !!

Step 1) Sign into your AWS account and on the top of the Page you will see “Search for services, features, marketplace products and docs” , here please type AWS RDS and choose RDS.

This image has an empty alt attribute; its file name is image-46.png

Step 2) On Left Panel click on Databases and then click on Create database.

This image has an empty alt attribute; its file name is image-47.png

Step 3) After you click on Create database choose method as Standard create , Engine as PostgreSQL and Latest Version : PostgreSQL 12.5-R1 and select FREE tier from Templates.

This image has an empty alt attribute; its file name is image-51.png

Step 4) Lets provide the database name , master username , master password and keeping all the storage values as default .

*According to your need and setup you can Provide storage.

This image has an empty alt attribute; its file name is image-52.png
This image has an empty alt attribute; its file name is image-53.png

Step 5) Lets Configure the Connectivity now : “Availability & durability and connectivity “

Important Note : Security group is used to allow your Inbound and Outbound traffic. In order for us to connect to RDS instance we will modify the inbound and outbound rules of default security group which we are using to create the RDS instance.

Path to reach security group : On top of the Page you will see “Search for services, features, marketplace products and docs” , here look for EC2 and enter and then you will see Security groups under Network and security. Choose the default security group which is linked with your VPC.

This image has an empty alt attribute; its file name is image-58.png
This image has an empty alt attribute; its file name is image-60.png
This image has an empty alt attribute; its file name is image-55.png

Step 6) Make sure the default settings for “Database authentication” and “Additional configuration” are selected.

This image has an empty alt attribute; its file name is image-57.png

Step 7) We are now ready : Lets click on Create Database

**** It usually takes few mins for your RDS instance to be launched.

Here we GO !! Our Database instance is successfully created now

This image has an empty alt attribute; its file name is image-59.png

Step 8) Lets Open pgAdmin and connect to our “myrds” database instance

After you open pgadmin you will see Servers on the left side. Right click and servers and create a new server

This image has an empty alt attribute; its file name is image-63.png

Step 9) Lets Open pgAdmin and connect to our “myrds” database instance

After you open pgadmin you will see Servers on the left side. Right click and create a new server

In General Tab select name as “myrds” and in Connection Tab provide the Host i.e endpoint URL of your database instance ( You can find this in Connectivity and security under Databases in AWS RDS

This image has an empty alt attribute; its file name is image-64.png
This image has an empty alt attribute; its file name is image-65.png

Step 10) Click “SAVE”

This image has an empty alt attribute; its file name is image-66.png



Step 11) Now, let’s go ahead and create a database within the server. Right click on “Databases” and select “Create” and then “Database…” Give the database a name and save when done.

This image has an empty alt attribute; its file name is image-67.png

Step 12) Alright, now our new database “testing” is also created successfully. 

This image has an empty alt attribute; its file name is image-69.png

Troubleshooting the PostgreSQL connectivity issues.

Error while connectingSolution
Database name doesn’t exist while connectingTry connecting the default database i.e postgres
Couldn’t connect to server: connection time out1.Check if you entered correct host name
2. Make sure DB is publicly accessible
3. Check if you are giving correct port i.e 5432
4. Check the Inbound and outbound rule of default security group


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

Are you saving your passwords in text files or configuration files or deployment files while deploying in Amazon AWS accounts? That’s very very risking but no worries you have come to the right place to learn and use AWS secrets which solves all your security concerns and encrypts all of your stored passwords and decrypt only while retrieving them.

Table of content

  1. What is AWS Secrets and Secret Manager?
  2. Prerequisites
  3. How to Install Terraform on Ubuntu 18.04 LTS
  4. Terraform Configuration Files and Structure
  5. Configure Terraform File to Create AWS Secrets and Secrets versions on AWS
  6. Create Postgres database using terraform with database master account credentials as AWS Secrets
  7. Conclusion

What are AWS Secrets?

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

Now here comes an AWS service that manages all the above issues with Secrets manager by retrieving the password programmatically. Another major benefit of using AWS secrets is it can rotate your credentials at any schedule defined by you.

We are using AWS Secrets Manager so that we can keep our main and important Passwords safe and secure.

The application connects with Secret Manager to retrieve secrets and then connects with database

Prerequisites

  • Ubuntu machine to run terraform preferably 18.04 version + , if you don’t have any machine you can create a ec2 instance on AWS account or AWS Account
  • Recommended to have 4GB RAM
  • At least 5GB of drive space
  • Ubuntu machine should have IAM role attached with full access to create AWS secrets or it is always great to have administrator permissions to work with demo’s.

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

How to Install Terraform on Ubuntu 18.04 LTS

  • Update your already existing system packages.
sudo apt update
  • Download the latest version of terraform in opt directory
wget https://releases.hashicorp.com/terraform/0.14.8/terraform_0.14.8_linux_amd64.zip
This image has an empty alt attribute; its file name is image-163.png
  • Install zip package which will be required to unzip
sudo apt-get install zip -y
  • unzip the Terraform download zip file
unzip terraform*.zip
  • Move the executable to executable directory
sudo mv terraform /usr/local/bin
  • Verify the terraform by checking terraform command and version of terraform
terraform               # To check if terraform is installed 
terraform -version      # To check the terraform version  
This image has an empty alt attribute; its file name is image-164.png
  • This confirms that terraform has been successfully installed on ubuntu 18.04 machine.
This image has an empty alt attribute; its file name is image-165.png

Terraform Configuration Files and Structure

Let us first understand terraform configuration files before running Terraform commands.

  • main.tf : This file contains code that create or import other AWS resources.
  • vars.tf : This file defines variable types and optionally set the values.
  • output.tf: This file helps in generating of the output of AWS resources .The output is generated after the terraform apply command is executed.
  • terraform.tfvars: This file contains the actual values of variables which we created in vars.tf
  • provider.tf: This file is very important . You need to provide the details of providers such as AWS , Oracle or Google etc. so that terraform can make the communication with the same provider and then work with resources.

Configure Terraform File to Create AWS Secrets and Secrets versions on AWS

Now, Let’s create terraform configuration files that will be required to create secrets.

  • Create a folder in opt directory and name it as terraform-demo-secrets and create a file and name it as main.tf.
# Firstly we will create a random generated password which we will use in secrets.

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


# Now create secret and secret versions for database master account 

resource "aws_secretsmanager_secret" "secretmasterDB" {
   name = "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
}

# Lets import the Secrets which got created recently and store it so that we can use later. 

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

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

# After Importing the secrets Storing the Imported Secrets into Locals

locals {
  db_creds = jsondecode(
  data.aws_secretsmanager_secret_version.creds.secret_string
   )
}
  • Create another file and name it as provider.tf
provider "aws" {
  region = "us-east-2"
}
  • Now your files and code are ready for execution . Initialize the terraform
terraform init
  • Terraform initialized successfully , now its time to see the plan which is kind of blueprint before deployment. We generally use plan to confirm if correct resources is going to provisioned or deleted.
terraform plan
  • After verification , now its time to actually deploy the code using apply.
terraform apply
  • Now Open your AWS account and search for AWS Secrets Manager. As you can see the secret has been created succesfully. Click on the secret which we created . In our case it was Masteraccoundb and scroll little down .
  • Click on Retrieve secret value
  • You can see the secrets keys and values are succesfully added.

We can see that secrets got created successfully using terraform. The next step is to use these secrets as credentials for the database master account while creating the database.

Create Postgres database using terraform with database master account credentials as AWS Secrets

  • Open the same main.tf again and paste the below code at the bottom
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 
}
  • This time you can directly run apply command as this is demo version , although its recommend to run terraform init then plan and then apply.
terraform apply
  • Now go to AWS RDS service on Amazon account and check the Postgres cluster
  • Now click on democluster and then hop over to configurations.

Conclusion

In this tutorial, we demonstrated AWS Secrets Manager and learned how to create AWS secrets, and later created a Postgres database utilizing AWS secrets as master account credentials.

Hope this tutorial will help you in understanding the terraform and provisioning the AWS secrets on the Amazon cloud. Please share with your friends