Re: Row-level Security vs Application-level authz

On 2/24/15 3:07 PM, Stephen Frost wrote:
> * David Steele (david(at)pgmasters(dot)net) wrote:
>> On 2/23/15 8:16 PM, Stephen Frost wrote:
>>> * David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
>>>> I take it that the table has to be permanent otherwise you would have
>>>> suggested
>>>> and unlogged temporary table as the target...
>>>
>>> A temporary table would have to be recreated each time and that'd be
>>> less than ideal. You can use a single unlogged table which includes the
>>> backend pid (which can be acquired through a function call) to keep
>>> track of which user is logged in on a given backend at a given point in
>>> time.
>>
>> It's not clear to me why creating a temp table per session would be less
>> than ideal. I've certainly used session-scope temp tables to good
>> effect a number of times. Transaction-scope would be another story of
>> course.
>>
>> Am I missing something?
>
> 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?

--
- David Steele
david(at)pgmasters(dot)net