* David G. Johnston (david.g.johnston@xxxxxxxxx) wrote: > On Mon, Feb 23, 2015 at 6:01 PM, Stephen Frost <sfrost@xxxxxxxxxxx> wrote: > > > * David G Johnston (david.g.johnston@xxxxxxxxx) 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
Attachment:
signature.asc
Description: Digital signature
