Re: Row-level Security vs Application-level authz

On Mon, Feb 23, 2015 at 6:01 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> * David G Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
> > My quick take-away from RLS compared to traditional multi-tenant security
> > policies is that with RLS you move the security logic into the database
> and
> > leverage the native database roles. Your model likely makes use of a
> single
> > user associated with an application and that application applies the
> > security logic during its interactions with the client-users that it
> > maintains separately.
>
> Note that you could still use RLS even with a single application user
> logging into PG. This can be done by having an authentication mechanism
> which is implemented in the database using a security definer function
> which updates a table (most likely unlogged, as it's for current
> sessions only and needs to be performant) that indicates which user is
> logged in for the current database connection. The RLS policies would
> then refer to that table to determine which rows can be operated on.
> The table would need to be cleaned up at the end of the session, but
> that should be reasonably straight-forward to do (again, with a security
> definer function).
>

​Does this still require actual roles to be created for the users in
question?

I take it that the table has to be permanent otherwise you would have
suggested
and unlogged temporary table as the target...

An example in the wiki of this idea would be welcomed by at least one member
of the community.

David J.
​