pg_ssh

Run remote commands over SSH from inside PostgreSQL.

SELECT convert_from(stdout, 'UTF8') AS stdout, exit_code
  FROM ssh.exec('web-1', 'uname -a; uptime');

pg_ssh exposes one-shot execution, key generation, and pooled transports:

• ssh.exec(host_name text, command text) → TABLE(stdout bytea, stderr bytea, exit_code int). stdout and stderr are bytea, so binary output is preserved exactly — wrap with convert_from(col, 'UTF8') when you want text. It connects with libssh2 (via the Rust ssh2 crate) and authenticates with Session::userauth_pubkey_memory, so the PEM private key is handed to libssh2 directly from process memory and is never written to the filesystem. Each call reconnects and re-authenticates.
• ssh.keygen(key_type text DEFAULT 'ed25519', comment text, passphrase text) → TABLE(private_key text, public_key text, fingerprint text). It generates a fresh keypair entirely in memory with the pure-Rust ssh-key crate (no system libraries) — so you never need the ssh-keygen binary, and the private key never touches disk.
• Pooled transports — ssh.session_open(host_name) authenticates once and hands back a uuid handle; ssh.session_exec(handle, command) then runs commands over the same already-open connection (no per-command handshake), and ssh.session_close(handle) tears it down. See Pooled transports below.

Security model

concern	how it's handled
Private keys	Stored only in the ssh.hosts catalog (in the data directory). During auth they are passed to libssh2 from memory — never materialized as a temp file on disk.
Catalog access	ssh.hosts is locked to its owner: REVOKE ALL … FROM PUBLIC. Only the superuser who ran CREATE EXTENSION can read or write it.
Caller privileges	ssh.exec is SECURITY DEFINER owned by that superuser, with a pinned search_path = pg_catalog, ssh. Unprivileged roles can be granted EXECUTE and run approved commands on pre-registered hosts — they see the result, never the keys.
Pooled transports	ssh.session_open is SECURITY DEFINER for the same reason (it reads ssh.hosts); session_exec / session_close / sessions are SECURITY INVOKER — they touch only in-process session state. A handle is scoped to the client connection that opened it: it lives in that backend's memory, is invisible to other connections, and is torn down on disconnect.
Host identity	Each profile may pin a host_key_fingerprint (lowercase hex SHA-256 of the server host key). If set, exec refuses to connect on mismatch.
Key generation	keygen mints ed25519 keypairs in process memory using the OS CSPRNG (pure-Rust ssh-key, no system libs). The private key is handed back to the caller and never written to disk — no ssh-keygen binary, no temp file.

The default bootstrap grants EXECUTE to PUBLIC so the extension is usable out of the box. If you'd rather restrict who can run remote commands, run REVOKE EXECUTE ON FUNCTION ssh.exec(text,text) FROM PUBLIC; then GRANT EXECUTE … TO <role>;.

The catalog itself is not encrypted at rest — it is protected by superuser-only table privileges, exactly like postgres_fdw user-mapping passwords. If you need encryption at rest, store the PEM column pgp_sym_encrypt(...)-ed and decrypt it inside load_host_config (requires pgcrypto).

The catalog

\d ssh.hosts
                       Table "ssh.hosts"
        Column        |  Type   | Notes
----------------------+---------+----------------------------------------------
 host_name            | text    | primary key — the name you pass to exec
 host                 | text    | hostname or IP
 port                 | integer | default 22
 username             | text    | remote login user
 public_key           | text    | optional; derived from private_key if NULL
 private_key          | text    | PEM private key (in-memory only)
 passphrase           | text    | optional, for encrypted keys
 host_key_fingerprint | text    | optional hex SHA-256 of the server host key

Requirements

• PostgreSQL 18 (13–17 also build via the pgXX features)

• Rust toolchain (stable)

• cargo-pgrx 0.19.1 — must match the pgrx crate version exactly

• System packages (Debian/Ubuntu):

  sudo apt-get install -y build-essential pkg-config libclang-dev \
    libssl-dev libssh2-1-dev zlib1g-dev libreadline-dev

