Unable to connect AWS Postgres database using ssh tunnel

Hi Folks,

  • Goal: We are trying to connect our AWS hosted Postgres database using ssh tunnel and bastion host as our database isn't publicly accessible

  • Steps:

  1. We have created a Amazon linux bastion host by completely following this document(Configure SSH tunneling for resources | Retool Docs) and kept open 22 port to open to the world. We had a plan to restrict the IP's in our bastion host's security group (firewall) when we could able to connect with our database.
  2. We provided all the information correctly in the connection details user input (screenshot-1)
  3. We weren't able to connect with our database and got this error "All configured authentication methods failed"
  4. We have tested database connection by using ssh tunnel with the exact same bastion host and retool user. In this case, we have created ssh keypair and add public key into retool user's ~/.ssh/authorized_keys and successfully we were able to connect with our database.
  5. Above test (step-4) proves that bastion host and database connectivity should work as expected if the provided public key is correct.
  • Details:
  1. Would anyone please investigate the retool's public(provided)/private keypair has issue or not?
  2. We got an extra % at the end of provided public key file (screenshot-2). Is this could be a problem?

Hi @Mafuj_Jia.

The issue is that the public/private key pair doesn't match. I checked our file and, it doesn't have a %. IDK how it got in there but can you remove it and test again, or try to download the pub file again?

Thank you for your reply. Would you please provide me the correct public key file as you said public/private key doesn't match?

I tried with this downloaded pulickey
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQC8TveEKJg5mYYOYL8PfQPLvEKunVbxrAafOY17eJ1NK6fP6ne+KFWqJDQWbx0VbnMqmZk2OFZXDHubxrNhtlIEyNCjLjjBMTlXsICyfSf05intlUszz/8kRGO1qqSoMbMmyguNBLroTyjzvv+/7X34lYZxkhSCZtpNl96FIlc+27bctfqKNntjUZNX58uIa9jcw45hyeCoA3qT2Luqrwu1MEOlv6UW+qvw9Sci7lCYLbcG/wGoaHId6TfgFV+i0K8ychEJQFWXELZ/ZIbrohyx8S1NEDoTjXgwHPszyGSwk4oXqJts5RgBcgEvVl/F280wXrbYbRVtac/AxvI98qv/34WgCJWVICQ7nzxpDHxt4E+yZ8FeZZAj6G7sV2qfu2tDMQMFSpZu5T/6OYfTZacHKBuMUAKg5jpCT+4oCfLHK7ESCAY/65fS/Gb8LQ3FVQeoNUW665zU5J6DcuoLVRcpdcrwbKVlLtOtiAqa1kHgAK52QQRwmJw9cCQfwFexHiv4lj5b9UYcoMnih/uVrZEm1ZQhSPv3TgIfBHJq4XqPVVf7DeJxawCTteVc5YpBA8ZkgTx5lY8c5VG18ZR7nqyythoq0WSfZFS9BaulZcFPIrYB4tUTslPan1T1dPYFj7HJZdSsI7/ENO68ioOcWr/Qc3a23JpSMW677xGpSI0X1w== retool@trykintsugi

But no luck! I don't have corresponding privatekey so that I can't test. Is anyone from retool admin who can ensure that keypair is correct?

Hi Folks, May I get any reply on this topic?

@Mafuj_Jia Sorry I'm unable to help, things seems fine if you've done all the setups we suggested. I'll ask a more knowledgeable teammate about this to chime in.

Hey folks - I've never seen a mismatched key pair in any of the organizations on our cloud platform, it has always been something else with configuration in every case I've been involved with. So before going down that rabbit hole, have we looked at the bastion host's SSH logs to see if there's more information on why it's failing?

In fact, it's definitely not an issue with the key. Works just fine testing a Postgres connection from your org through a bastion I set up. If you're on OpenSSH 8.7+, did you add the key type as noted here?

Hi jmann, I am not sure how have you been setup bastion host as bastion host needs to be setup inside the same VPC where the private postgres instance located. Anyway, As you mentioned the step here I followed exactly same steps and try again but no luck! I am providing you all the screenshots steps.


As you have connected already, Would you please mention which step I am missing?

This worked without any additional configuration on an Ubuntu server, and we're not even at the point of trying to connect to your private Postgres host yet. So seems like something with your Amazon Linux server configuration. Can you attach your full sshd_config file, as well as your SSH logs capturing an example failed connection attempt?

Hi @jmann , Thank you for your reply. I don't have any problem to use ubuntu server as bastion host. I will install a bastion host by using ubuntu server and get back to you with all details if we don't find with any issue with the attached sshd_config file.

[root@ip-10-0-0-31 ssh]# cat sshd_config
#	$OpenBSD: sshd_config,v 1.104 2021/07/02 05:11:21 dtucker Exp $

