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.

To put it another way: our queries should only contain the clauses requested by our interfaces and not the filters and conditions demanded by access control in a multi-tenant data store. Take this example:

SELECT id, value

FROM items

WHERE created_at > now() - interval '1 week'

AND items.owner_id = $1

In the above example the WHERE clause is imposing conditions required for access control, ( items.owner_id ), and requested by an interface, ( created_at ). If our interface grows more complex so could the WHERE clause and, all of a sudden, separating concerns becomes much harder. Mistakes will be made, and we will keep writing the same annoying access control logic into our queries.

Enter Row Level Security, which provides a space for us to define the conditions demanded by access control separately from the conditions requested by the interface. All of a sudden, our queries look like this:

SELECT id, value

FROM items

WHERE created_at > now() - interval '1 week'

And, all of a sudden, then can grow as complex as needed without impacting the access control side of the problem.

Requirements

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

Items are inherently private Items can be shared with individuals Items can be shared with groups Items can be made public Read and write permissions can be handled separately

It should also go without saying that we want our policies to evaluate quickly for large datasets; otherwise, our user experience will suck and no one will like us. I’ll use a couple of three letter initialisms throughout this post:

I will also use EXPLAIN ANALYZE a lot, which I suggest reading up on if you’re not familiar with it.

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.

In general, you can test any of these schemas the following way:

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;

Note that we are using jwt.claims.roles instead of an existing local variable, such as current_user , because those behave unexpectedly when querying views and we want this RLS strategy to work for everything, (tables and views). You can also just SELECT * FROM user_item_stats() , which grabs a random user ID as the role and executes the previously mentioned transaction.

In a real application, you would swap out the SELECT statement for whatever interesting query your user wants to run.

RLS Policy, ACL stored in a column

This is likely to be the most performant approach to RLS since we won’t need any SELECT s 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:

There are two key things which we’ve implemented in the table definition:

Read and write permissions are handled differently. We could add an owners column if we needed that as well. We are using a GIN index for the ACL columns to ensure we maintain good performance on the arrays of permissions.

Item Policy