aports/community/prometheus-postgres-exporter

This document is adapted from README.Debian contained in Debian package.

To use the PostgreSQL exporter, you need to connect to the database with
superuser (postgres) privileges, or with an user that has been granted enough
permissions.

The recommended way to do this, is to create a `prometheus` user with no
password, and then connect using UNIX domain sockets.

To do that, set this connection string in
/etc/conf.d/prometheus-postgres-exporter:

  DATA_SOURCE_NAME='user=prometheus host=/run/postgresql dbname=postgres'

And use psql (doas -u postgres psql) to execute these SQL commands to create
the user:

  CREATE USER prometheus;
  ALTER USER prometheus SET SEARCH_PATH TO prometheus,pg_catalog;
  
  CREATE SCHEMA prometheus AUTHORIZATION prometheus;
  
  CREATE FUNCTION prometheus.f_select_pg_stat_activity()
  RETURNS setof pg_catalog.pg_stat_activity
  LANGUAGE sql
  SECURITY DEFINER
  AS $$
    SELECT * from pg_catalog.pg_stat_activity;
  $$;
  
  CREATE FUNCTION prometheus.f_select_pg_stat_replication()
  RETURNS setof pg_catalog.pg_stat_replication
  LANGUAGE sql
  SECURITY DEFINER
  AS $$
    SELECT * from pg_catalog.pg_stat_replication;
  $$;
  
  CREATE VIEW prometheus.pg_stat_replication
  AS
    SELECT * FROM prometheus.f_select_pg_stat_replication();
  
  CREATE VIEW prometheus.pg_stat_activity
  AS
    SELECT * FROM prometheus.f_select_pg_stat_activity();
  
  GRANT SELECT ON prometheus.pg_stat_replication TO prometheus;
  GRANT SELECT ON prometheus.pg_stat_activity TO prometheus;