On Tue, Feb 24, 2015 at 8:37 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * David Steele (david@pgmasters.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.
This is starting to sound like a web app, which I have experience
with. The cardinal rule: assume everybody disconnects randomly, and
code accordingly :).
The goal here isn't to make the session table reflect the number of
users who are currently logged in. Rather, it's to ensure the session
table doesn't grow infinitely.
* You can set a restriction like, "a user can only be logged in once".
During login, delete other sessions associate with that user. The
session table's maximum size is the size of the user table.
* You can use an expiry-check function. Best is a definitive "this
session is disconnected"; if you can't do that, you can try a rule
such as "user cannot be logged in more than 20 times and sessions
older than two weeks are invalid". During login, run the expiry
checker on that user and delete expired rows. With the right function,
you can constrain the session table to a reasonable size.
* You can simply limit the size of the session table. If your limit is
100 and a user starts a 101st session, delete the first session.
The world of websites involves lots of users and loads of short-lived
sessions. A website doesn't check whether the user has access to a
row: it checks whether the user has access to an endpoint with the
given parameters. Postgres RLS seems like a bad approach for that use
case.
Enjoy life,
Adam
--
Adam Hooper
+1-613-986-3339
http://adamhooper.com