How to Access a PostgreSQL Database using PgAdmin 4 with SSH Tunneling

In modern DevOps and cloud-native workflows, managing remote PostgreSQL databases efficiently and securely is essential. One of the best tools for interacting with PostgreSQL is PgAdmin 4, a feature-rich web-based GUI. This blog will guide you through installing PgAdmin 4, generating SSH keys, and connecting securely to a remote PostgreSQL using SSH tunneling.

🎯 Why Use SSH Tunneling with PgAdmin?

Directly exposing your database to the internet is a major security risk. By using SSH tunneling, you can:

• Keep your database closed to the public internet.

• Authenticate securely using asymmetric encryption (public/private keys).

• Minimize attack vectors on your infrastructure.

SSH tunneling creates a secure encrypted tunnel between your local machine and the remote database server, using a bastion (jump) host on GCP.

🧰 Step-by-Step Setup

🧩 1. Install PostgreSQL with PgAdmin 4

PgAdmin 4 is bundled with the PostgreSQL installer. Use the link below to download:

🔗 Official PostgreSQL + PgAdmin Download

Installation Steps:

1. Launch the setup installer.

2. Click Next through the options.

3. Set a secure password, e.g., db@123.

4. Accept default port 5432 and locale.

5. Finish the installation and skip Stack Builder.

🔍 Tip: You can change the superuser password later via the SQL shell or PgAdmin.

🔑 2. Generate an SSH Key Pair

SSH keys are a secure alternative to passwords for authenticating with remote servers.

mkdir D:\postgres_db_ssh_folder; ssh-keygen -t rsa -b 4096 -C "your-email@example.com" -f D:\postgres_db_ssh_folder\postgres_db_ssh

What this does:

• Creates a folder D:\postgres_db_ssh_folder.

• Generates a 4096-bit RSA key pair:

  • postgres_db_ssh.pub → Public key

  • postgres_db_ssh → Private key

💡 Best Practice: Never share your private key. Keep it secure and consider using a passphrase for extra protection.

☁️ 3. Upload Public Key to Bastion/Jump Host Virtual Machine Metadata

To allow your local machine to SSH into the GCP-hosted PostgreSQL server:

1. Open postgres_db_ssh.pub in a text editor.

2. Copy the entire content.

3. Upload it to the bastion host.

🔌 4. Connect to PostgreSQL via PgAdmin 4

Now that your SSH key is authorized, let’s set up PgAdmin:

✅ Register New Server

1. Open PgAdmin 4

2. Right-click Servers → Register → Server

General Tab:

• Name: postgres-prod-db

Connection Tab:

• Host: <Your-DB-VM-Private-IP> (localhost proxy via SSH)

• Port: 5432

• Maintenance DB: postgres

• Username: postgres

• Password: <Password to connect to the database>

SSH Tunnel Tab:

• Tunnel Host: <Your-Bastion-Host-Public-IP> (external IP of GCP VM)

• Tunnel Port: 22

• Authentication: Identity file (upload the private key file postgres_db_ssh)

• Password: Same one used during PgAdmin setup (db@123)

Click Save.

🧪 Common Issues and Troubleshooting

🛡️ Security Tip: Never commit SSH private keys to version control systems like GitHub.

🔚 Conclusion

SSH tunneling with PgAdmin 4 is an effective way to securely manage remote PostgreSQL databases. It balances usability and security, allowing developers to access production-grade databases without compromising infrastructure safety.