DevOps

How to Connect to PostgreSQL (psql) from Your Local Computer to an AWS RDS

Posted on by Karmina Clemente
22
May

Connecting to an Amazon Web Services (AWS) RDS database from your local computer is an essential task to manage and query your data. However, it’s common to make some mistakes in the process. In this tutorial, I will show you how to do it correctly.

Preliminary Steps

Before starting, make sure you meet the following requirements:

  1. Have PostgreSQL installed on your local computer. This includes the command line tool psql. You can verify if it is installed by running psql --version in your terminal.
  2. Access credentials to your RDS database. You will need:
    • Host: The address of your RDS instance.
    • User: The username configured on your database.
    • Password: The user’s password.
    • Database name: The specific database you want to connect to.
  3. Proper configuration of your RDS Security Group. This step is crucial and where many people tend to make mistakes.

Note: Common Mistakes

A common mistake when configuring access to an RDS instance is granting permissions to the Security Group of an EC2 instead of the one for the RDS.

Another frequent mistake is creating a rule for SSH (port 22), because it is often assumed that the problem is related to the terminal, since the error appears there. However, even though access is initiated from the terminal, what is really needed is a connection to the corresponding database port, such as PostgreSQL port (5432). Therefore, it is important to ensure the necessary database port is configured in the security rules.

Step 1: Configure the Security Group for your RDS

A common mistake when configuring access to an RDS instance is granting permissions to the Security Group of an EC2 instead of the RDS. To connect from your local computer, you must add your IP address to the inbound rules (Inbound Rules) of the Security Group associated with your RDS. Here’s how to do it:

Another common mistake is creating the SSH rule (this is often assumed because the error usually appears in the terminal or we infer the connection is from the terminal… and yes, it is from the terminal but it seeks connection to another port, so the necessary port is PostgreSQL).

  1. Log into your AWS console.
  2. Go to the EC2 section and select Security Groups.
  3. Find the Security Group associated with your RDS.
  4. Edit the inbound rules (Inbound Rules) and add a new rule:
    • Type: PostgreSQL.
    • Protocol: TCP.
    • Port: 5432.
    • Source: Your IP address (select “My IP” to add it automatically).

Save the changes.

Step 2: Connect to your RDS using psql

Once the Security Group is configured, use the following command in your terminal to connect to your RDS from your local computer:

psql -h <host> -U <username> -d <database_name> -p 5432

Parameter details:

  • -h <host>: The endpoint of your RDS instance (for example, mydb.xxxxxx.us-east-1.rds.amazonaws.com).
  • -U <username>: The username of your database.
  • -d <database_name>: The name of the specific database you want to connect to.
  • -p 5432: The port where PostgreSQL listens by default.

Possible errors and solutions

Error 1: Connection timed out

This happens if your IP is not properly configured in the inbound rules of the Security Group. Check step 1 and confirm that your IP address is the one listed.

Error 2: FATAL: password authentication failed

This indicates that the username or password is incorrect. Verify your credentials and make sure they match those configured in your RDS.

Error 3: psql: could not translate host name

This occurs if the entered host is invalid. Make sure to use the full endpoint shown in your RDS configuration.

With these steps, you should be able to successfully connect to your RDS from your local computer using psql. If you have any questions, leave them in the comments! 🚀

When configuring security rules for our applications in Elastic Beanstalk, we may wonder which ports are truly necessary. Below, I explain the purpose of each port and best practices for configuring them.

Review of Inbound Rules

These are the rules that define what kind of traffic can reach your Elastic Beanstalk instances:

1. PostgreSQL (Port 5432)

  • Is it necessary?
    Yes, this port is absolutely necessary for your application to communicate with the database (RDS).
  • Recommended configuration:
    Allow traffic only from the Elastic Beanstalk security group.

2. HTTP (Port 80)

  • Is it necessary?
    Optional. This port allows insecure access to your application. If your application uses HTTPS (port 443), you can remove this rule.
  • Recommended configuration:
    If you decide to keep it, make sure to configure an HTTP to HTTPS redirect.

3. HTTPS (Port 443)

  • Is it necessary?
    Yes, if you are using secure connections (SSL/TLS) in your application.
  • Recommended configuration:
    Keep this rule active to secure your application’s traffic.

4. SSH (Port 22)

  • Is it necessary?
    No, for production environments. This port allows remote access to your instances, but leaving it open can be a security risk. For production, it is recommended to disable it.
  • Alternatives to SSH:
    • Use Elastic Beanstalk’s SSH feature from the AWS console, which creates secure temporary sessions.
    • Use AWS Systems Manager (SSM) to access remotely without enabling SSH.

Always minimize the number of open ports to reduce your security attack surface.

Karmina Clemente