Re: Row-level Security vs Application-level authz

Adam,

* Adam Hooper (adam@adamhooper.com) wrote:
> 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 :).

Haha, I like it. :)

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

Agreed.

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

Right, that all certainly makes sense to me, but I'm not sure the idea,
as it relates to RLS, was clear.

Consider that you *already* have per-user data in the system.  This
might be in the form of facebook friends, with perhaps a friend mapping
table:

CREATE TABLE friendships (
  friend_source    text,
  friend_dest      text,
  primary key (friend_source, friend_dest)
);

Then you have a sessions table, ala:

CREATE TABLE sessions (
  pg_pid           integer,
  username         text,
  attribute1       text,
  attribute2       text,
  etc
);

Now, you want a given DB session to only be able to see their friends
and not the friends of others, so you might have a policy on friendships
like so:

CREATE POLICY friend_policy ON friendships USING (
  friend_source = (
    SELECT username FROM sessions WHERE pg_pid = pg_backend_pid()
  )
);

The sessions table isn't where RLS is really being used, it's on the
other tables.  The session table is used just as a way to figure out
which user is currently logged in for the purposes of the filter which
is applied via RLS.  Having a GUC or server-side variable of some kind
would work too, provided it had the right characteristics (which is
mostly about making sure that the web app can't somehow "fake" the
user's credentials and become whichever user it wants- this would be
done in the above approach by having a security definer function which
requires credentials from the user to be passed in and only if that
matches is the session table updated to indicate that user as the one
who is logged in; that's not a complete fail-safe, of course, but it's a
lot better than the usual case of the web application having unfettered
access to the data in the database).

Note that the above is all off the cuff for this discussion and may have
syntax or other issues with it. :)

    Thanks!

        Stephen