Using Postgres Row-Level Security in Python and Django

Postgres introduced row-level security in 2016 to give database administrators a way to limit the rows a user can access, adding an extra layer of data protection. What's nice about RLS is that if a user tries to select or alter a row they don't have access to, their query will return 0 rows, rather than throwing a permissions error. This way, a user can use select * from table_name, and they will only receive the rows they have access to with no knowledge of rows they don't.

Most examples of RLS limit row access by database user. This can be a powerful feature. In this article, we will have a look at how you can make this happen for your Django app. The problem most people run into when trying to implement row level security is that most web applications, including Django applications, connect to the database with a single user, which makes it hard to take advantage of row level security.

One way to get around this is to create a database user for each application user. We’ll start with just the database layer. We’ll build out our tables and create a couple of users, then write our first row level security policy to limit which rows those users can access. Once we have an understanding of how RLS works in Postgres, we’ll expand our project out into Django and see how we can handle working with policies and multiple database users in a web application.

By the way, if you are interested in using Row-Level Security in Ruby on Rails, we have a dedicated article for that here: Using Postgres Row-Level Security in Ruby on Rails.

How to use RLS at the database level

Before we get to the Django side of things, let's take a look at how RLS works in Postgres. We'll keep it simple and say we are building an app to help our salespeople keep track of their clients, and we want to make sure no salesperson can access the clients of another salesperson. (These are very competitive, cutthroat salespeople).

First, let's set up our tables and populate them with some data:

CREATE TABLE salespeople (id serial primary key, name text);
CREATE TABLE clients (id serial primary key, name text, salesperson_id integer);

INSERT INTO salespeople (name) values ('Picard');
INSERT INTO salespeople (name) values ('Crusher');

INSERT INTO clients (name, salesperson_id) values ('client1', 1);
INSERT INTO clients (name, salesperson_id) values ('client2', 2);
INSERT INTO clients (name, salesperson_id) values ('client3', 2);

Now, we have two salespeople. Picard has one client, and Crusher has two clients.

Next, we are going to need some database users, one for each salesperson. Because two salespeople might share the same name, we are going to use the id to create Postgres users. We are also going to create a role called salespeople. This will be the role we grant permissions on, and all of our salespeople can inherit from it.

CREATE ROLE "1";
CREATE ROLE "2";
CREATE ROLE salespeople;

GRANT select, insert ON clients TO salespeople;
GRANT salespeople TO "1";
GRANT salespeople TO "2";

This setup will come in handy in the next section when we have to deal with Django's tables in addition to the ones we create for our models.

Now we are ready to set up RLS on our clients table. Our policy will limit access to the Postgres current_user so that they can only view rows where current_user matches salesperson_id.

ALTER TABLE clients ENABLE ROW LEVEL SECURITY;
CREATE POLICY salesperson_clients ON clients USING (salesperson_id::text = current_user);

