Shipping Multi-Tenant SaaS Using Postgres Row-Level Security

Be aware when using RLS with views: By default the RLS policy will be executed with the permissions of the owner of the view instead with the permissions of the user executing the current query. This way it can easily happen that the RLS policy will be bypassed because the owner of the view is a admin account or the same account that owns the underlying table (see the the gotchas section of the original post).<p>However, upcoming PostgreSQL 15 adds support for security invoker views: <a href="https:&#x2F;&#x2F;github.com&#x2F;postgres&#x2F;postgres&#x2F;commit&#x2F;7faa5fc84bf46ea6c543993cffb8be64dff60d25" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;postgres&#x2F;postgres&#x2F;commit&#x2F;7faa5fc84bf46ea6...</a> That means you can then define the security_invoker attribute when creating a view and this &quot;... causes the underlying base relations to be checked against the privileges of the user of the view rather than the view owner&quot; (see <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;15&#x2F;sql-createview.html" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;docs&#x2F;15&#x2F;sql-createview.html</a>) PG15 beta 1 release notes: <a href="https:&#x2F;&#x2F;www.postgresql.org&#x2F;about&#x2F;news&#x2F;postgresql-15-beta-1-released-2453&#x2F;" rel="nofollow">https:&#x2F;&#x2F;www.postgresql.org&#x2F;about&#x2F;news&#x2F;postgresql-15-beta-1-r...</a>

This is such a killer feature in PG, my new job uses it and it makes audits of our tenancy model dead simple.<p>Coming from a SaaS company that used MySQL, we would get asked by some customers how we guarantee we segmented their data, and it always ended at the app layer. One customer (A fortune 10 company) asked if we could switch to SQL Server to get this feature...<p>Our largest customers ask how we do database multi-tenant and we point to our SDLC + PG docs and they go &#x27;K&#x27;.

I don&#x27;t fully understand the performance implications here.<p>Say I was using this for a blog engine, and I wanted to run this SQL query:<p><pre><code> select * from entries; </code></pre> But I actually only want to get back entries that my current user is allowed to view - where author_id = 57 for example.<p>Would PostgreSQL automatically turn the above query into the equivalent of this:<p><pre><code> select * from entries where author_id = 57; </code></pre> And hence run quickly (assuming there&#x27;s an index on that author_id column)?<p>Or would it need to run an additional SQL query check for every single row returned by my query to check row permissions, adding up to a lot of extra overhead?

We were looking at RLS, various ABAC integrated frameworks (casbin, ..), and zanzibar clones late last year --<p>* RLS is super appealing. Long-term, the architecture just makes so much more sense than bringing in additional maintenance&#x2F;security&#x2F;perf&#x2F;etc burdens. So over time, I expect it to hollow out how much the others need to do, reducing them just to developer experience &amp; tools (policy analysis, db log auditing, ...). Short-term, I&#x27;d only use it for simple internal projects because cross-tenant sharing is so useful in so many domains (esp if growing a business), and for now, RLS seems full of perf&#x2F;expressivity&#x2F;etc. footguns. So I wouldn&#x27;t use for a SaaS unless something severely distinct tenant like payroll, and even then, I&#x27;d have a lot of operational questions before jumping in.<p>* For the needed flexibility and app layer controls, we took the middle of casbin, though others tools emerging to. Unlike the zanzibar style tools that bring another DB + runtime + ..., casbin&#x27;s system of record is our existing system of record. Using it is more like a regular library call than growing the dumpster fire that is most distributed systems. Database backups, maintenance, migrations, etc are business as usual, no need to introduce more PITAs here, and especially not a vendor-in-the-middle with proprietary API protocols that we&#x27;re stuck with ~forever as a dependency.<p>* A separate managed service might make zanzibar-style OK in some cases. One aspect is ensuring the use case won&#x27;t suffer the view problem. From there, it just comes down to governance &amp; risk. Auth0 being bought by Okta means we kind of know what it&#x27;ll look like for awhile, and big cloud providers have growing identity services, which may be fine for folks. Startup-of-the-month owning parts of your control plane is scarier to me: if they get hacked, go out of business, get acquired by EvilCorp or raise $100M in VC and jack up prices, etc.<p>There&#x27;s a lot of innovation to do here. A super-RLS postgres startup is on my list of easily growable ideas :)<p>On a related note: We&#x27;re doing a bunch of analytics work on how to look at internal+customer auth logs -- viz, anomaly detection, and supervised behavioral AI -- so if folks are into things like looking into account take overs &amp; privilege escalations &#x2F; access abuse &#x2F; fraud in their own logs, would love to chat!

