The idea here is you set yourself up with an entry in your ~/.ssh/config with a LocalForward to the Postgres port on the host you want to connect to. (The SSH connection itself might be to the same host or a remote bastion. LocalForward is a honey badger.) Then, create or add to your ~/.pg_service.conf file with a service name that matches the host name from your SSH config. See examples below, and just edit the included dummy values to suit your environment. Note that matching the host names between the two config files is the key to making this work nicely.
(Which port you choose to forward is arbitrary, but it's a good idea NOT to use the default Postgres port of 5432, to avoid any potential conflict with any Postgres service you might have running locally.)
Once you have that configured, the teeny but mighty bash script below will create an auto-closing SSH tunnel and use it to connect to the database with psql. Note that following the host/service name, you may also pass whatever additional arguments you like to psql—so you can use this to execute files, commands with -c, etc.
Make the script executable, put it somewhere in your $PATH (personally, I like /usr/local/bin/pgst) and Bob's your uncle. You could also just make it a fuction in your .bashrc—I made it a script because bash isn't usually my login shell. I'm happy to help with any questions.
PS – in my mind, pgst is short for "Postgres secure tunnel" 🙂