Table of Contents

A database through a jump host

A reporting job needs a PostgreSQL database that sits in a private network, reachable only from a bastion host. Instead of a VPN, the job opens an SSH connection to the bastion and forwards a local port to the database, as ssh -L does. Any ADO.NET provider, ORM or tool then connects to 127.0.0.1 on that port.

flowchart LR
    subgraph job["Reporting job (this machine)"]
        code["Npgsql / EF Core / Dapper"] -- "127.0.0.1:{random port}" --> fwd["SshLocalPortForwarder"]
    end
    fwd == "SSH connection, encrypted<br/>(direct-tcpip channel per DB connection)" ==> bastion["bastion.example.com"]
    subgraph private["Private network"]
        bastion -- "TCP" --> db[("db.internal:5432")]
    end

The approach

  • One SSH connection, many database connections. Each connection the provider opens (a connection pool has several) becomes its own channel inside the SSH connection.
  • Loopback and port 0. The forwarder listens on 127.0.0.1 only, so no other machine can use it, on a free port the system picks, so concurrent jobs do not collide.
  • The database still authenticates. The tunnel replaces network reachability, not the database's own login and TLS.

The code

/// <summary>An SSH connection to the bastion and a local port forwarded to the database, for as long as it is not disposed.</summary>
public sealed class DatabaseTunnelHandle : IAsyncDisposable
{
    private readonly SshClient _bastion;
    private readonly SshLocalPortForwarder _forwarder;

    private DatabaseTunnelHandle(SshClient bastion, SshLocalPortForwarder forwarder) => (_bastion, _forwarder) = (bastion, forwarder);

    /// <summary>The local port the database is reachable on.</summary>
    public int Port => _forwarder.LocalEndPoint.Port;

    /// <summary>Cancelled when the SSH connection drops: the tunnel is gone, and with it the database connections.</summary>
    public CancellationToken Closed => _bastion.ConnectionClosed;

    public static async Task<DatabaseTunnelHandle> OpenAsync(string bastionAlias, string databaseHost, int databasePort,
        CancellationToken cancellationToken)
    {
        var bastion = await SshClient.ConnectAsync(bastionAlias, cancellationToken: cancellationToken);
        try
        {
            // Loopback only, port 0: a free port, reachable from this machine and nowhere else.
            var forwarder = await bastion.ForwardLocalPortAsync(new IPEndPoint(IPAddress.Loopback, 0), databaseHost, databasePort,
                cancellationToken);
            return new DatabaseTunnelHandle(bastion, forwarder);
        }
        catch
        {
            await bastion.DisposeAsync();
            throw;
        }
    }

    public async ValueTask DisposeAsync()
    {
        await _forwarder.DisposeAsync();
        await _bastion.DisposeAsync();
    }
}

await using var tunnel = await DatabaseTunnelHandle.OpenAsync("bastion", "db.internal", 5432, cancellationToken);

// The database password from the environment or a secret store; the tunnel does not change how the database authenticates.
var connectionString =
    $"Host=127.0.0.1;Port={tunnel.Port};Database=reports;Username=report_reader;" +
    $"Password={Environment.GetEnvironmentVariable("REPORTS_DB_PASSWORD")};SSL Mode=Require";

// Hand the connection string to your ADO.NET provider (Npgsql here), Dapper or EF Core as usual:
// await using var db = new NpgsqlConnection(connectionString);
// await db.OpenAsync(cancellationToken);

The bastion's entry in ~/.ssh/config (or an application's own configuration, see Use ssh_config and known_hosts):

Host bastion
  HostName bastion.example.com
  User report
  IdentityFile ~/.ssh/report_ed25519
  ServerAliveInterval 30

For production

  • Keep-alive. ServerAliveInterval (or KeepAliveInterval) detects a dead tunnel in bounded time instead of leaving database calls hanging.
  • A dropped tunnel ends every database connection through it. Watch tunnel.Closed, and open a new tunnel (and clear the provider's connection pool) when it fires; see Keep-alive and reconnecting.
  • Bastion policy. The bastion must allow forwarding to the database (sshd's AllowTcpForwarding, perhaps PermitOpen db.internal:5432 to allow only that).
  • Several databases can share one bastion connection: call ForwardLocalPortAsync once per database.
  • No local port at all. A client library that can take a Stream can use OpenTcpTunnelAsync instead; most database providers need a host and port, which is what the forwarder gives them.

See also

Edit this page