macOS下pgAdmin无法通过SSH连接Linode上PostgreSQL求助

Hey there, let's walk through this issue step by step—SSH tunnel problems with pgAdmin and PostgreSQL can feel frustrating, but we can narrow down the missing configuration piece together.

1. First, Confirm Your SSH Tunnel Is Actually Working #

Before blaming pgAdmin or PostgreSQL, let's make sure the tunnel itself is up and running:

• Open your terminal and run the tunnel command manually (replace placeholders with your details):

  ssh -L 5432:localhost:5432 your_linode_ssh_user@your_linode_public_ip
  

  If this throws errors (like connection refused, permission denied), your problem is at the SSH level:
  • Check if Linode's firewall allows incoming traffic on port 22.
  • Verify your SSH credentials (password or private key) are correct.
  • Ensure your local machine isn't blocking outgoing SSH connections (e.g., corporate firewall).
• To confirm the tunnel is listening locally, run:

  lsof -i :5432
  

  You should see an entry for ssh listening on localhost:5432.

2. Double-Check PostgreSQL's Local Access Configuration #

Since we're using an SSH tunnel, PostgreSQL only needs to accept local connections (no need to open it to the public internet). Here's what to verify:

• Edit postgresql.conf (location varies by version, e.g., /etc/postgresql/14/main/postgresql.conf):
  Make sure listen_addresses is set to localhost (this is default, but sometimes gets changed):

  listen_addresses = 'localhost'
  

• Edit pg_hba.conf in the same directory:
  Ensure there's a rule allowing local connections from 127.0.0.1 (the tunnel will appear as a local connection):

  host    all             all             127.0.0.1/32            scram-sha-256
  

  (Use md5 instead if you're on an older PostgreSQL version that doesn't support scram-sha-256.)
• Restart PostgreSQL to apply changes:

  sudo systemctl restart postgresql
  

3. Test Connection with Local psql First #

If the tunnel is up, test connecting directly with psql (this rules out pgAdmin-specific issues):

psql -h localhost -p 5432 -U your_postgres_username -d postgres

• If this fails, check PostgreSQL's logs for details (e.g., /var/log/postgresql/postgresql-14-main.log):
  Look for lines like FATAL: password authentication failed for user "your_user" or FATAL: no pg_hba.conf entry for host "127.0.0.1".
• If this works, then the problem is in your pgAdmin setup.

4. Fix pgAdmin's Connection Settings #

It's easy to mix up tunnel and direct connection settings in pgAdmin—here's the correct setup:

• Connection Tab:
  • Host name/address: localhost (not your Linode IP—this is the tunnel's local end)
  • Port: 5432 (or whatever port you used in your SSH tunnel command, e.g., 5433 if you avoided a port conflict)
  • Maintenance database: postgres (default)
  • Username: Your PostgreSQL database username
  • Password: The password for that PostgreSQL user
• SSH Tunnel Tab:
  • Check Use SSH tunneling
  • Tunnel host: Your Linode's public IP address
  • Tunnel port: 22 (default SSH port—change if you modified your Linode's SSH port)
  • Username: Your Linode SSH username
  • Authentication: Choose Password (if using password login) or Identity file (browse to your private key file if using key-based auth)

5. Common Pitfalls to Watch For #

• Port Conflict: If you have a local PostgreSQL instance running, it's using port 5432. Change your tunnel to use a different port, e.g.:

  ssh -L 5433:localhost:5432 your_linode_ssh_user@your_linode_public_ip
  

  Then set pgAdmin's port to 5433.
• SELinux/AppArmor: On your Linode, these security tools might block PostgreSQL connections. Temporarily disable them for testing (or add appropriate rules if needed).
• Password Mismatch: Ensure your PostgreSQL user's password is correctly set, and that the pg_hba.conf auth method matches the password's encryption (e.g., scram-sha-256 requires the user's password was created with that method).

Start with verifying the SSH tunnel and local psql connection—once those work, pgAdmin should connect without issues.

内容的提问来源于stack exchange，提问作者Daniel da Rocha