I have a postgres database running on a linux EC2 instance. I want to view the data inside the tables, and currently the only way I can do that is via command line on the host server by:
sudo su postgres
psql
\c database
With the above, I am able to connect to the database and run whatever sql query I want, however this is impractical as it's just a text dump and the tables are formatted incorrectly across my screen due to monitor size limitations.
I've been trying to set up PgAdmin4 to connect to the EC2 postgres process via ssh tunnel, however have not been able to establish a connection. To log into the server normally as the default ubuntu user, we use an identity .ppk file provided by AWS of which I have converted into a .pem file.
Under the Connection tab, I have the following:
And the SSH Tunnel Tab:
I don't use a password to log into the server as the identity file takes care of that for me.
In the SSH Tunnel tab, I have tried setting the Tunnel Host to localhost however recieve the error:
Unable to connect to server: Failed to create SSH tunnel. Error: Could not establish session to SSH gateway.
And then if I try setting Tunnel Host to ec2-blah-blah.compute.amazonaws.com I recieve:
Unable to connect to server: connection to server at "127.0.0.1" port 23059 failed: server closed the connection unexpectedly. This probably means the server terminated abnormally before or while processing the request.
Is there a configuration error I am missing here? Or maybe something on the server side? I've also modified pg_hba.conf to allow 0.0.0.0\00 and commented out host ... 127.0.0.1 ...
sudoto connect when you are logged in locally, that won't work over the tunnel. You need to change something so that it is no longer necessary to use sudo. Like change the authentication method from peer to something else, or create a db user named 'ubuntu'.