Re: Trigger to run @ connection time?

On Mar 13, 2008, at 2:00 AM, Berend Tober wrote:

> 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.

It doesn't matter whether you're talking about a BEFORE or an AFTER
trigger, that was just an example to illustrate the problem - which
is the same for both cases.

What I was saying is that if the ON CONNECT trigger is defined in the
database you're connecting to and it contains an error, there's
nothing you can do to fix the error (starting with logging into that
database). Which was what Tom pointed out already.

My idea around this was to define the ON CONNECT trigger outside the
database you're connecting to, so that you'll at least be able to fix
or disable the problematic code. It's a bit like the situation with
PAM authentication to your (UNIX-based) OS - you can define your own
authorisation methods, but if they are broken your only option is to
boot into single-user mode and disable that part of authorisation. In
Postgres template1 is a bit like single-user mode in UNIX.

Another option would be to not fire the trigger if a super user
connects to the database, but that pretty much depends on what the
trigger does, which is unknown by definition.

Thinking this over a bit more, it seems you're not so much looking
for an ON CONNECT trigger, but for an ON AUTHORISE trigger. The
problems remain pretty much the same though.

Regards,
Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,47d8d2ed233091559156500!