Build & install

# 1. One time: build a PostgreSQL 18 from source for pgrx to test against.
cargo pgrx init --pg18 download

# 2. Compile and install the extension into that PG18.
cargo pgrx install --pg18

# 3. In a database:
CREATE EXTENSION pg_ssh;

For a system-installed PostgreSQL, point pgrx at its pg_config instead:

cargo pgrx init --pg18 $(pg_config --bindir)/..
cargo pgrx install --pg-config $(which pg_config)

Usage

Register a host (the PEM key can span multiple lines — use dollar-quoting):

INSERT INTO ssh.hosts
  (host_name, host, port, username, private_key, host_key_fingerprint)
VALUES
  ('web-1', '10.0.0.5', 22, 'deploy',
   $$-----BEGIN OPENSSH PRIVATE KEY-----
   ...
   -----END OPENSSH PRIVATE KEY-----$$,
   'b3f7...e2a0');  -- hex sha256 of the server host key (optional)
ON CONFLICT (host_name) DO UPDATE SET
  host = EXCLUDED.host,
  username = EXCLUDED.username,
  private_key = EXCLUDED.private_key;

Run a command:

-- stdout/stderr are bytea, so binary output is preserved exactly. Wrap with
-- convert_from() when you want text:
SELECT convert_from(stdout, 'UTF8') AS stdout, exit_code
  FROM ssh.exec('web-1', 'systemctl is-active nginx');
--  stdout | exit_code
-- --------+-----------
--  active |         0

Pooled transports

exec reconnects and re-authenticates on every call. When you run many commands against the same host that is wasteful — the SSH handshake + pubkey auth dominate latency. Open a pooled transport instead: authenticate once, then reuse the connection across as many session_exec calls as you like.

-- Authenticate once; the handle is a uuid meaningful only in THIS connection.
SELECT ssh.session_open('web-1') AS sid \gset

-- Each of these runs over the already-open connection (no per-command handshake).
SELECT convert_from(stdout, 'UTF8') AS out, exit_code
  FROM ssh.session_exec(:'sid', 'systemctl is-active nginx');
SELECT convert_from(stdout, 'UTF8') AS out, exit_code
  FROM ssh.session_exec(:'sid', 'hostname; uptime');

-- Inspect the transports currently open in this backend:
SELECT session_id, host_name, username, opened_at, last_used FROM ssh.sessions();

-- Tear it down when done (returns boolean; idempotent).
SELECT ssh.session_close(:'sid');

function	signature	notes
ssh.session_open	(host_name text) → uuid	SECURITY DEFINER; authenticates once and parks the transport in this backend's memory. Errors on an unknown host_name or a failed handshake/auth, just like exec.
ssh.session_exec	(session_id uuid, command text) → TABLE(stdout bytea, stderr bytea, exit_code int)	Runs command over the open transport. If the transport has died (server reboot, idle timeout, network drop) this errors — session_close it and reopen.
ssh.session_close	(session_id uuid) → boolean	Closes the transport. true if one was open, false if the id was unknown. Safe to call twice.
ssh.sessions	() → TABLE(session_id, host_name, host, username, opened_at, last_used)	Snapshot of this backend's open transports. Always live — no cached copy.

Handles are per-connection, not cluster-wide: a uuid from one psql/backend is meaningless in another. The live ssh2::Session lives in the backend's thread-local memory (it cannot live in a heap table — a table holds bytes, a session holds an open socket), and is dropped when the backend disconnects (the OS reclaims the fd on a crash). For the same reason other backends' sessions are not visible in ssh.sessions(). If a remote drops an idle connection, session_exec will error on the next call; there is no auto-reconnect yet.

Generating keypairs with keygen

Instead of creating the keypair with the external ssh-keygen binary (which writes it to disk), generate one directly in the database and hand it straight to ssh.hosts — the private key never touches the filesystem:

