Pro plan from £5 per endpoint per month · see pricing
Secronyx
AvailableLinuxWindowsmacOS

PostgreSQL

See what is running, waiting and lagging inside PostgreSQL, with a read-only monitoring login.

What it answers

Questions an on-call engineer asks, which an AI client can answer with this plugin.

  • What is running on the database right now, and what is it waiting on?
  • Which session is blocking the others, and how long has it held its locks?
  • How far behind is the replica, and is a replication slot filling the disk?
  • Which transactions have been open for hours and are holding back vacuum?
  • Which tables need vacuuming, and is autovacuum switched off anywhere?
  • How close is each database to transaction ID wraparound?
  • Which database grew, and which settings were changed from the defaults?
  • Which statements cost the server the most time?

What it returns

Each of the plugin's read-only tools, described in words.

  • Sessions on the PostgreSQL server that are doing something, with their state, wait event, ages and normalised statement
  • PostgreSQL sessions waiting on a lock, each with the session blocking it and how long it has waited
  • Locks held and awaited on the PostgreSQL server, grouped by lock type, mode, relation and whether they are granted
  • PostgreSQL replication from both ends - standbys connected to this primary with their lag, inactive slots, and this standby's receiver
  • PostgreSQL transactions open longer than a threshold, oldest first, with how far back their snapshot holds vacuum
  • Tables in the connected PostgreSQL database with the most dead rows, and when vacuum and analyze last ran on them
  • Transaction ID and multixact age of every PostgreSQL database, and how close each is to the wraparound limit
  • Size of every PostgreSQL database on disk, with its sessions, commits, rollbacks, cache hit ratio, temp files and deadlocks
  • PostgreSQL settings that differ from the built-in default, where each was set, and which wait for a restart
  • The statements that cost PostgreSQL the most time, from the pg_stat_statements extension, with calls, timings and rows

At a glance

Plugin
postgres 1.0.0
Publisher
levantar
Platforms
Linux, Windows, macOS

Tested on

  • PostgreSQL 13.22, 15.14, 16.10 and 17.6 on Linux (Ubuntu, Docker), CI runner
  • PostgreSQL 17 on macOS (Homebrew), CI runner
  • PostgreSQL 17 on Windows Server, CI runner

How it logs in

Without an explicit choice, install tries the methods that need no secret first, in this order.

  • The agent's OS account on the same host, with no secret
  • A user name and password
  • A client certificate

The least-privilege login

The grant script in the pack. secronyx plugin setup postgres --apply runs it with a random password for the new login, or you can run it by hand.

-- Least-privilege login for the postgres plugin: pg_monitor and nothing -- else. pg_monitor reads the statistics views and settings; it grants no -- access to table data and no write rights. Run with an administrator login -- (secronyx plugin setup postgres --apply), or by hand. -- -- For os-peer, create the role named after the agent's OS account instead, -- with no password, and map it in pg_hba.conf: -- CREATE ROLE "secronyx-plugin" LOGIN; GRANT pg_monitor TO "secronyx-plugin"; -- local all secronyx-plugin peer -- For client-cert, create a role named after the certificate's CN and use -- the cert method for it in pg_hba.conf: -- hostssl all secronyx_cert 127.0.0.1/32 cert CREATE ROLE {{user}} LOGIN PASSWORD '{{password}}'; GRANT pg_monitor TO {{user}};

See it run

Recording coming. Install, setup and status runs for this plugin are recorded on the shared test lab, and appear here once they have run.

Read-only, in layers

Every query is fixed text inside the signed pack: one SELECT or WITH statement whose functions are all on the agent's allowlist for that database. It runs in a read-only transaction with server-side statement and lock timeouts, and is always rolled back. Callers can only fill in typed, bounded arguments, which are bound as parameters.

These controls narrow what the plugin can do; none of them is a guarantee on its own. An administrator can still accept a more powerful login on purpose (audited and shown in status), root or Administrator on the host can replace the agent, and the plugin can read whatever its login can see.