Re: Row-level Security vs Application-level authz

* David G. Johnston (david(dot)g(dot)johnston(at)gmail(dot)com) wrote:
> 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?

No.

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

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

It's been my intention to build that; perhaps I can find resources in
the near future to turn that into a reality.

Thanks!

Stephen