Postgresql trigger on user logon

Question

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?

Answer 1

login_hook

Postgres database extension to execute som code on user login, comparable to Oracle's after logon trigger.

Answer 2

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.