Search Postgresql Archives

Re: Trigger to run @ connection time?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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!



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux