Authentication monitoring in PostgreSQL

How about a situation when you want to log all the login attempts made to your PostgreSQL server. Yes, one way would be to read your log files and find out all the information related to login attempts and then transfer to them some other file, etc. to further use this information. Now, this is one way of doing it but it might pose some challenges like time and memory consumption in reading from those long files, additionally this is going to incur a lot of I/O which might be a serious issue when you are on cloud. During my time working for Zalando, we came across this problem and decided to write a Postgres extension to handle this efficiently.

And that's the story motivation behind the development of this extension - pg_auth_mon. In order to use this, include it in shared_preload_libraries, and then run CREATE EXTENSION pg_auth_mon on your server. Once this extension is in place, all the log in attempts to your server will be saved in a view called pg_auth_mon in the following way.

Now, a caveat to remember here is that all the information in this view would be lost at the server restart.

If you are interested in understanding what is happening inside the extension, here is a brief description on it. Basically, at the time of login  prev_client_auth_hook redirects the control to this extension. And it is here in function auth_monitor, all the details from the connection like, authentication method, user name, etc. which are available in Port data structure are read and saved in the required view. It also records the the time of the last login attempt. So, it might help in understanding if there's some  security attack attempted or a genuine mistake by the username. 

The underlying data structure used here is hash_table, which makes it easy to search for the usernames if they have been already logged in before. Additionally, it also logs the attempts made using incorrect usernames i.e. usernames that do not exist in the database. Now, in order to be mindful of the space it takes in case of invalid username, all such attempts are counted in a single row and the username column is left blank for this. So, one drawback in this attempt is to not able to know what all incorrect usernames were tried by an attacker (if any) in such cases.

In latest version, the code also takes care of the case when a user is renamed since the last login attempt. It has an additional field login_name_at_last_attempt to provide the last username used.