Image for post
Image for post
Elephants standing performantly between some trees. Photo by Jeff Lemond on Unsplash

Designing the most performant Row Level Security schema in Postgres

In an effort to write less application logic in my services and interfaces, (and out of pure laziness), I am persistently looking for performant strategies to bake access control directly into my schemas. Row Level Security has been around in Postgres for a while now, but we have recently gotten some upgrades to the optimizer which make it worth a deeper investigation.

But why?

If you’re not familiar with Row Level Security, the main hypothesis is that we should be able to prevent access to specific rows of data based on a policy. That means our application logic only has to worry about SELECT * FROM my_table and RLS will handle the WHERE user_id = my_user_id part automagically.

SELECT id, value
FROM items
WHERE created_at > now() - interval '1 week'
AND items.owner_id = $1
SELECT id, value
FROM items
WHERE created_at > now() - interval '1 week'

Requirements

Since the goal is to produce something usable for real applications, let’s talk requirements:

  1. Items can be shared with individuals
  2. Items can be shared with groups
  3. Items can be made public
  4. Read and write permissions can be handled separately

Produce these results locally!

Everything to reproduce these tests is available on GitHub, but I’ll just copy the gists on here to keep things simple. Each of the schemas is generated with a dataset of 100 users who can write to 1000 items and read from a subset of the rest.

BEGIN;
SET LOCAL jwt.claims.roles = ‘c94a743e-8ffd-42dc-a237-b1613e111f53,be95031e-76e0–401a-808c-5c7acde292de’;
-- this represents an example query from an interface
SELECT count(*) FROM items;
COMMIT;

RLS Policy, ACL stored in a column

This is likely to be the most performant approach to RLS since we won’t need any SELECTs in our policy; permissions are stored directly on the items. The biggest drawback about storing an ACL in a column is that it will get very wide for items shared with many users/groups, and that may impact performance.

Item Table

We’ll use the following schema to define our table:

  1. We are using a GIN index for the ACL columns to ensure we maintain good performance on the arrays of permissions.

Item Policy

Image for post
Image for post
ACL in column, 100 users, public attribute. Results here: https://explain.depesz.com/s/ivDo

Performance

As for all of these schemas, I’m testing it using a simple query: EXPLAIN ANALYZE SELECT count(*) FROM items. Running on my local machine this completes in under 1ms.

RLS Policy, ACL stored in a table

Using rows-in-tables instead of arrays-in-columns feels so much more…normal. The only question: does doing it the “right” way ruin performance? Probably…

Item and Permissions Table

For this schema we can take full advantage of a normalized database and foreign key all of the things. To take it one step further, this schema uses an enumerable value for the role of each permission. We could use that ENUM to add more permission types, (like owner), without impacting performance.

Object Policy

Here’s a massive policy:

Performance

Performance is certainly slower at a whopping 22ms for only 100 users, but it is still pretty fast:

Security-barrier View, ACL stored in a table

Moving on, let’s use the same schema as the previous approach but tell the planner what’s up by using a VIEW that JOINs more performantly. Similar to the column-based ACL schema, we’ll have to use a group to implement the public feature.

Object View

Performance

Image for post
Image for post
Image for post
Image for post

Next Steps

Both of the RLS schemas performed well and may be valuable tools for different use cases. The table-based approach avoids modifying existing tables, while the column-based approach if blazingly fast. In my mind, the only thing to do next is trying building something with both of them and see what pain points emerge.

Written by

I build things by breaking them.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store