As the developer of an external authorization system (full disclosure)[0], I feel obligated to chime in the critiques of external authorization systems in this article. I don&#x27;t think they&#x27;re far off base, as we do recommend RLS for use cases like what the article covers, but anyways, here&#x27;s my two cents:<p>1+2: Cost + Unnecessary complexity: this argument can be used against anything that doesn&#x27;t fit the given use case. There&#x27;s no silver bullet for any choice of solution. You should only adopt the solution that makes the most sense for you and vendors should be candid about when they wouldn&#x27;t recommend adopting their solution -- it&#x27;d be bad for both the users and reputation of the solution.<p>3: External dependencies: That depends on the toolchain. Integration testing against SpiceDB is easier than Postgres, IMO [1]. SpiceDB integration tests can run fully parallelized and can also model check your schema so that you&#x27;re certain there are no flaws in your design. In practice, I haven&#x27;t seen folks write tests to assert that their assumptions about RLS are maintained over time. The last place you want invariants to drift is authorization code.<p>4: Multi-tenancy is core to our product: I&#x27;m not sure I&#x27;m steel-manning this point, but I&#x27;ll do my best. Most companies do not employ authorization experts and solutions worth their salt should support modeling multi-tenant use cases in a safe way. SpiceDB has a schema language with idioms and recommendations to implement functionality like multi-tenancy, but still leaves it in the hands of developers to construct the abstraction that matches their domain[2].<p>[0]: <a href="https:&#x2F;&#x2F;github.com&#x2F;authzed&#x2F;spicedb" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;authzed&#x2F;spicedb</a><p>[1]: <a href="https:&#x2F;&#x2F;github.com&#x2F;authzed&#x2F;examples&#x2F;tree&#x2F;main&#x2F;integration-testing" rel="nofollow">https:&#x2F;&#x2F;github.com&#x2F;authzed&#x2F;examples&#x2F;tree&#x2F;main&#x2F;integration-te...</a><p>[2]: <a href="https:&#x2F;&#x2F;docs.authzed.com&#x2F;guides&#x2F;schema" rel="nofollow">https:&#x2F;&#x2F;docs.authzed.com&#x2F;guides&#x2F;schema</a>

We&#x27;re currently using the schema-per-tenant, and it&#x27;s working very well for us:<p>* No extra operational overhead, it&#x27;s just one database<p>* Allows to delete a single schema, useful for GDPR compliance<p>* Allows to easily backup&#x2F;restore a single schema<p>* Easier to view and reason about the data from an admin point of view<p>* An issue in a single tenant doesn&#x27;t affect other tenants<p>* Downtime for maintenance is shorter (e.g. database migration, non-concurrent REINDEX, VACUUM FULL, etc.)<p>* Less chance of deadlocks, locking for updates, etc.<p>* Allows easier testing and development by subsetting tenants data<p>* Smaller indexes, more efficient joins, faster table scans, more optimal query plans, etc. With row level security, every index needs to be a compound index<p>* Easy path to sharding per tenant if needed. Just move some schemas to a different DB<p>* Allows to have shared data and per-tenant data on the same database. That doesn&#x27;t work with the tenant-per-database approach<p>There are a few cons, but they are pretty minor compared to the alternative approaches:<p>* A bit more code to deal in the tenancy, migrations, etc. We opted to write our own code rather than use an existing solution<p>* A bit more hassle when dealing with PostgreSQL extensions . It&#x27;s best to install extensions into a separate extensions schema<p>* Possible caching bugs so you need to namespace the cache, and clear the query cache when switching tenant<p>* The security guarantees of per tenant solution aren&#x27;t perfect, so you need to ensure you have no SQL injection vulnerabilities

