When attempting to establish an SSH tunnel to a remote PostgreSQL server using pgAdmin3 or psql, you encounter the error:

server closed the connection unexpectedly
    This probably means the server terminated abnormally
    before or while processing the request

Based on your configuration, several potential issues need investigation:

• Insufficient pg_hba.conf entries for remote connections
• PostgreSQL service not listening on expected interfaces
• SSH tunnel configuration problems
• Firewall restrictions

Your current pg_hba.conf only allows:

local   all         all                               trust
host    all         all         127.0.0.1/32          trust

Add this line to allow tunneled connections:

host    all         all         ::1/128               md5

Check postgresql.conf for these critical settings:

listen_addresses = 'localhost,127.0.0.1'
port = 5432

For debugging, temporarily set:

log_connections = on
log_disconnections = on

Ensure your SSH command includes proper forwarding:

ssh -N -L 3333:localhost:5432 myUser@myHost

Key flags explanation:

• -N: Don't execute remote commands
• -L: Local port forwarding

Systematic verification steps:

# First test local connection on server
psql -h localhost -U myUser

# Then test through tunnel from client
psql -h localhost -p 3333 -U myUser

Check active connections on server:

sudo -u postgres psql -c "SELECT * FROM pg_stat_activity;"

Verify listening ports:

sudo netstat -tulnp | grep postgres

Here's a tested configuration that works:

# On server
sudo nano /etc/postgresql/12/main/postgresql.conf
# Set: listen_addresses = '*'

sudo nano /etc/postgresql/12/main/pg_hba.conf
# Add: host all all 0.0.0.0/0 md5

sudo service postgresql restart

# On client
ssh -f -N -L 3333:localhost:5432 myUser@myHost
psql -h localhost -p 3333 -U myUser -d myDB

• Verify PostgreSQL service is running
• Check logs: /var/log/postgresql/postgresql-12-main.log
• Test basic connectivity: telnet myHost 5432
• Confirm user privileges: \du in psql
• Verify tunnel: netstat -tulnp | grep 3333

---

When attempting to connect to a remote PostgreSQL database through an SSH tunnel, you might encounter the frustrating error:

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request

From your setup, I notice several important points:

• You're using pgAdmin3/psql to connect via SSH tunnel (localhost:3333 → myHost:5432)
• Your pg_hba.conf only allows local connections (127.0.0.1/32)
• Authentication method is set to 'trust' for local connections

The primary issue lies in your pg_hba.conf configuration. While your SSH tunnel is correctly forwarding the connection, PostgreSQL isn't configured to accept connections from your tunnel's endpoint.

Your current pg_hba.conf:

local   all         all                               trust
host    all         all         127.0.0.1/32          trust

You need to add a rule that accepts connections from the SSH tunnel. Add this line to your pg_hba.conf:

host    all         all         ::1/128               trust
host    all         all         127.0.0.1/32          trust

This covers both IPv4 and IPv6 localhost connections. After modifying the file, don't forget to reload PostgreSQL:

sudo systemctl reload postgresql  # For systemd systems
# OR
sudo service postgresql reload    # For init.d systems

If you prefer not to modify pg_hba.conf, you can connect directly through the Unix domain socket:

psql -h /var/run/postgresql -p 5432 -U myUser

If you're still having issues, check these logs:

# PostgreSQL logs (location varies by distro)
tail -f /var/log/postgresql/postgresql-12-main.log

# SSH tunnel debug
ssh -v -L 3333:localhost:5432 myUser@myHost

Here's a full example of establishing the connection:

# Terminal 1: Establish SSH tunnel
ssh -L 3333:localhost:5432 myUser@myHost

# Terminal 2: Connect using psql
psql -h localhost -p 3333 -U myUser -d myDB

# Or via pgAdmin3:
# Create new server connection with:
# Host: localhost
# Port: 3333
# Username: myUser
# Password: [if using password auth]

While 'trust' authentication is convenient for development, consider these security improvements for production:

• Use md5 or scram-sha-256 authentication
• Restrict access to specific databases/users
• Consider using SSH key authentication only