1This document is adapted from README.Debian contained in Debian package.
 2
 3To use the PostgreSQL exporter, you need to connect to the database with
 4superuser (postgres) privileges, or with an user that has been granted enough
 5permissions.
 6
 7The recommended way to do this, is to create a `prometheus` user with no
 8password, and then connect using UNIX domain sockets.
 9
10To do that, set this connection string in
11/etc/conf.d/prometheus-postgres-exporter:
12
13  DATA_SOURCE_NAME='user=prometheus host=/run/postgresql dbname=postgres'
14
15And use psql (doas -u postgres psql) to execute these SQL commands to create
16the user:
17
18  CREATE USER prometheus;
19  ALTER USER prometheus SET SEARCH_PATH TO prometheus,pg_catalog;
20  
21  CREATE SCHEMA prometheus AUTHORIZATION prometheus;
22  
23  CREATE FUNCTION prometheus.f_select_pg_stat_activity()
24  RETURNS setof pg_catalog.pg_stat_activity
25  LANGUAGE sql
26  SECURITY DEFINER
27  AS $$
28    SELECT * from pg_catalog.pg_stat_activity;
29  $$;
30  
31  CREATE FUNCTION prometheus.f_select_pg_stat_replication()
32  RETURNS setof pg_catalog.pg_stat_replication
33  LANGUAGE sql
34  SECURITY DEFINER
35  AS $$
36    SELECT * from pg_catalog.pg_stat_replication;
37  $$;
38  
39  CREATE VIEW prometheus.pg_stat_replication
40  AS
41    SELECT * FROM prometheus.f_select_pg_stat_replication();
42  
43  CREATE VIEW prometheus.pg_stat_activity
44  AS
45    SELECT * FROM prometheus.f_select_pg_stat_activity();
46  
47  GRANT SELECT ON prometheus.pg_stat_replication TO prometheus;
48  GRANT SELECT ON prometheus.pg_stat_activity TO prometheus;