How Do You View Failed User Login Attempts to a PostgreSQL Instance?

Problem scenario
You are running a LAPP application on Ubuntu Linux with PostgreSQL version 9.5.  A user has complained that she cannot log into your Postgres database via a LAPP application.  You are not sure if the attempts are even registering of if the user is entering an incorrect username.  How do you look at a log to see a history of unsuccessful attempts at logging into the Postgresql database?

Solution
Examine this file: /var/log/postgresql/postgresql-9.5-main.log

It logs failed Postgres authentication attempts -- even if they come from a PHP web page (a web-based UI).  If a role, like an OS user but for postgres, tries to log in from a web application with the wrong password, it will be logged in to /var/log/postgresql/postgresql-9.5-main.log.  Please note that this log does not show successful database login attempts.  It is not a complete history -- it only records failed attempts.

Leave a comment

Your email address will not be published. Required fields are marked *