Preparing PostgreSQL: monitoring role, query statistics and SSH tunnel

This page covers what to set up on the database and network side so Spitfire can read your PostgreSQL: a monitoring role that cannot touch data, query statistics and, if needed, an SSH tunnel locked to a single target, with no port opened. Spitfire changes nothing in your database and you can cut its access at any time. Connection types and the setup script: Databases. The Spitfire side of the tunnel: SSH tunnel.

#What you need

Your security team usually asks these first:

• What it can read: only the statistics views (pg_stat_*, pg_settings). pg_monitor shows no table rows; constants in query texts arrive as $1, $2.
• What it can write: nothing. The role cannot write to any table or change the schema.
• What opens on the network: in a tunnel, a user without a shell may forward only to <db-address>:5432. The server's other users and the global sshd settings do not change.

#Find your version

psql -h <db-address> -U postgres -c "select version();"

#Steps by version

#PostgreSQL 10 and later

Create the role as a superuser:

CREATE ROLE spitfire_monitor LOGIN CONNECTION LIMIT 3 PASSWORD '<password>' IN ROLE pg_monitor;

• IN ROLE pg_monitor opens the statistics views, not the data.
• CONNECTION LIMIT 3 keeps the role at three connections at most even if Spitfire's pool is set wrong, so your application never runs out of connections. In Spitfire Max open connections must be below this limit (In Spitfire).
• If Spitfire connects directly, add a line for Spitfire's egress address to pg_hba.conf (hostssl <database> spitfire_monitor <spitfire-ip>/32 scram-sha-256) and run select pg_reload_conf();.

#With a Kubernetes operator

Do not run CREATE ROLE by hand in a cluster an operator manages: the role stays outside the operator's knowledge and can be lost on a restore or when the cluster is recreated. Add the role to the cluster definition.

CloudNativePG: first put the password in a Secret (kubernetes.io/basic-auth, username spitfire_monitor), then:

apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
  name: <cluster>
spec:
  managed:
    roles:
      - name: spitfire_monitor
        ensure: present
        login: true
        connectionLimit: 3
        inRoles: [pg_monitor]
        passwordSecret:
          name: spitfire-monitor

Status: kubectl get cluster <cluster> -o jsonpath='{.status.managedRolesStatus}'.

Zalando postgres-operator: create the role under users; the operator writes its password to the spitfire-monitor.<cluster>.credentials.postgresql.acid.zalan.do Secret. The operator does not grant pg_monitor; grant it once by hand (it lasts, since the role is the operator's):

apiVersion: acid.zalan.do/v1
kind: postgresql
metadata:
  name: <cluster>
spec:
  users:
    spitfire_monitor: [login]

GRANT pg_monitor TO spitfire_monitor;
ALTER ROLE spitfire_monitor CONNECTION LIMIT 3;

Crunchy PGO: under spec.users; the password is in the <cluster>-pguser-spitfire-monitor Secret. The options field takes CREATE ROLE options; membership is again granted once:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: <cluster>
spec:
  users:
    - name: spitfire-monitor
      options: "LOGIN CONNECTION LIMIT 3"

GRANT pg_monitor TO "spitfire-monitor";

On Kubernetes the Host in Spitfire is the address of the write service <cluster>-rw (its ClusterIP in a tunnel, SSH tunnel). Monitoring queries can go to a replica through the -ro service, but replication lag and write statistics are visible only on the primary; point the main connection at the primary (-rw).

#Cloud databases

RDS / Aurora do not need rds_superuser; the CREATE ROLE above, run as the master user, is enough. Cloud SQL and Azure Database for PostgreSQL also ship pg_monitor and the command is the same. The pg_stat_statements library is loaded by default on RDS / Aurora and Cloud SQL; on Azure turn it on under Server parameters → shared_preload_libraries. On all three, CREATE EXTENSION in the monitored database is then enough.

#PostgreSQL 9.6 and earlier

There is no pg_monitor. Create the role and grant the statistics views one by one; other sessions' query texts are hidden from anyone but a superuser, so expose them through a SECURITY DEFINER wrapper:

CREATE ROLE spitfire_monitor LOGIN CONNECTION LIMIT 3 PASSWORD '<password>';
GRANT SELECT ON pg_stat_database, pg_stat_user_tables, pg_stat_replication TO spitfire_monitor;

CREATE SCHEMA IF NOT EXISTS spitfire AUTHORIZATION postgres;
CREATE FUNCTION spitfire.pg_stat_activity() RETURNS SETOF pg_stat_activity
  LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog
  AS 'SELECT * FROM pg_catalog.pg_stat_activity';
REVOKE ALL ON FUNCTION spitfire.pg_stat_activity() FROM PUBLIC;
GRANT USAGE ON SCHEMA spitfire TO spitfire_monitor;
GRANT EXECUTE ON FUNCTION spitfire.pg_stat_activity() TO spitfire_monitor;

On these versions Spitfire's Test reports the pg_monitor step as information; we recommend upgrading (9.6 went out of support in 2021).

#Query statistics (pg_stat_statements)

The slow and frequent queries screen reads this extension. Is it off:

psql -h <db-address> -U postgres -c "show shared_preload_libraries;"

If pg_stat_statements is not in the list, turning it on needs a restart:

1. ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements'; (if the list is not empty, append ,pg_stat_statements to the current value).
2. Restart PostgreSQL (sudo systemctl restart postgresql).
3. In the monitored database: CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Do not do this by hand on CloudNativePG: writing one pg_stat_statements.* parameter (e.g. pg_stat_statements.max: "10000") under the cluster's spec.postgresql.parameters is enough. The operator loads the library, creates the extension in every database and restarts the replica first, then the primary through a switchover.

The extension replaces constants in query texts with $1, $2; no personal data stays in them.

#Front door: direct or SSH tunnel

#Direct

If you can allow Spitfire's egress address to reach 5432 (firewall + pg_hba.conf), no tunnel is needed. SSH tunnel stays off in Spitfire and Host is the database's address. Connect with sslmode=require.

#SSH tunnel

If the database is on a private network behind a jump host, a tunnel needs no open port. Only an installation admin adds the tunnel in Spitfire (SSH tunnel). Run the commands on the jump host, as root (the prompt reads root@<server>:~#).

1. Is the target reachable: the database port must be open from the jump host.

   bashCopy

   timeout 3 bash -c '</dev/tcp/<db-address>/5432' && echo OPEN

   On Kubernetes the target is the ClusterIP of the <cluster>-rw service (kubectl get svc <cluster>-rw -o jsonpath='{.spec.clusterIP}'). This check also shows that the NetworkPolicy controller lets node-to-pod traffic through.

2. A user without a shell:

   bashCopy

   useradd -r -m -s /usr/sbin/nologin spitfire-tunnel
   install -d -m 700 -o spitfire-tunnel -g spitfire-tunnel ~spitfire-tunnel/.ssh

3. Key: in Spitfire's connection dialog, in the SSH tunnel section, press Generate key pair. The private key stays encrypted in Spitfire and is never shown; the screen shows the public key and a ready line filled from the form's Host / Port. To use your own key, choose My own key and paste the private key; the line has the same form.

4. A line locked to one target: in ~spitfire-tunnel/.ssh/authorized_keys, as one line:

   textCopy

   restrict,port-forwarding,permitopen="<db-address>:5432" ssh-ed25519 AAAA… spitfire-tunnel

   restrict turns everything off (shell, PTY, agent, X11), port-forwarding turns only forwarding back on and permitopen limits the target to that address and port. Then:

   bashCopy

   chown spitfire-tunnel: ~spitfire-tunnel/.ssh/authorized_keys
   chmod 600 ~spitfire-tunnel/.ssh/authorized_keys

5. Host key fingerprints, all of them:

   bashCopy

   for f in /etc/ssh/ssh_host_*_key.pub; do ssh-keygen -lf "$f"; done

   A server usually has several keys (ED25519, ECDSA, RSA). Spitfire's Fetch host key shows every type the server offers, with its type; compare with the line of the same type.

#Role scan

Never monitor with a role that can read data. Is there another monitoring or read role, and is spitfire_monitor broader than it needs to be:

SELECT r.rolname, r.rolsuper, r.rolbypassrls, r.rolconnlimit,
       array(SELECT g.rolname FROM pg_auth_members m JOIN pg_roles g ON g.oid = m.roleid
             WHERE m.member = r.oid) AS memberships
FROM pg_roles r
WHERE r.rolcanlogin AND r.rolname NOT LIKE 'pg\_%'
ORDER BY 1;

Roles defined in CloudNativePG: kubectl get cluster <cluster> -o jsonpath='{.status.managedRolesStatus}'. Do not give Spitfire a role that is a superuser or has pg_read_all_data, pg_write_all_data or BYPASSRLS. Spitfire's Test also shows these as warnings.

#Try it before Spitfire

First the role (from a machine that can reach the database):

psql "host=<db-address> dbname=<database> user=spitfire_monitor sslmode=require" \
  -c "select pg_has_role('pg_monitor','member') as monitor, rolconnlimit from pg_roles where rolname = current_user;"

With a tunnel, try the three restrictions from your own machine (~/.ssh/spitfire is a key you added for the test; with the key Spitfire generated, Spitfire's Test runs this check):

1. No shell:

   bashCopy

   ssh -i ~/.ssh/spitfire spitfire-tunnel@<jump-host>

   Expected: PTY allocation request failed and This account is currently not available.

2. Another target is refused:

   bashCopy

   ssh -i ~/.ssh/spitfire -N -L 15433:127.0.0.1:22 spitfire-tunnel@<jump-host> &
   nc -z localhost 15433; kill %1

   Expected: channel 2: open failed: administratively prohibited: open failed

3. The database is open:

   bashCopy

   ssh -i ~/.ssh/spitfire -N -L 15432:<db-address>:5432 spitfire-tunnel@<jump-host> &
   nc -z localhost 15432 && echo OPEN; kill %1

   Expected: OPEN

#In Spitfire

In Connections → Add connection → PostgreSQL:

In the tunnel section:

• Fetch host key shows the keys the server offers with their type (ED25519, ECDSA, RSA) and the command that lists every type. Compare with the server's output and press The fingerprints match: trust this key; Spitfire asks once more.
• The Preparation commands card fills the commands above with the form's addresses, ready to copy.

Then press Test in the dialog. The result comes layer by layer: SSH connection, host key, authentication, forwarding permission, PostgreSQL login, pg_monitor, pg_stat_statements, the role's scope, connection limit and primary / replica. A failed step links to the matching heading on this page. If you press Save without a test, Spitfire asks once.

#Common errors

#Undoing it

1. Delete the connection in Spitfire (Connections → trash can). The key and passwords go with the tunnel.
2. On the jump host remove the line and the user: delete the line from authorized_keys, then userdel -r spitfire-tunnel.
3. Remove the role: DROP ROLE spitfire_monitor; With an operator set the role to ensure: absent (CloudNativePG) or remove it from users (Zalando, Crunchy).

pg_stat_statements may stay on, it is harmless; turning it off again needs a restart.

#Related pages

• SSH tunnel
• Databases: PostgreSQL
• Preparing Zabbix