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.  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?

Examine this file: /var/log/postgresql/postgresql-x.y-main.log

(Substitute x.y with the major and minor versions of PostgreSQL you are running e.g., 9.5.)

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 (if you are using PostgreSQL 9.5).  Please note that this log does not show successful database login attempts.  It is not a complete history -- it only records failed attempts.

To check operating-system-level failed logins, check these files (the top two pertain to SSH both successful and unsuccessful while btmp pertains to other failed remote login attempts):


Leave a comment

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