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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
