Monitoring base de donnée PostgreSQL

Par défaut (pour les versions antérieures a la version 10.X, donc 9.X et avant), il n'existe pas encore de role pg_monitor, ce qui “oblige” votre monitoring à être effectué avec les droits d'admin.

Comme on est des gens sérieux et qu'on ne veut pas que le monitoring puisse dropper des bases de prod on va faire ca mieux ;)

Merci a https://github.com/wrouesnel/postgres_exporter#running-as-non-superuser

L'idée est la suivante :

• avec PostgreSQL, les vues se lancent avec les permissions de l'utilisateur qui les a créées, elles peuvent donc servir de barrières de sécurité.
• les fonctions correspondantes sont utilisées pour partager ces données avec un utilisateur non superadmin

Pour collecter des metriques de pg_stat_activity et pg_stat_replication en tant qu'ilisateur non superadmin, on va créer des fonctions et vues associées (en tant que superadmin), et on va pouvoir ajouter les permissions séparément pour un utilisateur non privilégié.

En tant que superadmin on va donc créer les fonctions, vues, utilisateur non privilégié et droits associés :

1. Creation de l'utilisateur spécifique au monitoring (le lecteur averti voudra surement changer le mot de passe :p) :

   CREATE OR REPLACE FUNCTION __tmp_create_user() returns void as $$
   BEGIN
     IF NOT EXISTS (
             SELECT                       -- SELECT list can stay empty for this
             FROM   pg_catalog.pg_user
             WHERE  usename = 'postgres_monitoring') THEN
       CREATE USER postgres_monitoring;
     END IF;
   END;
   $$ language plpgsql;
   
   SELECT __tmp_create_user();
   DROP FUNCTION __tmp_create_user();
   
   ALTER USER postgres_exporter WITH PASSWORD 'password';
   ALTER USER postgres_exporter SET SEARCH_PATH TO postgres_monitoring,pg_catalog;

2. Créer le schéma spécifique au monitoring :

   CREATE SCHEMA IF NOT EXISTS postgres_monitoring;
   GRANT USAGE ON SCHEMA postgres_monitoring TO postgres_monitoring;
   GRANT CONNECT ON DATABASE postgres TO postgres_monitoring;

3. Créer les fonctions et vues associées du schéma spécifique au monitoring (pour l'instant juste pg_stat_activity et pg_stat_replication : on voudra certainement en ajouter d'autres plus tard sur le même principe) :

   CREATE OR REPLACE FUNCTION get_pg_stat_activity() RETURNS SETOF pg_stat_activity AS
   $$ SELECT * FROM pg_catalog.pg_stat_activity; $$
   LANGUAGE sql
   VOLATILE
   SECURITY DEFINER;
   
   CREATE OR REPLACE VIEW postgres_monitoring.pg_stat_activity
   AS
     SELECT * from get_pg_stat_activity();
   
   GRANT SELECT ON postgres_monitoring.pg_stat_activity TO postgres_monitoring;
   
   CREATE OR REPLACE FUNCTION get_pg_stat_replication() RETURNS SETOF pg_stat_replication AS
   $$ SELECT * FROM pg_catalog.pg_stat_replication; $$
   LANGUAGE sql
   VOLATILE
   SECURITY DEFINER;
   
   CREATE OR REPLACE VIEW postgres_monitoring.pg_stat_replication
   AS
     SELECT * FROM get_pg_stat_replication();
   
   GRANT SELECT ON postgres_monitoring.pg_stat_replication TO postgres_monitoring;

4. Pensez a autoriser cette nouvelle connexion utilisateur dans le fichier pg_hba.conf, par exemple en y ajoutant la ligne suivante, limitée a vos serveurs de monitoring par exemple :

   host    postgres      postgres_monitoring      192.168.16.151/32            md5

5. Il ne reste plus qu'a recharger ce fichier, par exemple avec un service postgresql reload (adapter le nom au besoin) ou encore lui envoyer un signal HUP

Notre nouvel utilisateur postgres_monitoring va donc maintenant etre capable de lancer certaines commandes sur la base postgres, par exemple :

SELECT * FROM pg_stat_database WHERE datname = 'maBase'

ou encore

SELECT * FROM pg_stat_activity ;

Le reste dépend de votre monitoring en place, et de ce que vous souhaitez mettre dedans

Pour obtenir le délai de réplication (en secondes) entre un esclave et son maitre, a lancer sur l'esclave :

SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM now() - pg_last_xact_replay_timestamp()) END AS log_delay;