Docs / Connections: PostgreSQL

Set up PostgreSQL

Verbiqo needs one login role with pg_monitor and no rights on your tables. It refuses superusers. The minimum takes about ten minutes per cluster and no restart.

At a glance

What you need

The names below are the ones Verbiqo uses when you ask What should I enable?

Not sure what a server has? Attach it and ask What should I enable? Verbiqo checks every item above and gives the statements for that server.

1. Create the monitoring role

As a superuser, on the primary (it replicates to the standbys):

CREATE ROLE verbiqo LOGIN PASSWORD 'choose-a-password' CONNECTION LIMIT 5;
GRANT pg_monitor TO verbiqo;

pg_monitor lets it read every session's activity, the statistics views and settings. It grants nothing on your tables. Verbiqo holds at most two connections, so the limit of 5 leaves room.

2. Allow it to connect

Add a line to pg_hba.conf on every node, then reload:

hostssl  all  verbiqo  10.0.0.0/8  scram-sha-256

SELECT pg_reload_conf();

hostssl accepts encrypted connections only. Use all (or list them) so Verbiqo can switch between databases on the server.

3. Add the connection

1. Open the form

   Extensions… › PostgreSQL.

2. Fill it in

   Name (such as orders), host, port (5432), database, user verbiqo, password.

3. Choose TLS

   Verify full with your CA certificate. Without the CA certificate yet, Require: it encrypts without checking the certificate. Keep Prefer for a lab, because it falls back to no encryption when the server offers none. See TLS.

4. Test, then save

   The test shows the version, primary or standby, sessions and the TLS in use.

Or from a terminal:

verbiqo pg add orders --host db1.example.com --port 5432 --database orders --user verbiqo --password-stdin --ssl-mode verify-full --ssl-ca /etc/ssl/company-ca.pem
verbiqo pg test orders

Every verb takes --json. One statement, under the same read-only rules as a question (a read-only transaction, catalog and statistics views only):

verbiqo pg query orders "SELECT datname, numbackends FROM pg_stat_database"

TLS and certificates

For a certificate login, the certificate’s common name must be the role name:

hostssl  all  verbiqo  10.0.0.0/8  cert

Modes and options are explained in Encrypted connections.

4. Turn on what helps

Each item is independent: add the ones your policy allows, in any order. Run the statements as a superuser.

Query statistics (recommended)

pg_stat_statements must be preloaded, which needs one restart. Keep any libraries already listed in shared_preload_libraries:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
-- restart PostgreSQL, then in the database Verbiqo connects to:
CREATE EXTENSION pg_stat_statements;

ALTER SYSTEM SET track_io_timing = on;
SELECT pg_reload_conf();

If USAGE on the schema holding pg_stat_statements was revoked from PUBLIC, grant it back to verbiqo.

Plans of your statements, and index tests

Needs PostgreSQL 16 or later. PostgreSQL checks read permission even to plan a statement, so the role needs pg_read_all_data. Verbiqo still never reads rows from your tables: see What Verbiqo can and cannot do. Grant it only if your policy allows.

GRANT pg_read_all_data TO verbiqo;
CREATE EXTENSION hypopg;   -- optional: test a suggested index without building it

Measured bloat

CREATE EXTENSION pgstattuple;   -- in the database Verbiqo connects to

IDs running out

In each database and schema. The second line runs as the role that creates the sequences:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO verbiqo;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO verbiqo;

Whole statements in live samples

ALTER SYSTEM SET track_activity_query_size = 4096;   -- needs a restart

Actual plans of slow statements

auto_explain writes the plans of slow statements, with real row counts, to the log. Verbiqo reads them through Splunk. Add it to the preloaded libraries (one restart), then:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements,auto_explain';
-- restart PostgreSQL, then:
ALTER SYSTEM SET auto_explain.log_min_duration = '1s';
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_timing = off;
ALTER SYSTEM SET auto_explain.log_format = 'json';
SELECT pg_reload_conf();

WAL archiving

Point-in-time recovery needs every WAL file archived. Use your backup tool’s command. Its exporter in Prometheus also gives Verbiqo the age of the last backup, which the server itself does not record.

ALTER SYSTEM SET archive_mode = on;   -- needs a restart
ALTER SYSTEM SET archive_command = 'pgbackrest --stanza=main archive-push %p';

Permissions at a glance

Never grant write rights. Verbiqo has no use for them, and it refuses a superuser outright.

What Verbiqo can and cannot do on your server

Several independent layers keep Verbiqo read-only. Any one of them alone stops a write.

What leaves your network, to the AI service you chose: names (databases, tables, columns, indexes), measurements, settings and the shape of statements. Values are withheld first:

• values in other sessions’ statements, and quoted values in query plans, are replaced with placeholders.
• the sample values PostgreSQL keeps for planning (pg_stats most-common values and histograms) are withheld.
• no row of your tables is ever read.

What Verbiqo’s reads do touch: like any query, planning takes the lightest lock on the tables named (for milliseconds), which never blocks reads or writes and gives up after 2 seconds if DDL holds the table. Every change Verbiqo recommends is a statement for you to review and run.

Logging for incident reviews

The statistics views show now. The log shows what happened. Verbiqo reads it through Splunk:

ALTER SYSTEM SET log_min_duration_statement = '1s';
ALTER SYSTEM SET log_lock_waits = on;
ALTER SYSTEM SET log_autovacuum_min_duration = '10s';
ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET log_temp_files = '10MB';
ALTER SYSTEM SET log_line_prefix = '%m [%p] %q%u@%d app=%a client=%h ';
SELECT pg_reload_conf();

• jsonlog (log_destination = 'jsonlog', PostgreSQL 15+) works as well as the prefix above.
• Messages in English (lc_messages): Verbiqo reads PostgreSQL’s own wording.
• Any log name. Verbiqo recognises the log by its lines. What it finds: Log findings.

Managed services and clusters

• Amazon RDS, Aurora, Cloud SQL, Azure: the administrator account can grant pg_monitor and pg_read_all_data. Change settings in the parameter group or flags, with the same names.
• Clusters: each node needs the pg_hba.conf line, on the same port as the saved connection. Verbiqo finds the other nodes itself.
• Machine and history: a connected Prometheus with node_exporter sizes memory settings for the real machine and gives capacity forecasts.

Security and the record of reads

The security review checks this server’s own security (TLS, pg_hba.conf rules, SCRAM, superusers, a supported version) with the same role, and every statement Verbiqo sends is on the access record.

Next: Ask PostgreSQL.

Common errors

The connection test and the answer say what went wrong. Find the message below, then follow the fix.