I'm trying to figure out of a way of knowing as soon as a user logs into a Postgres database. Is there a way to define a trigger to fire when a user logs into the DB? Or is there a table or a system view that gets updated whenever anyone logs into the DB?
1
3 Answers
Postgres database extension to execute som code on user login, comparable to Oracle's after logon trigger.
Comments
I don't think there's anything at the SQL level which can identify a logon event (i.e. no view you can query, and no trigger you can create).
You can, however, write a server-side module (in C) which intercepts logon events via the ClientAuthentication_hook. This hook is invoked after the server has attempted to authenticate a user, but before it has sent its response.
There is a worked example of a ClientAuthentication_hook on the Postgres wiki. You can also find a couple of examples of this hook in action within Postgres' own contrib modules.
Comments
Login triggers are now possible in 2 ways:
Method 1: Postgres v17+ now has the login Event trigger, which helps you with a Login based trigger event. If on a newer version, this is the best solution for Login based triggers.
Method 2: For older postgres versions, you could try to use a recent version of pg_tle which now directly exposes the clientauth hook to be used in multiple languages (such as Pl/PgSQL or Rust or plv8 etc.). I've just written a blog post (with code) that explains how to get this going - https://www.thatguyfromdelhi.com/2024/04/boost-database-security-restrict-users.html .
Notably, pg_tle (comes with RDS) but is essentially open-source, so you could always install it on your own postgres installation (EC2 or on-premise systems).
(Disclaimer - pointing to my own blog post but it directly answers here)
pg_stat_activitywhich reflects all current connections. But you would need to poll that with an extremely high frequency