There are a few ways to connect to an Amazon RDS instance running PostgreSQL. Two of the most common methods are:
- Write inbound rules to allow a specific IP address (or set of IPs) to access the RDS instance.
- Use an SSH Tunnel.
NOTE: Accessing an RDS instance directly, via the inbound rules on its security group, is possible only if you enabled “Public” access during its creation. If you follow AWS best practices and launch all your RDS instances in a VPC and make them private, you can’t access them from an outside IP. Instead, you’ll need to jump through some hoops (e.g. following the second method above— the SSH tunnel method).
Both of these methods require a basic understanding of AWS Security Groups. And both require us to assign inbound rules to Security Groups, specifically the group being used by the RDS instance running your database.
One issue with the first method is that a machine’s IP address is not necessarily permanent — the machine attempting to connect to the RDS instance may be a laptop. Thus, it will have a different IP address depending on where it, and its user, are located.
Connecting using an SSH tunnel to channel through an EC2 machine that has access to the RDS instance’s VPC is more secure. It’s also less of a hassle in the long run, because its IP will not change. However, the machine can fail. This will result in a new EC2 instance being spun up to run your application, which will have a new IP address. If this occurs, you will once again need to alter the inbound rules of the RDS Security Group.
Regardless of which method you decide to use, you’ll need to understand how to connect to the database using the psql client. An quick explanation of how to do so follows.
Connect to a PostgreSQL Database
There are multiple valid formats (and orderings of parameters) for connecting to a database with psql from the command line. If you don’t know much about PostgreSQL or the psql client, read this article first for a quick introduction. The format that I use most often to connect to a PostgreSQL database via psql is:
psql -h hostname -p portNumber -U userName dbName -W
The parameters are explained below:
- h — The host name of your DB instance (“localhost” if the DB is running on your local machine). If you’re attempting to connect to an RDS database, the host name will be the endpoint (URL) of the instance on which the database is running. This is found in the “Connectivity” panel of the specific database, under the “RDS” service on the Amazon Web Console.
- p — The port number you’re using to connect (5432 is the default for PostgreSQL databases).
- U — The database account you’d like to access (the user who owns the database).
- dbName — The database you want to access.
- W — This forces the psql client to prompt you for the database password.
Before you can use the psql client to connect to your RDS database (with either of the methods above) you’ll have to ensure that the appropriate security groups and inbound rules are set.
Managing Security Groups and Inbound Rules
Per Amazon’s documentation:
“a Security Group acts as a virtual firewall for your instance to control inbound and outbound traffic. When you launch an instance in a VPC, you can assign up to five security groups to the instance. Security groups act at the instance level, not the subnet level. Therefore, each instance in a subnet in your VPC could be assigned to a different set of security groups. If you don’t specify a particular group at launch time, the instance is automatically assigned to the default security group for the VPC.
For each security group, you add rules that control the inbound traffic to instances, and a separate set of rules that control the outbound traffic.”
With this brief understanding, let’s dive in to managing our own Security Group in order to grant us the access we need. First, log in to the Amazon Web Console. Next, navigate to the “RDS” service and click “Databases” from the left side-bar. Select the “DB Name” of the database to which you’d like to connect. Under the “Connectivity Panel” you can locate the particular Security Group assigned to the instance. Click its link. This will take you to the Security Group management panel. At the bottom, you will see inbound and outbound tabs showing rules associated with the security group.
If you want to enable SSH access to the RDS instance via the machine you’re currently working on, you have two options. The first (which is NOT recommended because it presents serious security concerns) is to add the following rule:
Make sure to hit “Save” when you’re done.
This rule allows access to all TCP traffic coming from any IP address. Here at Deep Space Program, we enable this rule only once in a blue moon for very limited periods of time—almost exclusively development periods when we’re in the process of setting up a fresh database that stores no live user data. Once again, this method is NOT recommended as a permanent solution.
The second option is very similar, yet it limits traffic to a single IP address — that of the machine you’re working on. The rule for this setup is simple:
Both of these options will allow you to use the psql client, with the command described in the section above, to access a database running on the RDS instance whose security is governed by the particular Security Group you’ve just edited.
Now, let’s go through how to set up an SSH tunnel. This method is slightly more complicated but ultimately more secure.
Setting up an SSH Tunnel
We advise you to follow this method. It’s also the only method available to you in the (recommended) scenario that your RDS instance is set to
Let’s assume you’re working with an RDS instance whose assigned Security Group disallows connections from hosts outside of its private VPC. Instead of trying to connect directly from our local machine, we SSH tunnel through a EC2 machine (likely the one that’s running your web app) that has public access enabled.
What we’re actually doing is using a process called port forwarding to access the RDS instance. What this means is that we’re forwarding commands made from a specific port on our local machine through a port on a remote machine (an EC2 instance with public access in this case), which has access to another remote machine (the RDS instance without public access in this case).
We’re going to cover the process of connecting to an RDS instance from an EC2 instance that was set up for us on behalf of Elastic Beanstalk. However, the process is similar for any EC2 instance, including those you’ve set up manually for your own purposes.
The first step is to double check that you’ve enabled SSH access on whatever EC2 instance was spun up to run your app. If you remember allowing access when creating your Elastic Beanstalk application via the command line, as part of that process you will have automatically saved a valid keypair within your local
~/.ssh folder. If you can locate that keypair, you will have SSH access to the EC2 instances that are created within that application’s environments. If you neglected to do so, you’ll need to go back and enable access. As of the time of this writing, the latest version (v3.0) of the Elastic Beanstalk CLI makes it incredibly easy to SSH into an EC2 machine via the following command:
# Using the Elastic Beanstalk CLI (v3.0 or later)
It’s possible that access was not previously allowed; thus, a keypair was neither autogenerated nor saved. Luckily, if that’s your situation, this command will prompt you with the option to simultaneously 1) create a new keypair and 2) restart your EB environment with instances that have this new keypair assigned to them.
NOTE: In order to SSH to your instance (which could be behind a Load Balancer) you may also need to manually add inbound rules to the environment’s “default” Security Group. You can do this by navigating to the “EC2” service on the Amazon Web Console. Make sure you’ve already selected the relevant region. Next, under the “Network & Security” header in the left side-bar, click “Security Group”. If you (or EB on your behalf) have launched an EC2 instance in this region, you will see a Security Group with a Group Name of “default” and a description of “default VPC security group”. Edit the Security Group’s inbound rules by adding the following:
This rule allows SSH access from all hosts.
Ok, deep breath. We’re almost there!
Now that SSH access has been enabled from your machine to the EC2 instance, you can setup SSH forwarding (port forward). As a reminder, the port you choose from which to forward commands from your local machine (the -L parameter) is actually arbitrary, but it must:
- Match the port being used by your psql client.
- Not already be in use by another application. It’s possible that the psql client is already connected to a local PostgreSQL database which you were using for development. Just be sure to double check if you’re having connectivity issues.
Run the following command to setup the SSH tunnel:
# Setting up and SSH Tunnelssh -N -L localPort:rdsHost:remotePort user@remoteHost -i ~/path/to/keypair
And now, let’s go over the options and parameters (it’s important read this to understand what’s actually happening).
-N : Do not execute a remote command (useful for forwarding ports).-L : Forward a local port to a remote port.localPort: The local port on which you connect to your database. You can set this to any available port such as 1234, 5432, and so on.rdsHost: The endpoint (URL) of your RDS instance.remotePort: The port on which your remote database listensfor connections. For PostgreSQL databases, the default is 5432.user@remoteHost: The username (ec2-user is the default EC2 username) and the remote instance through which you will connect to the RDS instance/database. These are the same credentials you would use when attempting to SSH into your EC2 instance.-i : Identity (The keyfile/keypair's file location).
If the command runs successfully, the terminal will NOT return automatically. In order for your machine to continue forwarding these commands, you must keep this connection open (in other words, don’t close the terminal or discontinue the session). Your machine is now set up using SSH to forward commands from your local machine to the RDS instance, via the EC2 instance.
For the final step, open up a new tab/window in Terminal and run the psql command from earlier:
# IMPORTANT: The -h parameter is now 'localhost' (because you're forwarding commands made to -p on your local machine).# IMPORTANT: The -p parameter must match the localport you specified when setting up SSH forwarding using the command shown above.psql -h localhost -p 5432 -U userName dbName -W
Congratulations, you’re now connected!
Your psql commands will be forwarded to your database running on your RDS instance. We hope this article helped further your understanding of a few different topics: AWS, PostgreSQL, SSH, and more.
Comments & Questions
If you have any questions, please don’t hesitate to reach out to someone from our crew. You can get in touch via our email (email@example.com) or our studio’s contact page.
We live and breath technology at Deep Space Program and are always looking to share and learn from other developers, hobbyists, or whoever may want to share their love for technology.