Re: Row-level Security vs Application-level authz

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

Another option might be an extension which provides a GUC that can be
updated with a security definer function (but not otherwise) and which
is cleared at DISCARD ALL. That requires the application to still
handle the user authentication (instead of having the security definer
function handle that), but as that's already happening today, it might
not be an issue and would still allow removal of most of the
application-side authorization complexity in favor of using RLS
policies.

Thanks!

Stephen