Securing Remote PostgreSQL Connections with SSH Tunnels

What You’ll Need

• A cloud server running PostgreSQL on Hetzner VPS, Contabo VPS, or DigitalOcean
• A domain name managed through Namecheap pointed to your database host IP address
• An automated workflow engine such as n8n Cloud or a local development workstation needing access
• OpenSSH client installed on your local machine and OpenSSH server running on your remote database host
• Standard PostgreSQL client tools like psql, DBeaver, or PgAdmin installed on your local environment

Table of Contents

• The Security Risks of Exposing PostgreSQL Directly
• Configuring PostgreSQL and Host Security Rules
• Establishing Local SSH Tunnels for PostgreSQL Access
• Automating Tunnels in Python with sshtunnel and psycopg2
• Hardening SSH Configurations for Port Forwarding Only
• Getting Started

The Security Risks of Exposing PostgreSQL Directly

Exposing a database port such as default port 5432 directly to the public internet creates a massive attack surface. Automated port scanners constantly sweep IPv4 ranges looking for open database endpoints. Once discovered, your database host becomes subject to brute-force authentication attacks, zero-day exploit probes, and potential Denial of Service (DoS) conditions.

Even if you configure complex passwords and strong roles within PostgreSQL, exposing native TCP sockets means relying solely on the database process to defend itself against transport-layer attacks. Furthermore, unencrypted database connections pass queries, tables, and sensitive user records in plain text across public routers. While native SSL/TLS encryption for PostgreSQL solves wire-eavesdropping, managing public SSL certificates for raw database ports adds operational overhead and renewal failure points.

Running automated database services without public exposure also simplifies system observability. When your database receives unwanted network traffic, server resources degrade rapidly. If you are Configuring Vector for Centralized Log Aggregation, you will quickly notice your log files ballooning with failed authentication attempts from thousands of unauthorized IP addresses. Encapsulating database traffic inside SSH tunnels ensures that your database software never interacts with public packets.

Configuring PostgreSQL and Host Security Rules

The most effective security posture is binding PostgreSQL strictly to local interfaces and enforcing firewall rules to drop incoming requests on port 5432.

When setting up your infrastructure on Hetzner VPS, start by altering the main PostgreSQL configuration file. Locate your postgresql.conf file, which typically resides in /etc/postgresql/16/main/postgresql.conf on Debian and Ubuntu systems.

Edit the file to restrict listen_addresses to loopback interfaces only:

listen_addresses = 'localhost, 127.0.0.1'
port = 5432
max_connections = 100
shared_buffers = 128MB

Next, update the Host-Based Authentication configuration in pg_hba.conf to ensure local unix sockets and loopback connections are granted authentication privileges, while rejecting external network segments:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

Restart the PostgreSQL service to apply the configuration:

sudo systemctl restart postgresql

Verify that PostgreSQL is listening exclusively on the loopback interface using netstat or ss:

sudo ss -tulpn | grep 5432

The output must show 127.0.0.1:5432 or [::1]:5432. If it shows 0.0.0.0:5432, the server is still listening on all network interfaces.

Finally, block all external access to port 5432 using UFW (Uncomplicated Firewall). Ensure SSH access remains open on your designated SSH port (default 22) before enabling the firewall:

sudo ufw allow 22/tcp
sudo ufw deny 5432/tcp
sudo ufw enable
sudo ufw status verbose

This defense-in-depth layout mirrors production strategies used when Deploying Activepieces on Hetzner Using Docker Compose, where internal application containers communicate across isolated virtual networks or localhost loops rather than public interfaces.

💡 Fast-Track Your Project: Don’t want to configure this yourself? I build custom n8n pipelines and bots. Message me with code SYS3-HUGO.

Establishing Local SSH Tunnels for PostgreSQL Access

An SSH tunnel works by opening an encrypted connection between your local machine and the remote server. The local SSH client listens on a chosen local port, encrypts all traffic sent to that port, forwards it over the SSH connection, and has the remote SSH daemon deliver the decrypted traffic locally to PostgreSQL.

Manual CLI Tunnel Command

Execute the following command on your local machine to establish an ad-hoc SSH tunnel:

