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).

One could use an exclusive lock. When the client goes away
so does the lock.

If the security definer func can get the lock on an existing
entry the entry is bogus.

Karsten