Comment suivre les échecs de tentatives de connexion à l'instance de bases de données Amazon RDS exécutant PostgreSQL ?

Date de la dernière mise à jour : 11/12/2019

Je dois effectuer le suivi du nombre d'échecs de connexions à l'instance Amazon Relational Database Service (Amazon RDS) qui exécute PostgreSQL. Comment faire ?

Résolution

PostgreSQL génère de nouveaux fichiers journaux toutes les heures. Pour suivre les échecs de tentatives de connexion à une instance de base de données exécutant PostgreSQL, activez le paramètre log_connections dans le groupe de paramètres personnalisés qui est associé à l'instance de base de données. Pour plus d'informations, consultez la section Modification de paramètres dans un groupe de paramètres de base de données. Une fois que vous avez activé le paramètre log_connections, si un utilisateur tente de se connecter à l'instance de base de données avec des identifiants incorrects, les tentatives qui échouent sont enregistrées dans le journal, par exemple, error/postgresql.log.2018-04-19-10.

Voici un exemple d'échec de tentative de connexion :

2018-04-19 10:12:31 UTC:123.45.67.8(6789):[unknown]@[unknown]:[12507]:LOG: connection received: host=123.45.67.8port=12345
2018-04-19 10:12:31 UTC:123.45.67.8(6789):test_user@Test_DB:[12507]:FATAL: password authentication failed for user "test_user"

Vous pouvez également utiliser le paramètre log_connections PostgreSQL pour enregistrer les tentatives de connexion réussies à une instance de base de données exécutant PostgreSQL.

Voici un exemple de tentative de connexion réussie :

2018-04-19 10:19:20 UTC:123.45.67.8(6789):[unknown]@[unknown]:[17196]:LOG: connection received: host=123.45.67.8 port=12345
2018-04-19 10:19:20 UTC:123.45.67.8(6789):test_user@Test_DB:[17196]:LOG: connection authorized: user=test_user database=Test_DB SSL enabled (protocol=xxxx, cipher=xxxx, compression=off)

Vous pouvez utiliser les paramètres log_connections et log_disconnections pour enregistrer la durée de la session d'un utilisateur.

Voici un exemple de durée de session :

2018-04-19 10:19:43 UTC:123.45.67.8(6789):test_user@Test_DB:[17196]:LOG: disconnection: session time: 0:00:23.753 user=test_user database=Test_DB host=123.45.67.8 port=12345

Remarque : 123.45.67.8 est l'adresse IP de l'ordinateur client et 6789 est l'ID de processus (PID) qui est donné au processus de la base de données PostgreSQL.

Analyse du fichier journal

Téléchargez le journal à l'aide de l'interface de ligne de commande AWS (CLI AWS) ou à l'aide de la console Amazon RDS. Ensuite, utilisez l'utilitaire grep pour rechercher les connexions qui ont échoué :

$ grep -B 1 "authentication failed" postgresql.log.2018-04-19-10
2018-04-19 10:12:31 UTC:123.45.67.8(6789):[unknown]@[unknown]:[12507]:LOG: connection received: host=123.45.67.8port=12345
2018-04-19 10:12:31 UTC:123.45.67.8(6789):test_user@Test_DB:[12507]:FATAL: password authentication failed for user "test_user"

Remarque : le paramètre - B 1 indique à grep d'afficher également la ligne qui précède les lignes correspondant au modèle que vous recherchez. Dans cet exemple, le modèle est authentication failed (échec de l'authentification).

Vous pouvez également utiliser l'extension PostgreSQL log_fdw pour accéder à ces journaux en tant que tables étrangères. Si vous souhaitez conserver les fichiers journaux PostgreSQL au-delà de la période de conservation, vous pouvez exporter les journaux Amazon RDS PostgreSQL vers CloudWatch Logs si vous utilisez PostgreSQL versions 9.6.6 et versions ultérieures, et 10.4 et versions ultérieures.