Postgres connect over ssh

Question

I have PostgreSQL server running on some host pgserver. This server is not opened to the outside world (only connections from localhost are allowed). I can login to that host as user via ssh (with public key):

me@local:~ $ ssh user@pgserver

Then I can su to specific user pguser to run queries.

user@pgserver:~ $ sudo su pguser
pguser@pgserver:~ $ psql

I need to enter user's sudo-password here. I can't connect as pguser and don't know his password. I also don't have access to the database as user.

Now to simplify development I would like to setup ssh-tunnel from my local machine to the pgserver:

me@local:~ $ ssh -L localhost:5432:localhost:5432 user@pgserver

The problem is that while user has access to the server, he doesn't have access to database. pguser has it, but doesn't have access to the server. What is frustrating is that I can actually sudo to pguser's account and run queries after I've connected as user.

Can I solve this in some way?

Answer 1

Try

ssh -t -l user pgserver sudo -u pguser psql

The -t forces ssh to allocate a pty on the other end so there's a terminal for password input and such.