docs
GitHub CI/CD Deployment
DB Access with SSM
psql

psql

psql is a command-line tool for interacting with PostgreSQL databases. It allows users to execute SQL queries, manage database objects, and perform administrative tasks directly from the terminal. It's widely used for connecting to PostgreSQL databases, running queries, and automating database management tasks.

Step 1: Check you have psql installed

psql --version

If you don't see output like psql (PostgreSQL) 14.13 (Homebrew), please install psql (opens in a new tab).

Step 2: Make Sure You Have a Login Session Alive

Before starting the session, ensure your AWS Single Sign-On (SSO) login session is active. Run the following command:

aws sso login --profile [PROFILE]
 
i.e. 
 
aws sso login --profile awslaunchgoat-staging

Step 3: Start the SSM Session to Your EC2 Instance

Once you're logged in, initiate the port-forwarding session using the following command. This sets up a secure tunnel between your local machine and your RDS instance through an EC2 instance.

aws ssm start-session \
 --target [EC2-INSTANCE-ID] \
 --document-name AWS-StartPortForwardingSessionToRemoteHost \
 --parameters '{"host":["[HOST]"],"portNumber":["[DB-PORT]"],"localPortNumber":["[DB-PORT]"]}' \
 --profile [PROFILE] \
 --region [REGION]

i.e.

 aws ssm start-session \
 --target i-0d7428d804f21205e \
 --document-name AWS-StartPortForwardingSessionToRemoteHost \
 --parameters '{"host":["cdkstack-rdsnestedstackrdsnest-rdsinstance9f6b765a-u99tmvyib4fq.cr2gsoc46kml.us-east-1.rds.amazonaws.com"],"portNumber":["5432"],"localPortNumber":["5432"]}' \
 --profile awslaunchgoat-staging \
 --region us-east-1

Refer to this to get the values for EC2-INSTANCE-ID, HOST, and DB-PORT from Secrets Manager and EC2.

  • [EC2-INSTANCE-ID]: The ID of your EC2 instance.
  • [HOST]: The private DNS or IP address of your RDS PostgreSQL instance.
  • [DB-PORT]: The port number your RDS PostgreSQL is running on (default is 5432).
  • [PROFILE]: Your AWS CLI profile name.
  • [REGION]: The AWS region where your EC2 and RDS instances are located.

This command forwards the specified port from your RDS instance to your local machine via the EC2 instance.

Create Permission Set

Step 4: Connect to PostgreSQL from Another Terminal

In a new terminal window, use the following psql command to connect to the database:

psql -h localhost -p [db-port] -U [username] -d [dbname]
 
i.e. 
 
psql -h localhost -p 5432 -U launchgoatStagingAdmin -d launchgoatStaging

Get the values for db-port, username, dbname from Secrets Manager.

Create Permission Set

Step 5: Enter the Password

When prompted for a password, enter the database password. You can retrieve it from Secrets Manager. Just copy and paste it.

Step 6: Run SQL Commands

After a successful connection, you can start running SQL commands. Here are some basic commands to get you started:

  • To list tables in the database:
\dt
  • To select all records from a table (i.e. User table):
SELECT * FROM "User";

Create Permission Set

Summary

This method provides a highly secure way to access your database. By following these steps, you can establish a secure connection to your private RDS PostgreSQL instance through an EC2 instance using AWS SSM, ensuring that your database remains protected while still accessible for administrative tasks.

IntropgAdmin 4