Using PostgreSQL Row-Level Security in a high-growth startup for tenant isolation
By Christopher Manouvrier— Principal Software Engineer
Most companies protect their customer information like they would the Crown Jewels. In the modern era where Yahoo had 3 billion accounts breached and Facebook had half a billion user details leaked, it’s critical to place an emphasis on and invest in, protecting customer information.
So every team member at Dovetail is responsible for prioritizing security. As an engineer, this means that permissions are a core focus. We take a somewhat unusual approach to where permissions live within our technical architecture. While most modern web applications would make this the responsibility of the application layer, we’ve instead made it the responsibility of the data layer.
Our approach to permissions
At Dovetail, we leverage Row-Level Security (RLS) features built into PostgreSQL. By composing together GRANTS and POLICIES (that depend on transaction local parameters) we can ensure each customer’s data is logically isolated from one another. This is done without the overhead of having to add permission-related conditionals to the queries themselves when the application code is being written.
For example, instead of writing WHERE tenant_id = … in every query, this can be omitted, and PostgreSQL will automatically include it based on a policy. This means engineers can focus on writing product features and stay confident that tenant isolation will be enforced in a consistent and robust manner.
We initially dismissed RLS due to our false understanding that we would need to create a new distinct PostgreSQL database role each time a customer signed up for an account. However, we discovered it was viable to use a single database role in conjunction with a transaction local tenant_id setting to achieve tenant isolation.
Column-Level security
Unfortunately, RLS doesn’t give you fine-grained column-level permissions when using a single shared database role. For example, we have a requirement where only workspace admins are allowed to update the title column on the workspace table. Normally you would use column-level grants to accomplish this using different database roles, but in our case, this was not an option.
Early on, an option we explored was to split out the columns that admins were able to edit into a separate table with a one-to-one foreign key relation. This meant we could use a separate policy on this new table to only permit admins to make changes. This works for simple cases but began to fall over when we introduced more complicated requirements and combinations of permissions.
We eventually settled on a pattern of using AFTER UPDATE triggers that raise exceptions. This allowed us to define arbitrarily complex permission logic at the database level. Using this pattern, we still have an underlying RLS policy that limits by tenant_id.
What we’ve learned
After five years of using RLS at Dovetail, we’ve built up a set of practices and techniques that help us address some of the common gotchas.
If a cyclical dependency is detected in a policy that is theoretically possible due to your schema (but in practice isn’t due to your dataset) you can break the chain by calling a user-defined function. This works because function contents don’t contribute to static cyclical analysis.
However, this can cause the query planner to be naive at times. One way to work around this is to define a large COST on the function to inform the planner that it should try determining a plan that avoids evaluating it frequently.
Unfortunately, it is easy to write policies that cause queries to perform poorly. As such it’s important to invest time into learning the ins and outs of RLS and its implications on performance. Here are some articles that we have found helpful.
Our bet on RLS has meant we’ve been able to constantly evolve the product and maintain engineering velocity without compromising security. If we had our time again we would go down this route again in a heartbeat.
If you’re interested in tackling permissions and optimizing performance outside of the norm, check out our open roles — we’re hiring!