ssh -N -L 5433:127.0.0.1:5432 root@db.example.com -i ~/.ssh/id_rsa

Here is how each flag functions:

• -N: Instructs SSH not to execute a remote command. This is useful for forwarding ports.
• -L 5433:127.0.0.1:5432: Maps local port 5433 to 127.0.0.1:5432 from the perspective of the remote server.
• root@db.example.com: The remote SSH login credentials and hostname.
• -i ~/.ssh/id_rsa: Path to your private SSH key.

Once connected, open a separate terminal on your local computer and connect to PostgreSQL using local port 5433:

psql -h 127.0.0.1 -p 5433 -U postgres -d production_db

Persistent Background Tunnel via Systemd

For automated client environments or local workstations, relying on manual terminal commands is fragile. You can manage the tunnel as a persistent background service using systemd.

Create a file named /etc/systemd/system/postgres-tunnel.service on your local machine:

[Unit]
Description=SSH Tunnel for Remote PostgreSQL Database
After=network.target

[Service]
User=localuser
ExecStart=/usr/bin/ssh -NT -o ServerAliveInterval=60 -o ServerAliveCountMax=3 -o ExitOnForwardFailure=yes -L 5433:127.0.0.1:5432 remoteuser@db.example.com -i /home/localuser/.ssh/id_rsa
Restart=always
RestartSec=10

[Install]
WantedBy=multi-user.target

Reload systemd daemon files, enable the unit to run at boot, and start the service immediately:

sudo systemctl daemon-reload
sudo systemctl enable postgres-tunnel.service
sudo systemctl start postgres-tunnel.service
sudo systemctl status postgres-tunnel.service

If the remote host reboots or network connectivity drops, systemd automatically restarts the tunnel process after 10 seconds.

Automating Tunnels in Python with sshtunnel and psycopg2

In programmatic workflows, application scripts need to establish SSH tunnels on demand without depending on external system daemons. Python provides the sshtunnel package to manage SSH forwarding programmatically alongside database drivers like psycopg2.

First, install the necessary dependencies into your virtual environment:

pip install sshtunnel psycopg2-binary

Below is a complete script that establishes an SSH tunnel, opens a connection to PostgreSQL, creates a table, inserts a sample record, queries the data, prints the output, and gracefully tears down both connections.

import psycopg2
from sshtunnel import SSHTunnelForwarder

SSH_HOST = "db.example.com"
SSH_PORT = 22
SSH_USER = "deploy"
SSH_PRIVATE_KEY_PATH = "/home/appuser/.ssh/id_rsa"

DB_HOST = "127.0.0.1"
DB_PORT = 5432
DB_NAME = "analytics"
DB_USER = "analytics_user"
DB_PASSWORD = "SecurePassword123!"

def run_database_operations():
    with SSHTunnelForwarder(
        (SSH_HOST, SSH_PORT),
        ssh_username=SSH_USER,
        ssh_pkey=SSH_PRIVATE_KEY_PATH,
        remote_bind_address=(DB_HOST, DB_PORT),
        local_bind_address=("127.0.0.1", 6543)
    ) as tunnel:
        print(f"SSH Tunnel established on local port: {tunnel.local_bind_port}")
        
        connection = psycopg2.connect(
            host="127.0.0.1",
            port=tunnel.local_bind_port,
            dbname=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD
        )
        
        cursor = connection.cursor()
        
        create_table_query = """
        CREATE TABLE IF NOT EXISTS event_logs (
            id SERIAL PRIMARY KEY,
            event_name VARCHAR(100) NOT NULL,
            payload TEXT NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        );
        """
        cursor.execute(create_table_query)
        connection.commit()
        
        insert_query = """
        INSERT INTO event_logs (event_name, payload)
        VALUES (%s, %s) RETURNING id;
        """
        cursor.execute(insert_query, ("user_signup", '{"user_id": 4092, "status": "active"}'))
        inserted_id = cursor.fetchone()[0]
        connection.commit()
        print(f"Inserted row ID: {inserted_id}")
        
        select_query = "SELECT id, event_name, payload, created_at FROM event_logs WHERE id = %s;"
        cursor.execute(select_query, (inserted_id,))
        record = cursor.fetchone()
        
        print("Retrieved Record:")
        print(f"ID: {record[0]}")
        print(f"Event: {record[1]}")
        print(f"Payload: {record[2]}")
        print(f"Timestamp: {record[3]}")
        
        cursor.close()
        connection.close()
        print("Database connection closed cleanly.")

