CODEWITHSUNDEEP
postgresql
alessandro ferrucci
alessandro ferrucci

Reputation: 1291

Postgresql trigger on user logon

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?

Upvotes: 3

Views: 7124

Answers (3)

Robins Tharakan
Robins Tharakan

Reputation: 2473

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)

Upvotes: 0

Jan Sommer
Jan Sommer

Reputation: 3798

login_hook

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

Upvotes: 4

Nick Barnes
Nick Barnes

Reputation: 21346

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.

Upvotes: 3

Related Questions