# This is the sshd server system-wide configuration file.  See
# sshd_config(5) for more information.

# This sshd was compiled with PATH=/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin

# The strategy used for options in the default sshd_config shipped with
# OpenSSH is to specify options with their default value where
# possible, but leave them commented.  Uncommented options override the
# default value.

# To modify the system-wide sshd configuration, create a  *.conf  file under
#  /etc/ssh/sshd_config.d/  which will be automatically included below
Include /etc/ssh/sshd_config.d/*.conf

# If you want to change the port on a SELinux system, you have to tell
# SELinux about this change.
# semanage port -a -t ssh_port_t -p tcp #PORTNUMBER
#Port 22
#AddressFamily any
#ListenAddress ::

#HostKey /etc/ssh/ssh_host_rsa_key
#HostKey /etc/ssh/ssh_host_ecdsa_key
#HostKey /etc/ssh/ssh_host_ed25519_key

# Ciphers and keying
#RekeyLimit default none

# Logging
#SyslogFacility AUTH
#LogLevel INFO

# Authentication:

#LoginGraceTime 2m
#PermitRootLogin prohibit-password
#StrictModes yes
#MaxAuthTries 6
#MaxSessions 10

#PubkeyAuthentication yes

# The default is to check both .ssh/authorized_keys and .ssh/authorized_keys2
# but this is overridden so installations will only check .ssh/authorized_keys
AuthorizedKeysFile	.ssh/authorized_keys

#AuthorizedPrincipalsFile none

# For this to work you will also need host keys in /etc/ssh/ssh_known_hosts
#HostbasedAuthentication no
# Change to yes if you don't trust ~/.ssh/known_hosts for
# HostbasedAuthentication
#IgnoreUserKnownHosts no
# Don't read the user's ~/.rhosts and ~/.shosts files
#IgnoreRhosts yes

# Explicitly disable PasswordAuthentication. By presetting it, we
# avoid the cloud-init set_passwords module modifying sshd_config and
# restarting sshd in the default instance launch configuration.
PasswordAuthentication no
PermitEmptyPasswords no

# Change to no to disable s/key passwords
#KbdInteractiveAuthentication yes

# Kerberos options
#KerberosAuthentication no
#KerberosOrLocalPasswd yes
#KerberosTicketCleanup yes
#KerberosGetAFSToken no
#KerberosUseKuserok yes

# GSSAPI options
#GSSAPIAuthentication no
#GSSAPICleanupCredentials yes
#GSSAPIStrictAcceptorCheck yes
#GSSAPIKeyExchange no
#GSSAPIEnablek5users no

# Set this to 'yes' to enable PAM authentication, account processing,
# and session processing. If this is enabled, PAM authentication will
# be allowed through the KbdInteractiveAuthentication and
# PasswordAuthentication.  Depending on your PAM configuration,
# PAM authentication via KbdInteractiveAuthentication may bypass
# the setting of "PermitRootLogin without-password".
# If you just want the PAM account and session checks to run without
# PAM authentication, then enable this but set PasswordAuthentication
# and KbdInteractiveAuthentication to 'no'.
# WARNING: 'UsePAM no' is not supported in Fedora and may cause several
# problems.
#UsePAM no

#AllowAgentForwarding yes
#AllowTcpForwarding yes
#GatewayPorts no
#X11Forwarding no
#X11DisplayOffset 10
#X11UseLocalhost yes
#PermitTTY yes
#PrintMotd yes
#PrintLastLog yes
#TCPKeepAlive yes
#PermitUserEnvironment no
#Compression delayed
#ClientAliveInterval 0
#ClientAliveCountMax 3
#UseDNS no
#PidFile /var/run/sshd.pid
#MaxStartups 10:30:100
#PermitTunnel no
#ChrootDirectory none
#VersionAddendum none

# no default banner path
#Banner none
PubkeyAcceptedKeyTypes +ssh-rsa
# override default of no subsystems
Subsystem	sftp	/usr/libexec/openssh/sftp-server

# Example of overriding settings on a per-user basis
#Match User anoncvs
#	X11Forwarding no
#	AllowTcpForwarding no
#	PermitTTY no
#	ForceCommand cvs server

AuthorizedKeysCommand /opt/aws/bin/eic_run_authorized_keys %u %f
AuthorizedKeysCommandUser ec2-instance-connect

I've seen certain default configurations pull in config from other places, and looks like yours is with this line. What are in those .conf files?

Include /etc/ssh/sshd_config.d/*.conf

Pretty sure SSH doesn't append configuration or anything after it initially finds values for settings. So would either remove that reference or put the PubkeyAcceptedKeyTypes +ssh-rsa line at the very top of the file. Then of course restart the sshd service after making those changes.