Terminal psql TCP/IP connection attempt that failed with “Operation timed out”

Connecting to RDS from Local over TCP (Operation timed out)

Justin Ross
Jan 31 · 5 min read

I deployed a RDS instance to AWS today and found difficulty connecting over TCP from my local. Below is the experience I faced and the steps I took to resolve. Hopefully I can save you (and future me) some trouble.

Disclaimer: Making your RDS instance accessible to the public is categorically a bad security practice. If your database security matters, you should access it through a bastion host in your VPC.

Experience:

  • Tried connecting over TCP/IP from terminal, using:
psql --host=xxxxx.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com \
--port=5432 \
--username=xxxxx \
--password \
--dbname=xxxxx
  • Entered password as prompted:
Terminal password prompt
Terminal password prompt
  • Waited a minute or so, and received the following error:
psql: error: could not connect to server: Operation timed outIs the server running on host "xxxxx.xxxxxxxxxxxx.us-east-1.rds.amazonaws.com" (xx.xx.xx.xx) and accepting TCP/IP connections on port 5432?

I am running a PostgreSQL RDS instance. If you are running a different db engine, you will receive a different error message, but it will generally convey the same information.

Troubleshooting:

A more likely suspect here is our network routing.

For reference, here’s a screenshot of the RDS instance’s “Connectivity and Security” in AWS Console:

RDS Instance Connectivity And Security Tab in AWS Console
RDS Instance in AWS Console

Is our RDS instance accessible to the public?

RDS Instance Security in AWS Console
RDS Instance Security

The “Public Accessibility” setting can be found in the AWS Console, under your instance’s “Security,” on the right.

If it is set to “No”, you can change this setting by clicking “Modify” in the top right of the page.

If you’re connecting via SSH through a bastion host, then this step doesn’t apply.

Is our Security Group allowing our request to hit the RDS instance?

RDS Instance Security in AWS Console
RDS Instance Security in AWS Console
RDS Instance Security

The security groups can be found in the AWS Console, under your instance’s “Security,” on the right, just above “Public accessibility” from our last step.

If you’re trying to connect to your RDS instance from your local, it’s important the security group permits this traffic. For PostgreSQL, this is typically an allowance of inbound traffic to 5432 from whatever host you would like to connect from. To allow access from any address on the internet, you would use 0.0.0.0/0. This is a bad security practice and should be avoided. For testing with this empty database, it’s what we’ll use.

RDS Security Group Inbound Rules in AWS Console
RDS Security Group Inbound Rules in AWS Console
RDS Security Group Inbound Rules

If you’re connecting from a bastion host in your VPC, you’ll likely want to specify the security group of the bastion host. If you’re just connecting from your current IP, there’s an option under source to use “My IP.”

Is our RDS instance able to send responses back to us?

RDS Instance Networking in AWS Console
RDS Instance Networking in AWS Console

The subnets can be found in the AWS Console under your RDS instance’s “Networking,” in the bottom middle.

If you’re connecting from an address outside of your VPC, you’ll want to be sure there’s an entry in your routing table that sends public traffic to an internet gateway.

If you’re connecting from a bastion host within your VPC or will have connected servers within your VPC, it’s important you’re routing traffic destined to a private IP within your VPC to “local.” My VPC CIDR is 10.0.0.0/16. Yours may start with 192 or 172.

RDS Instance Subnet Routing Table Routes in AWS Console
RDS Instance Subnet Routing Table Routes in AWS Console
Subnet Routing Table Routes

Here, the first line specifies that we’ll direct any traffic destined for a 10.x.x.x address to remain within our VPC. The second line specifies that we want any other traffic to any IPv4 address to go to our internet gateway.

Note: Though routing tables contain directions for traffic originating from within your subnet, targeting an internet gateway in your routing table is necessary to enable inbound traffic from the internet gateway. I'm unsure exactly what makes this so. I imagine there's some magic internal to AWS. If you understand why the routing table impacts inbound traffic, please let me know!

Summary:

The items to check are:

  1. Is your RDS instance enabled for “Public Accessibility?”
  2. Do your RDS instance Security Groups allow your requests in?
  3. Do your RDS instance Route Tables allow your responses out?

Hopefully this has saved you some trouble. If you have any questions or corrections, send em!

Cheers,
Justin

Drover Publications

Articles about design and development from Drover, a…

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store