Re: Trigger to run @ connection time?

Alban Hertroys wrote:
> On Mar 11, 2008, at 10:28 PM, Tom Lane wrote:
>
>> An ON CONNECT trigger enforced by the database seems a bit scary to me.
>> If it's broken, how you gonna get into the DB to fix it?
>>
>> regards, tom lane
>
> If creating the trigger wouldn't be possible from within the database
> that it's defined for (which would be strange anyway as far as I'm
> concerned, since you are already connected at that point and thus missed
> an opportunity to fire that trigger) this shouldn't be a problem.
>
> To put that into an SQL statement, something like:
> #template1 $ CREATE TRIGGER my_database_connect_trg BEFORE CONNECT ON
> my_database EXECUTE PROCEDURE my_database_setup()
>
> Although of course that begs the question where that procedure would be
> stored; Rather not in template1, I suppose! This points to another
> problem with ON CONNECT triggers, you'll likely need to be connected to
> reach the stored procedure that the trigger calls! A nice chicken and
> egg problem, with some scope issues on the horizon...

I envision this not so much as a BEFORE connect trigger, but
rather as an event that happens after the point of the user being
successfully authenticated, but before executing any user
application commands -- in fact before even starting to listen
for any incoming application commands.

A particular implementation I see this useful for, to give some
context to thinking about this, is to update a user password
expiration date (to, say, CURRENT_DATE + 30) at each login. This
would then allow the creation of a system that lets unused
accounts expire but automatically maintains the validity of
actively used accounts, for example. I can think of other uses, too.

I currently achieve this functionality with an event triggered in
an end-user application, but I'd really like it to happen in the
data base so that every application that access this data base
doesn't have to recreate that particular functionality -- and
also so as to eliminate to problem of the functionality not being
implemented by other applications outside our control that access
the data base.