How do I connect to the RDS instance?

Setup and SSH tunneling to the Moped RDS instance

Moped runs on a PostgreSQL database in AWS, but it is not publicly accessible. It currently lives in a closed (private) network, where only certain servers have access to it. One of those servers is called the moped bastion host. This server is just a small virtual machine (EC2 instance) on AWS, and it is carefully placed in the same network and VPC as the database, and it acts as a proxy.

In short, to connect to the PostgreSQL database, you will need two sets of credentials:

1. An SSH Key. The ssh key needs to be generated by you in your computer, and an admin needs to set up that key in the bastion host for you to have access. The SSH tunnel will be taken care of by your PostgreSQL client.

2. The PostgreSQL username/password. Once your machine is connected to the bastion host, you will be able to directly connect against the PostgreSQL client.

1. Generate SSH Keys

Apple MacBook, iMac, etc.

1. Be sure to be in your ~/.ssh/ directory using the command $ cd ./ssh/

2. To generate an ssh, run these command: $ ssh-keygen -t rsa

3. The command is going to prompt you to enter the name of the name of the file, try: moped_bastion

4. Then the rest of the questions can be blank, just hit enter.

5. # Example steps:
   $ cd ~/.ssh/
   $ ssh-keygen -t rsa
   Generating public/private rsa key pair.
   Enter file in which to save the key (/Users/sergiogc/.ssh/id_rsa): moped_bastion
   Enter passphrase (empty for no passphrase):
   Enter same passphrase again:
   Your identification has been saved in moped_bastion.
   Your public key has been saved in moped_bastion.pub.
   The key fingerprint is:
   SHA256:MMCYGck08OGKA2d5UJmhQ5L+c4/vfgFT9oSfxsbNJtI [e-mail]
   The key's randomart image is:
   +---[RSA 3072]----+
   |o=BO++    .      |
   |.=**+.   + .     |
   |o O . o o B +    |
   |o= o   = . E +   |
   |+ .     S + o    |
   | . o .   .       |
   |    o o   .      |
   |     . . .       |
   |      ++.        |
   +----[SHA256]-----+

6. # Check the files were generated, and the permissions look like these:
   $ ls -lha moped_bastion*
   -rw-------  1 sergiogc  staff   2.6K Dec 17 15:54 moped_bastion
   -rw-r--r--  1 sergiogc  staff   587B Dec 17 15:54 moped_bastion.pub

Demonstration

Provide Final Key to Admin

The final key is the .pub file you created, it should look something like this:

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQDGqkNuw65U6jJsxRhTzR/VqHQXq3VEF3syCg9Kzx
6oVNZPpPu4OteT3oBOocJEjPXI5zPZb07KYdoHo9ErP9OoJfBqq2NXHlPeSakWHAPBE25a7ZdeekpI
yTVCgo5Q25LNbGeX8neU//3x7/I/wEjeTMoyLgWgPt/IwEdsGGT2Kt3VvoK3Vn7fK4vxQ3FgjyWsdm
uWIeVDFu7lK/qTmoHbUYWIEgeeL9MLrGay4J7IyNsoF8TxtI2zZmpzhaFhsYYdo0IlvmWbFcRMn3Xp
AT4izGA//JCYevCtV882p4X8Chs/NXApB9cD90rJ9qO8BkdXR/fbwWdQ9+qDduR045DlNpxdLvxwlD
Cz4XoM1RAGbBFX2Et7Qi2AplKxaMNZvFLEdmOkwfBus/45lt2vFGmDuQ7soSGdvtk95GFEt1g+TanV
wvOvBRgyFdyIPAIyNCw8bffmM/A+rvMG2d4n4aEmG62Qyt1bxc/M0lS3BIop0xbi+xNUHgffpUk/b2
g6e40= [e-mail]

Test Your Access

Once you submit the key to an administrator, you will be able to test access to the server. To test the key works correctly, all you need to do is run this command:

$ ssh -i ./ssh/rds_bastion -T [e-mail]

2. Open the tunnel

⚠️ There are few tasks that require directly accessing production databases. If you can achieve what you are doing with read only access, always us a Read Replica! ⚠️

1. To access the staging or production database directly, you must:

   1. Have your IP added to the Inbound Rules of the atd-moped-ssh-rds-production security group attached to the RDS Bastion Host

   2. Have your RSA key added to the RDS Bastion Host (described above)

2. After that is done, you can use the ssh command and the -L flag to map a local port to port 5432 of the RDS instance through a session connected to the bastion host.

3. To do this, run:

   $ ssh -L 5433:<the RDS endpoint>:5432 <your username as set up on the bastion host EC2>@rds-bastion.austinmobility.io

4. Note: The 5433 port in the previous command is a protection from accidentally connecting to the RDS through a client or script that is targeting the local 5432 port which is the default for PostgreSQL. HT to Frank Hereford for this!

5. Now that you have opened the tunnel, you can target localhost port 5433 with a SQL client or script to interact with the database (carefully!!!). The username and password for the database is stored in 1Password under the name AWS RDS Moped.