I find adding loads of stuff to Postgres exciting and fun, but I want all of my logic in the code in GitHub, rather that floating around in my global data store. Has anyone thought about a data layer that allows you to define this stuff programmatically rather than in SQL but then it configures your data layer to work like this. Not necessarily an ORM but more a business logic layer that compiles everything down to use features like this. Or maybe even a data layer that is a set of programmatic building blocks that works as described?

&gt;Another issue we caught during testing was that some requests were being authorized with a previous request’s user id.<p>This is the terrifying part about RLS to me: having to rely on managing the user id as part of the database connection session seems like an easy way to shoot yourself in the foot (especially when combined with connection pooling). Adding WHERE clauses everywhere isn&#x27;t great, but at least it&#x27;s explicit.<p>That said, I&#x27;ve never used RLS, and I am pretty curious: it does seem like a great solution other than that one gotcha.

Am I right in my understanding that EVERY request that comes in to their api creates a new connection to the database? What about reusing connections with connection pools or one level up using pgbouncer or thing. Can you actually use RLS while reusing connections?

I use RLS quite heavily for my app Sudopad (<a href="https:&#x2F;&#x2F;sudopad.com" rel="nofollow">https:&#x2F;&#x2F;sudopad.com</a>) and it has been working quite well so far.<p>One gotcha specific to Supabase (where I run the backend) is because there is no anonymous login in Supabase, turning on RLS and using database functions marked as security definers are the way to go. Otherwise there is no easy way of stopping a &#x27;select * from x&#x27; since some rows might not have a user_id if they are anonymous and I still want people to access the row if they know a specific primary key uuid.

Row-level security is always a tricky and hard to enforce assumption as this not how we relational databases really.<p>Much bigger fan of the approach described here:<p>Scalability, Allocation, and Processing of Data for Multitenancy<p><a href="https:&#x2F;&#x2F;stratoflow.com&#x2F;data-scalability-allocation-processing-multitenancy&#x2F;" rel="nofollow">https:&#x2F;&#x2F;stratoflow.com&#x2F;data-scalability-allocation-processin...</a>

I use this for a startup in a re-write of their solution. It simplifies my queries and mutations, and security concerns. It also drammatically reduces the complexity of my code. There&#x27;s also ROLES (Guest&#x2F;Public user, Authenticated, Admin) and combinding the roles with Row Level Security.<p>I like it so much I don&#x27;t want to go back!

I needed this two years ago, i was looking at this but couldn&#x27;t figure out how to do it with a existing db connection pool to reuse connections. I might be migrating to this soon so that things will be more isolated from the tenants.

this is cool. next up, row level encryption with private information retrieval methods for enabling queries and searches homomorphically (on data encrypted by the client that the service provider never has a key for).

Curious how advanced authorization (like ABAC) can be implemented with RLS. For example returning resources that are accessible to the team I belong to within the tenant.

If I were leaning into RLS today I would do it through PostgREST

I once implemented RLS&#x2F;Postgres for Django.<p>It worked pretty well.<p>The basic mechanism was to intercept all outbound SQL queries and wrap them in postgres environment variables that set up the RLS.

This is awesome. Are there any similar features that can be implemented with Mariadb? One of my favorite products is integrated with Mariadb.

Context aware data access is really cool. And hard :)

What about stop using ORM abstractions as an option then it is much harder to forget needed filters?

Using RLS to implement multi-tenancy is a terrible idea. Just deploy a database per tenant. It&#x27;s not hard. Why overcomplicate it?

Is having to write &quot;SELECT [...] WHERE user_id=&lt;123&gt;&quot; really considered a security hole? Isn&#x27;t that how like every service in existence operates? Coming up with complicated auth systems and patterns just because you are scared you will accidentally skip that WHERE clause seems bizarre to me.