if __name__ == "__main__":
    run_database_operations()

This methodology is essential when building complex data transformation pipelines. For instance, if you are Handling Structured Output with OpenAI Function Calling, you can parse structured JSON from language model APIs locally, open an SSH tunnel on demand, safely stream records into PostgreSQL, and close the forwarder immediately upon task completion.

Hardening SSH Configurations for Port Forwarding Only

By default, an SSH account allows interactive shell execution, command running, and broad network navigation. If an attacker steals a private key used exclusively for database tunneling, they could gain full interactive access to your server.

To prevent this, you should restrict SSH keys specifically to port forwarding tasks.

Dedicated Tunnel User Setup

Create a dedicated system user on your remote server that has no shell privileges and no interactive login rights:

sudo useradd -m -s /bin/false tunneluser
sudo mkdir -p /home/tunneluser/.ssh
sudo chmod 700 /home/tunneluser/.ssh

Generate a designated key pair on your local client machine:

ssh-keygen -t ed25519 -f ~/.ssh/id_ed25519_postgres_tunnel -C "postgres-tunnel-key"

Copy the public key contents from id_ed25519_postgres_tunnel.pub. On the remote server, create and open /home/tunneluser/.ssh/authorized_keys:

no-pty,no-agent-forwarding,no-X11-forwarding,permitopen="127.0.0.1:5432" ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIExampleKeyDataHere postgres-tunnel-key

Explaining Authorized Keys Restrictions

• no-pty: Prevents the allocation of a pseudo-terminal, blocking interactive bash access.
• no-agent-forwarding: Disables forwarding of the SSH authentication agent.
• no-X11-forwarding: Blocks graphical window forwarding.
• permitopen="127.0.0.1:5432": Restricts port forwarding privileges strictly to local target 127.0.0.1 on port 5432. The SSH daemon will drop requests attempting to forward to any other port or external internal network host.

Set ownership and file permissions properly on the server:

sudo chown -R tunneluser:tunneluser /home/tunneluser/.ssh
sudo chmod 600 /home/tunneluser/.ssh/authorized_keys

Server-Wide SSH Host Hardening

Modify /etc/ssh/sshd_config on the remote server to enforce strict key authentication rules globally:

PermitRootLogin no
PasswordAuthentication no
PubkeyAuthentication yes
AllowTcpForwarding yes
X11Forwarding no
MaxAuthTries 3
ClientAliveInterval 300
ClientAliveCountMax 2

Test the configuration for syntax errors and restart SSH:

sudo sshd -t
sudo systemctl restart ssh

Test the hardened tunnel connection from your client machine:

ssh -N -L 5433:127.0.0.1:5432 tunneluser@db.example.com -i ~/.ssh/id_ed25519_postgres_tunnel

If an unauthorized user attempts to open a shell session using this key, SSH instantly rejects the attempt while preserving the underlying encrypted TCP forwarding path.

Getting Started

Securing database infrastructure requires locking down open network sockets, enforcing encrypted transit layers, and enforcing minimum privileges. By combining PostgreSQL host restrictions with SSH tunnels, you achieve bank-grade database isolation without exposing public connection endpoints.

To begin securing your database setup today:

1. Provision a high-performance database instance on Hetzner VPS, Contabo VPS, or DigitalOcean.
2. Attach a domain or subdomain managed through Namecheap for clean certificate management and host resolution.
3. Configure PostgreSQL to bind strictly to localhost and apply host UFW firewall rules.
4. Set up non-privileged SSH tunneling accounts using restricted authorized_keys parameters.
5. Integrate background systemd processes or Python sshtunnel scripts into your workflow runtimes or n8n Cloud instances.

Outsource Your Automation

Don’t have time? I build production n8n workflows, WhatsApp bots, and fully automated YouTube Shorts pipelines. Hire me on Fiverr, mention SYS3-HUGO for priority. Or DM at chasebot.online.