When we create the policy, we give it a name, salesperson_clients, and enter the table we want to set the policy on, clients. Next, we define the policy. In this case, it is very simple: the salesperson_id on the table must be equal to the value of current_user. We have to convert the salesperson_id from an integer to text because our current_user must be a string (we can't create Postgres users with integers as names).

Right now, we are logged in as the postgres user.

SELECT session_user, current_user;

 session_user  | current_user  
---------------+---------------
 postgres      | postgres
(1 row)

If we query our clients table, we will be able to see all the rows because RLS policies do not apply to superusers.

SELECT * FROM clients;

 id |  name   | salesperson_id 
----+---------+----------------
  1 | client1 |              1
  2 | client2 |              2
  3 | client3 |              2
(3 rows)

But if we change the current user, we only get the rows that belong to that user.

SET ROLE "1";
SELECT session_user, current_user;

 session_user | current_user 
--------------+--------------
 postgres     | 1
(1 row)

SELECT * FROM clients;

 id |  name   | salesperson_id 
----+---------+----------------
  1 | client1 |              1
(1 row)

How to Use Postgres Row-Level Security in Django

Now, how can we translate this to a Django application?

First, we will need to create a database user for each app user we create. One way to accomplish this would be to override the save method on the Salesperson model, but this is a great opportunity to take advantage of Django signals , so we'll create a signal that creates the database user after a new salesperson is saved.

Next, we'll have to figure out how to switch to the correct user when a salesperson logs in. For this, we can use a middleware that gets the salesperson_id and sets the role in the database.

Models

Our models reflect exactly what we set up in our earlier database example. Here I chose to make Salesperson a proxy of Django's built-in User model, but this is not required.

from django.db import models
from django.contrib.auth.models import User

class Salesperson(User):
    class Meta:
        proxy = True
    
class Client(models.Model):
    name = models.CharField(max_length=50)
    Salesperson = models.ForeignKey(Employee, on_delete=models.CASCADE)

Django Signals: Creating Our Database User

We want to create a new database user every time a new salesperson record is created. We can use Django signals to execute some code after a new record is saved. If you're not familiar with signals, the Django docs on this topic are easy to understand. If this piqued your interest, this article goes into more detail.

Here is the code for the signal itself, but you'll have to reference the above article to get it registered in your app:

from .models import Salesperson
from django.db.models.signals import post_save
from django.db import connection

def create_db_user(sender, instance, created, **kwargs):
    if created:
        user_id = instance.id
        with connection.cursor() as cursor:
            cursor.execute(f'CREATE ROLE "{user_id}"')
            cursor.execute(f'GRANT salespeople TO "{user_id}"')

post_save.connect(create_db_user, sender=Salesperson) 

The post_save signal can take a named argument created, which is a boolean. This avoids running the code every time we update the record and ensures it will only run when we create a new salesperson. From there, we can get the user id from the instance and use django.db.connection to run our SQL to create the role and grant permissions.

It's very important to note that if you want to use Django's built-in User model and the authentication that comes with it, you'll need to grant salesperson permissions on the django_admin_log and auth_user tables. That's why it's so helpful to have this parent role that all individual users inherit from.

Django Middleware: Setting Current User

Now, we can write a middleware to switch the database user to the current application user making the request.

from django.db import connection

class RlsMiddleware(object):
    def __init__ (self, get_response):
        self.get_response = get_response
        
    def __call__ (self, request):
        user_id = request.user.id
        with connection.cursor() as cursor:
            cursor.execute(f'SET ROLE "{user_id}" ')

        response = self.get_response(request)
        return response

We get the user id from the request object. After that, the code looks pretty similar to our signal. We use the Django db connection again to set the role to the corresponding database user, which should match the application user's id. Don't forget to register your middleware in settings.py.

Now we can use all of Django's built-in query methods while maintaining row-level security in Postgres. What is particularly cool is that, with the role set, all we need to do to get all of a salesperson's clients is call Client.objects.all(), and we can be sure that only the clients related to the salesperson will be returned. If a salesperson tries to query for a client that doesn't belong to them, they'll get zero results.

Conclusion

In this article we were able to create a simple but powerful row level security policy and, with the help of Django middleware and Django signals, implement the policy at the application level. We saw how to create database users each time we created a new application user, and looked at setting the database role to the correct user after log in, ensuring each application user only had access to the rows that belonged to them.

There are a few caveats here. For one, using the ids 1, 2, 3 is probably not a good idea in production. You'd want to set up some kind of UUID or some other identifier. Also, creating a new database user for every application user becomes hard to scale at a certain point. Row level security can be a useful tool for limiting access at the database level, and we just scratched the surface of what's possible.

Still, you should be sure RLS is the right solution for your application before trying to implement it. In particular, the performance implications of row-level security, and how the Postgres planner treats it for query plans should not be overlooked. This has been significantly improved in Postgres 10, but its still essential to monitor your Postgres query plans when using RLS.

In many cases, RLS is not needed, and you’ll be able to secure your data using the security measures already built into Django.

Share this article: If you liked this article you might want to tweet it to your peers.

About the Author

Josh is a former educator turned developer with a proven ability to learn quickly and adapt to different roles. In 2018 he changed careers from education to tech and has been excited to find that his communication and presentation skills have transferred over to his new technical career. He's always looking for a new challenge and a dedicated team to collaborate with.