on connect trigger?

[btober]

Is there any kind of mechanism in pg 7.3 for doing something like what I
would describe as a "login trigger" procedure or maybe "on connect"
trigger, i.e., a way to specify a stored procedure to run when a user
connects to the database?

What I'm thinking is this. Right now, my end-user GUI application calls a
procedure which updates the user account expiration date whenever they
log in. The idea is that accounts that are never used will expire,
eventually, and those that are active will continually have the
expiration date pushed
further ahead each time they log in.

I'd like to not depend on the application making this call, because other
applications which connect to the same database will be written, and the
developers might not build in this same explicit procedure call -- it
really is the kind of thing that is best done on the backend.

~Berend Tober

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Keith C. Perry]

Quoting bt****@seaworthysys.com:

Is there any kind of mechanism in pg 7.3 for doing something like what I
would describe as a "login trigger" procedure or maybe "on connect"
trigger, i.e., a way to specify a stored procedure to run when a user
connects to the database?

What I'm thinking is this. Right now, my end-user GUI application calls a
procedure which updates the user account expiration date whenever they
log in. The idea is that accounts that are never used will expire,
eventually, and those that are active will continually have the
expiration date pushed
further ahead each time they log in.

I'd like to not depend on the application making this call, because other
applications which connect to the same database will be written, and the
developers might not build in this same explicit procedure call -- it
really is the kind of thing that is best done on the backend.

~Berend Tober

Berend,

I've got something like that setup on an e-communities site I built. There was
already a "last action" query/report I had so what I have setup on as part of
the database nightly vacuum is to first delete any account that did not have any
actions for over a year. You don't need to do a trigger to do that. You just
need to make a cron job that run at whatever is an acceptable interval.

--
Keith C. Perry, MS E.E.
Director of Networks & Applications
VCSN, Inc.
http://vcsn.com

____________________________________
This email account is being host by:
VCSN, Inc : http://vcsn.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[btober]

Quoting bt****@seaworthysys.com:

Is there any kind of mechanism in pg 7.3 for doing something like
what I would describe as a "login trigger" procedure to run
when a user connects to the database?

Berend,

I've got something like that setup on an e-communities site I built.
There was already a "last action" query/report I had so what I have
setup on as part of the database nightly vacuum is to first delete any
account that did not have any actions for over a year. You don't need
to do a trigger to do that. You just need to make a cron job that run
at whatever is an acceptable interval.

--
Keith C. Perry, MS E.E.

That sounds somewhat interesting, and I understand the crontab approach,
but the key to your methods is that "last action" thing. I can see how I
might get a date from some table that has an column value that gets
assigned the current date after insert or update (using DEFAULT or a
trigger), but what if the user's access is read-only, i.e., they perform
only SELECT operations? How could I catch that?

~Berend Tober


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster