Re: Row-level Security vs Application-level authz

* David Steele (david(at)pgmasters(dot)net) wrote:
> On 2/24/15 3:07 PM, Stephen Frost wrote:
> > The problem with a temporary table is, well, it goes away. :) There are
> > further concerns that, because it's created in some fashion by the
> > single application user, it might be less secure. Really, though, I'd
> > want it to be real so that it could have constraints be on it which
> > reference other appropriate tables, so the web user doesn't have to have
> > rights in any fashion to create objects, and so that it can be
> > referenced from RLS policies. A table as transient as a temporary table
> > doesn't strike me as the right solution for that.
>
> Temp tables go away at the end of the session, sure. It seems like
> exactly the time when you'd want them to do so.
>
> If the temp table is created by a security definer function (as was
> suggested earlier) then no special user privs are required.
>
> Being referenced from RLS polices is a good argument, though. I guess
> that's not possible with a temp table? Are they pre-parsed?

Actually, it is possible, but it creates a dependency on the temporary
table and when the temporary table goes away, so will the policy. This
isn't a huge issue for RLS, of course, as if there's no policy then the
default-deny policy will kick in, but you can't have a policy against a
temporary table continue past the end of that session.

Following the earlier discussion, I suppose you could create both the
temporary table and the policies in the security definer function, but
it feels a lot cleaner to have a real table for all of that, in my view,
to keep that security definer function nice and simple.

Further, there's lots of other reasons to have a session table anyway,
from an application standpoint, and so this feels like an approach which
is more in-line with how the application likely wants to operate anyway.
It's also handy to be able to log into the database and see all the
current sessions, similar to how we have pg_stat_activity.

Thanks!

Stephen