How to Connect pgAdmin 4 to a Private Amazon RDS for PostgreSQL Database via AWS SSM

This post I assume you already use Cloud9.

Step 1. Create IAM Role: SSM

IAM -> Roles -> Create Cloud9SSMRole

Reference: https://docs.aws.amazon.com/en_en/systems-manager/latest/userguide/systems-manager-setting-up-messageAPIs.html

Role: Cloud9SSMRole
Attached policy: AmazonSSMManagedInstanceCore
Trusted service: ec2.amazonaws.com

Step 2. Attach the IAM role to the Cloud9 EC2 instance

EC2 -> Instances (Check Cloud9 EC Instance, set IAM Role)

Step 3. Activate and verify the SSM Agent

Cloud9 EC2 instance already included the official Amazon SSM Agent. Enable and start the agent so the instance can register with AWS Systems Manager.

sudo systemctl enable --now amazon-ssm-agent
sudo systemctl status amazon-ssm-agent --no-pager

Step 4. Verify the EC2 instance in Systems Manager

Open AWS Systems Manager and navigate to Explore nodes. Verify that the Cloud9 EC2 instance appears as an online managed node.

If the instance does not appear, confirm that the EC2 IAM role includes AmazonSSMManagedInstanceCore and that amazon-ssm-agent is running.

Step 5. Verify the AWS CLI and Session Manager Plugin

Make sure aws-cli and session-manager-plugin are installed on your Mac.

aws --version
session-manager-plugin
aws sts get-caller-identity

Step 6. Start the SSM port-forwarding tunnel

Open your terminal app on your Mac, make sure your Cloud9 EC2 instance remains running before forwarding SSM tunnel. Cloud9 auto-hibernation can stop the backing EC2 instance and terminate database access

aws ec2 start-instances \
  --region <AWS_REGION> \
  --instance-ids <CLOUD9_EC2_INSTANCE_ID>

Then, run it for forwarding tunnel.

aws ssm start-session \
  --region <AWS_REGION> \
  --target <CLOUD9_EC2_INSTANCE_ID> \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters '{"host":["<PRIVATE_RDS_ENDPOINT>"],"portNumber":["5432"],"localPortNumber":["15432"]}'

Step 7. Register the RDS connection in pgAdmin

Open pgAdmin App, register RDS Server

You don’t have to set SSH Tunnel, check Red Boxes only

Step 8. Verify the connection

References

• AmazonSSMManagedInstanceCore Policy
• Attach an IAM Role to an Existing EC2 Instance
• Working with AWS Systems Manager SSM Agent
• Install the Session Manager Plugin on macOS
• Verify the Session Manager Plugin Installation
• Start a Session and Use Remote-Host Port Forwarding
• Troubleshooting Session Manager