AWS for Beginners – How to connect to AWS Postgresql DB from EC2 – Part 97

In this tutorial, we are going to look into the detailed steps of how to connect to an AWS PostgreSQL DB from an EC2 instance using psql client tool. Once the connection is established, the Postgresql DB can be managed through psql client tool. Also, if there are any web applications running on EC2 instances, those applications can also connect to the Postgresql DB once this is completed.

Prerequisites to connect to AWS Postgresql DB from EC2

To connect an AWS RDS PostgreSQL instance with an EC2 instance, you need to ensure the following prerequisites are met:

• AWS Account: You should have an active AWS account to access and manage AWS services
• EC2 Instance: Launch an EC2 instance that will serve as the client or application server connecting to the RDS PostgreSQL instance
• Security Group: Create a security group for your EC2 instance and configure inbound rules to allow incoming traffic on the desired PostgreSQL port (default is 5432). You’ll need to open this port to allow the EC2 instance to connect to the RDS instance
• RDS PostgreSQL Instance: Create an RDS PostgreSQL instance in your AWS account. Ensure that the instance is in the same AWS region as your EC2 instance
• VPC Configuration: If your RDS and EC2 instances are in different VPCs, you’ll need to set up VPC peering or a VPN connection to establish connectivity between them
• Subnet Configuration: If you are using subnets, ensure that the subnet where your EC2 instance resides allows outbound internet access, as the EC2 instance needs to communicate with the RDS instance over the internet
• Database Credentials: Set up a username and password for your PostgreSQL database in the RDS instance. You’ll need these credentials to connect from the EC2 instance
• IAM Role or User: If you want to use IAM-based authentication for connecting to the RDS instance, you’ll need to create an IAM role or user and assign the necessary permissions to access the RDS service
• RDS Security Group: Configure the security group of the RDS instance to allow inbound connections from the EC2 instance’s security group on the PostgreSQL port (5432 by default)

Once you have met these prerequisites, you should be able to establish a connection between your EC2 instance and the RDS PostgreSQL instance using the appropriate connection string and credentials.

Infrastructure Details:

The infrastructure details that will be used for this tutorial is given below.

These resources are created already.

• EC2 Instance:
• Name: dbclient
• OS: Amazon Linux
• VPC Id: vpc-5e746a27
• Subnet Id: subnet-11421b1d
• Security Group Id: sg-0f46dfa94a19515d0

• AWS RDS Postgresql Instance:
• Name: my-aws-psql-db-1
• VPC Id: vpc-5e746a27
• Subnet ID’s: subnet-11421b1d
• Security Group Id: sg-d48409a7

If you notice the infrastructure, the EC2 instance and PostgreSQL instance is created in the same the VPC. Hence, VPC peering is not required in this case. Both the resources are in the same subnet ID’s.

The following prerequisites are met already.

• EC2 instance created
• RDS PostgreSQL instance created
• VPC peering not required. Both the EC2 instance and DB is in same VPC
• Database credentials are setup already when creating the RDS PostgreSQL DB
• IAM role permissions are available

We will look at the steps to configure the connectivity between these 2 resources.

EC2 SECURITY GROUP CONFIGURATION

• In the AWS console, search for the EC2 instance and click on the security tab

In the security tab, you can find the security details like the security group name associated with the EC2 instance. Click on the security group.

• In the next screen, you can find the existing incoming rules in the security group.

• Click on Edit inbound rules and add a new rule to allow the traffic from PostgreSQL database from port 5432. This is the default port for PostgreSQL.

We will create a custom TCP rule, to allow the incoming connection from any IPv4 address to the port 5432 on this EC2 instance. Once done, click on “Save Rules” and the rule will be added to the security group.

RDS POSTGRESQL SECURITY GROUP CONFIGURATION

• In the AWS console, navigate to the RDS service and locate the postgreSQL database.

• Click on the database name. In the next screen, you can find the VPC security group for the DB.

• Click on the VPC security group ID for the DB. The rules configured will be visible in this screen.

• Click on edit inbound rules and add the rule to allow the incoming traffic to port 5432 from any IP address. In production environment, this can be restricted from the IP address of the EC2 instance. Click on “Save Rules”.

INSTALL PSQL CLIENT IN EC2 INSTANCE

Now install the PSQL client in the EC2 instance. To install the client follow the below steps.

• Find the postgresql version available using “sudo yum search postgres”

• In this version of Amazon Linux, the latest version available is postgresql15. We will install the client using “sudo yum install postgresql15”

EXECUTE PSQL COMMAND TO CONNECT TO DB

To connect to the DB using psql we need the following details. These details are available in the RDS page of the database in AWS console.

Hostname: my-aws-psql-db-1.c8x7rukvf8s2.us-east-1.rds.amazonaws.com (Postgresql endpoint from the AWS Console for RDS DB)
port:5432 (DB port number)
database: free_tier_aws_db_1 (Postgresql DB name. This is different from the DB Instance)
username: psqladmin (User name configured while creating the DB)
password: Command to connect to the DB:

psql -h my-aws-psql-db-1.c8x7rukvf8s2.us-east-1.rds.amazonaws.com -p 5432 -U psqladmin -d free_tier_aws_db_1

CONCLUSION:

In this tutorial, we have looked at the prerequisites for connecting EC2 to RDS and how to connect to connect EC2 to RDS Postgresql. Once this connection is enabled, psql clients, PGAdmin client or any web applications would be able to connect to the RDS Postgresql database.

Follow our Twitter and Facebook feeds for new releases, updates, insightful posts and more.