Re: Row-level Security vs Application-level authz

* David Steele (david(at)pgmasters(dot)net) wrote:
> So I guess my last question is if you are inserting rows into a table to
> track user connections, how do you clean them out when the client does
> not disconnect cleanly? Or is this table intended to be append-only?

It wouldn't be intended to be append-only but I agree that, ideally,
there'd be a way to address clients disconnect uncleanly. One way to
address that would be by having the security definer function that's
called on entry check to see if there was a prior session for its pid
and log an error when found. With a connection pooler, that'd probably
turn up any issues pretty quickly as the set of pids would be relatively
small (compared to the overall potential pid space). Another approach
would be to have it check for all backends by joining against
pg_stat_activity, but that might result in false positives. A cron job
could also be used to check for sessions beyond a certain expected
lifetime (PHP and other systems do this at the filesystem level; it's
not ideal but it does seem to work).

Thanks!

Stephen