Re: Trigger to run @ connection time?

On Friday 14 March 2008 11:36, Marko Kreen wrote:
> On 3/14/08, Erik Jones <erik(at)myemma(dot)com> wrote:
> > On Mar 14, 2008, at 7:17 AM, Marko Kreen wrote:
> > > To put it to core Postgres, it needs to be conceptually sane
> > > first, without needing ugly workarounds to avoid it bringing
> > > whole db down.
> > >
> > > I can see ATM only few ways:
> > >
> > > - Applies only to non-superusers.
> > >
> > > - Error from CONNECT trigger does not affect superuser.
> > >
> > > - Applies to database + role. Role could be also group of users.
> > >
> > > So you always have way do fix things, without hexediting in data
> > > dir...
> >
> > Another option:
> >
> > Does not fire at all in single-user mode. This would be covered by
> > "Applies to non-superusers" if that were there but, by itself, the
> > triggers would still fire for normal superuser connections.
>
> Seems bit too hard - you may other db-s that work fine,
> why should those suffer?
>

there are other failure scenario's for a single db that require single user
mode (think corrupted indexes), so I'm not sure that is too high a price to
be paid, though a less barriar would be better.

If we decide that an on connect trigger involves the combination of a database
and a role, you generally can escape from the failure scenario by having
either a different role, or a different database with the ability to
do "alter database disable on connect triggers". whether this is a direct
alter database, or set at the GUC level, either makes it pretty hard to lock
yourself out completly, and single user mode can be the fall back for that if
needed.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL