Posted on

Row Level Security in Postgres: how and why

Row Level Security (RLS) is a feature of the PostgresQL database engine that allows you to restrict at a database level operations on each row on the basis of which user is executing said operation, based on a policy that you define when you apply RLS to a table.

An example use-case

RLS is useful when you want to restrict access to your data and you do not have an application layer in within to broker this data and filter based on who is accessing it.

An easy example to understand this is the following: you have a DB and an application layer that handles all data acess and validations via an API, but you want to integrate a third party reporting solution to your system, which can only handle direct DB connections. In this reporting solution, users of your application will be able to create custom reports based on their needs. The problem that arises is: how do you make sure that users only see their own data when you do not have an application layer in within to handle this logic? Consider the basic diagram below

Before going on to explain exactly how it works and why it is useful, let us a consider a basic scenario to use as an example: you are developing an application where restaurant owners can list their restaurants, and where users can look them up based on what kind of food they want to eat.

In our toy example, an entity Company will exist, which can own one or more restaurants. For simplicity’s sake, our other two entities will be User and Restaurant.

With this simple model in mind, imagine the scenario where you need to give users access to the restaurants of their company only, so that they can report on it on your third-party reporting solution.

Before moving on to implementing, let us create the tables and populate them with data

CREATE TABLE companies (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE restaurants (
    id SERIAL PRIMARY KEY,
    name TEXT,
    address TEXT,
    company_id INT REFERENCES companies(id)
);

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT,
    company_id INT REFERENCES companies(id)
);

INSERT INTO companies (name) VALUES ('Company A'), ('Company B');

INSERT INTO restaurants (name, address, company_id) VALUES
('Restaurant 1', 'Address 1', 1),
('Restaurant 2', 'Address 2', 1),
('Restaurant 3', 'Address 3', 2);

INSERT INTO users (email, company_id) VALUES
('alice@test.com', 1),
('bob@other.com', 2);

How to Implement RLS

To implement RLS on your database, you must enable it on a per-table basis. This is simply done by running ALTER TABLE tablename ENABLE ROW LEVEL SECURITY.

Once you have RLS activated for a given table, you must create policies that state under which conditions each row can be accessed. An implicit default deny-all policy applies, meaning that no one will be able to access any row unless you state it so. Also, keep in mind that table owners bypass this security-check which is run before each query unless you explictly state it; this means that we do not have to write additional policies for our application layer’s DB user if that user owns the tables.

By default, all policies that apply to a table are created as permissive, meaning that they will evaluated with OR operator. When you create a policy, you can explicitly create it to be restrictive and evaluate them with an AND operator instead.

At the same time, a policy can be applied to a specific operation on a row (SELECT, UPDATE, DELETE, INSERT) or to all of them at once.

Following our toy example from above, we want to grant each user an ability to see their own data in a third-party reporting tool. To do this, we will take the following steps:

  1. Create roles for each individual user, where the user role name is their email
  2. Grant these roles access to SELECT on the restaurants table
  3. Enable RLS on the restaurants table and create appropiate policy
  4. Test RLS was implemented

Creating the Roles

Before we start, we need to create the DB roles that will benefit from the RLS policy. To do this, we will run:

CREATE ROLE "alice@test.com" WITH LOGIN PASSWORD 'password';
CREATE ROLE "bob@other.com" WITH LOGIN PASSWORD 'password';

Granting Roles Access to Select on the Table

With our roles in place, we need to go ahead and grant them access to perform the SELECT operation in our restaurants table. Keep in mind that this step is previous to RLS; as it is now, this would grant each role access to select all rows in the table

GRANT SELECT ON restaurants TO "alice@test.com";
GRANT SELECT ON restaurants TO "bob@other.com";

Enabling RLS

To enable RLS on our restaurants table, we will run:

ALTER TABLE restaurants ENABLE ROW LEVEL SECURITY;

Creating and Managing RLS Policies

Now that RLS is activated on the table, we need to create a policy to grant each user access to their companies’ restaurants. Before we do this, we must create a function that we can run that can tell us the company ID of the user that is running the query. We can do this using a native Postgres function:

CREATE OR REPLACE FUNCTION get_user_company_id() RETURNS INT AS $$
BEGIN
    RETURN (SELECT company_id FROM users WHERE email = current_user);
END;
$$ LANGUAGE plpgsql;

Now that we have a function to find out a user’s company, we can create the policy itself:

CREATE POLICY company_policy ON restaurants
FOR SELECT
USING (company_id = get_user_company_id());

Keep in mind policies can be selected, altered, or dropped, like other objects in the Postgres, like this:

-- Select policies on table
SELECT * FROM pg_policies WHERE tablename = 'restaurants';

-- Drop a policy
DROP POLICY company_policy ON restaurants;

-- Alter a policy
ALTER POLICY company_policy ON restaurants
FOR SELECT, UPDATE
USING (company_id = get_user_company_id());

Testing RLS

With our RLS policies and roles in place, we can test them by assuming the role of a user from Company A and of Company B, and seeing the resulting output. 

SET ROLE 'alice@test.com';
SELECT * FROM restaurants;

-- Output

 id |     name      |  address  | company_id 
----+---------------+-----------+------------
  1 | Restaurant 1  | Address 1 |          1
  2 | Restaurant 2  | Address 2 |          1

SET ROLE 'bob@other.com';
SELECT * FROM restaurants;

-- Output

 id |     name      |  address  | company_id 
----+---------------+-----------+------------
  3 | Restaurant 3  | Address 3 |          2

Having succesfully implemented and tested RLS in our restaurants table, we can now hand over to the users the roles that we have created for them, so that they can use them safely in the third-party reporting solution, knowing they will only be able to access the rows they own when running SQL queries against our database.

Leave a comment