Amazon-ssm-agent: Database connection via bastion host tunnel to RDS; Slow and lost connections

[marcelboettcher]

Hey guys,

I tried following setup:

localhost -> PortForwarding via SSM Session Manager to EC2 Bastion Host -> Tunnel via socat to RDS

Version: amazon-ssm-agent-2.3.842.0-1.x86_64

Problem: I can connect to by rds database from localhost via multiple db client tools (ui). But I cannot run sql queries. They just timeout.
Any idea why this happens? Is the setup not able to handle the databse connection correctly?

Details:

localhost -> PortForwarding via SSM Session Manager to EC2 Bastion Host
aws ssm start-session \
--target ${INSTANCE_ID} \
--parameters "{\"portNumber\":[\"${INSTANCE_PORT}\"], \"localPortNumber\":[\"${LOCAL_PORT}\"]}" \
--document-name AWS-StartPortForwardingSession \
--profile ${AWS_PROFILE} \
--region ${AWS_REGION}

Bastion Host -> Tunnel via socat to RDS
sudo socat -d -d TCP4-LISTEN:8888,fork TCP4:xyz.eu-central-1.rds.amazonaws.com:5432

[marcelboettcher]

I tested a bit more and I think the problem is that the ssm session just allows one connection. E.g. I cannot connect twice via psql cli.
I guess that my client ui tool (pgadmin 4, DBVisualizer, DBNavigator, ....) try to open a second connection to run queries and keep the first one to show meta data.

Could you please tell me, if multiple parallel connections are possible via ssm sessions?

[baron42bba]

You are right that you can have only one connection per port.
But that issue is independent of ssm.
If you use ssh port forwarding you run into the same issue.

[marcelboettcher]

@baron42bba Thank you! Is their any work-around to allow multiple connections?

[baron42bba]

To be honest I don't see ssm as a full replacement.
Think about it: you encapsulate ssh in ssm in https.
It is ok for some small file transfers but anything bigger - and your client ui tools are bigger in that context - is not performing.
I made a test of using gnu screen locally running a ssm session and starting the 'rz' command remotely. gnu screen has build in support for transferring files in zmodem protocol.
The speed was a shame.
In this configuration you just directly transfer the data in the ssm session. no extra protocol layers involved.
What's working for me here is editing text files remotely via Emacs TRAMP mode. (in combination with aws-gate). But there we have relatively small files of maybe 5MB max.
open a ssm connection, start something like tmux and run your sql command line client.
For anything else find a different approach.
Maybe configure openvpn on an instance to reach your databases in a safe manner.

[NicolasKarnick]

Hi Marcel, we had this kind of issue like a minute ago 😄 .
We too have this kind of configuration: Local<-->SSM-Agent (Ec2-instance)<-->RDS-Instance

Connection between SSM-Agent (Ec2-instance)<-->RDS-Instance over socat like you've written obove.

Connection between Local<-->SSM-Agent (Ec2-instance) via portforwarding command.

You are right, ssm allows only one connection. And you are right that your client tools try to open up a second connection for metadata or sql-queries.

We are using dbeaver and in the window->preferences->dbeaver->metadata there's a checkbox like "open own connection for metadata queries" (aus Hamburger Sicht "Eigene Verbindungen für Metadatenabfragen öffnen")
And that the sql queries won't open seperate connection:
window->preferences->DBeaver->Editors->SQL-Editor->like "open seperate connection for each editor" (first checkbox) bzw. "Öffnen Sie eine seperate Verbindung für jeden Editor"

With these checkboxes unchecked, sql queries work and the client is using only one connection.

[ptrhck]

@NicolasKarnick

Thanks for your hint! I think that I am having the same issue with pgadmin. Do you know if I can change this behaviour in pgadmin as well?

[NicolasKarnick]

Unfortunately, i dont know if theres an option in pgadmin as well.

[nitikaaws]

Port Forwarding feature now supports multiple simultaneous connections over the session which should potentially solve the issue reported here. Please upgrade to latest version of Session Manager Plugin and SSM Agent to use this enhancement. Let us know in case of further issues.

[setiadywendy]

hi @marcelboettcher and @NicolasKarnick sorry for asking OOT, could you please share me how to connect dbeaver using SSM? I tried to look around and did some trial, but failed to do the tunneling via SSM. any pointer or guide will be really helpful, thanks 🙇

[NicolasKarnick]

hi @marcelboettcher and @NicolasKarnick sorry for asking OOT, could you please share me how to connect dbeaver using SSM? I tried to look around and did some trial, but failed to do the tunneling via SSM. any pointer or guide will be really helpful, thanks 🙇

hi @setiadywendy , in Dbeaver, the host is "localhost" in your connection settings. If you have an open tunnel with ssm, it should work. Remember to have a connection with socat between ec2<->rds instance.

For troubleshoot, try to find out where the connection problem is. between local<->ec2 or between ec2<->rds

[setiadywendy]

hi @setiadywendy , in Dbeaver, the host is "localhost" in your connection settings. If you have an open tunnel with ssm, it should work. Remember to have a connection with socat between ec2<->rds instance.

For troubleshoot, try to find out where the connection problem is. between local<->ec2 or between ec2<->rds

thanks for the answer @NicolasKarnick. but sorry I don't get your explanation. my problem is, I can't even connect dbeaver to the ec2 through SSM 😢 . would you mind to share your connection settings example? and perhaps some guides that related with this. I tried to send the SSM command through dbeaver shell commands connection setting, but I just realised it's really stupid 🤣

[NicolasKarnick]

Hi @setiadywendy , sorry for the delay. There arent any big settings. The settings mentioned above are the ones for ssm. You start a normal shell and paste the ssm commands there. And in dbeaver you set localhost as host.
Sorry but thats all I can do for you.
Maybe you start first with ssm and try to get a connection to your ec2 instance (no dbeaver needed here). And when you are on your ec2 instance, try to get a connection to your rds instance with an sql client (installed on the ec2 instance). when both work, you can try to establish a connection with dbeaver.

[setiadywendy]

no problem @NicolasKarnick , really appreciate you still wanted to help me 🙇 .

yes, I can connect to my EC2 via SSM, and from the EC2 I can connect to my RDS as well.
for this part And in dbeaver you set localhost as host, is the configuration like my SS below? but I am pretty sure there is something wrong with mine because I haven't set where is my RDS host 🤔 . and should I start it from the dbeaver from terminal? or I could just click dbeaver logo like starting other apps normally.

[NicolasKarnick]

hi @setiadywendy , the configuraton looks good. you can start dbeaver as normal. You said I havent set where my RDS host is. Thats the magic of the socat process on your ec2 instance. this process has to be running. It forwards all incoming traffic on the given port (in the above example its 8888) and forward it to the rds instance. so these settings in dbeaver should be sufficient.

[setiadywendy]

ahh got it @NicolasKarnick, so it's because I don't have any socat yet. do you have any recommendations regarding the socat setup tutorial? or any kind of tutorial is fine?

and also I wanna confirm something, so both the socat and dbeaver should be running in the ec2 instance instead of our local machine? previously I have assumption that the configuration is done in our own laptop / PC