-- Generate an ed25519 keypair in memory and register it in one statement.
INSERT INTO ssh.hosts (host_name, host, port, username, private_key, public_key)
SELECT 'web-1', '10.0.0.5', 22, 'deploy', private_key, public_key
  FROM ssh.keygen('ed25519', 'deploy@web-1');

keygen returns three columns:

column	contents
private_key	OpenSSH-format PEM (-----BEGIN OPENSSH PRIVATE KEY-----), the exact form exec passes to libssh2. Encrypted with AES-256-CTR + bcrypt-pbkdf when passphrase is non-empty (like ssh-keygen -N).
public_key	one-line OpenSSH form (ssh-ed25519 AAAA… <comment>). Optional to store — exec derives it from the private key when public_key is NULL.
fingerprint	the keypair's own fingerprint as SHA256:<base64> (what ssh-keygen -lf prints). This is the client key's fingerprint; it is unrelated to host_key_fingerprint, which pins the server host key.

Only ed25519 is supported. The function is callable by PUBLIC (like exec): it reads no catalog and returns a fresh keypair to each caller, so it leaks no existing secrets. Treat the returned private_key as a credential — don't log it.

Getting the host key fingerprint

host_key_fingerprint is the lowercase hex SHA-256 of the server's host key. From any machine that trusts the host:

ssh-keyscan -t ed25519,rsa,ecdsa HOST 2>/dev/null \
  | ssh-keygen -E sha256 -lf -
# strip the leading "SHA256:" and base64, or compute the raw-digest hex:
ssh-keyscan -t ed25519 HOST 2>/dev/null \
  | awk '{print $3}' | base64 -d | sha256sum
# -> the hex string to store in host_key_fingerprint

The fingerprint is of the raw host key, matching what libssh2's host_key_hash(SHA256) returns. The base64 -d | sha256sum recipe above produces exactly that.

OpenSSL / libssh2 linkage (important)

PostgreSQL itself is usually built against OpenSSL. libssh2 also needs a crypto backend. To avoid two copies of OpenSSL colliding inside one backend process, this extension links the system, dynamically-linked libssh2, which in turn links the same system libssl.so that PostgreSQL uses. Do not enable vendored-openssl or statically link a separate OpenSSL into the extension. If you build PostgreSQL with a non-default OpenSSL, build libssh2 against that same one.

keygen's crypto (ssh-key + ed25519-dalek, sha2, aes, bcrypt-pbkdf) is pure Rust and links no system library, so it adds no second crypto backend and does not disturb the arrangement above.

Limitations

• exec performs blocking I/O in the backend. A slow/dead remote will hold the backend for up to SSH_TIMEOUT_MS (60 s) before erroring. Don't call it from performance-sensitive paths. session_exec has the same behaviour.
• Pooled transports have no auto-reconnect and no keepalive. An idle connection may be reaped by the server or a firewall; the next session_exec then errors and you must session_close + session_open again. There is also no graceful SSH-level DISCONNECT on a backend crash — the OS closes the fd (a before_shmem_exit hook could add this if needed).
• Postgres query cancellation (Ctrl-C) is best-effort: a blocked libssh2 call may not return until its socket times out.
• stdout and stderr are returned as bytea, so binary output is preserved exactly (no lossy UTF-8 decode). Wrap with convert_from(col, 'UTF8') for text.
• stdout and stderr are drained concurrently so a process cannot deadlock the channel by filling one stream.
• keygen generates ed25519 keypairs only. RSA/ECDSA are not supported (they would pull in the heavy pure-Rust rsa/elliptic-curve crates and, for RSA, block a backend during prime search). Use the external ssh-keygen binary and paste the PEM into ssh.hosts if you need them.

Project layout

src/lib.rs        exec + keygen + pooled transports, SPI catalog lookup, libssh2 exec, bootstrap SQL
pg_ssh.control    extension control file
Cargo.toml        pgrx 0.19.1 + ssh2 0.9.5 + ssh-key 0.6.7
live_test.sql     manual end-to-end smoke test (needs a reachable sshd)
verify.sql        no-network checks of the catalog, privilege model, keygen, and pooled transports