Blog - Spectory

By Noam S.

September 29, 2020

Row Level Security was introduced in v9.5 of PostgreSQL. For further information see PostgreSQL’s Documentation.

This is a privileges feature; by defining policies and enabling the RLS on specific tables, the database will filter the rows by the policy and return only the ones that are allowed. For writing queries, new data that is not allowed will be rejected.

For the set up we’ll need 3 things:

User that is not the owner
RLS policies
Enabling the policies on the tables

In this post, I’ll describe some common duplication sources.

Lets start…

1. User

The RLS is not working on the table owner and for superusers, except if, for example, you created the table with postgres user you’ll need to create another user for querying. lets call him app_user ;).

The simplest way to do this will be to create a role:

CREATE ROLE app_user NOINHERIT;
GRANT SELECT, UPDATE, INSERT,
DELETE ON ALL TABLES IN SCHEMA
public TO app_user;
-- before you run the queries set the role
SET ROLE app_user;

This will not work for a web app communicating it’s database through a new server:

CREATE USER app_user WITH PASSWORD pa$$w0rd NOINHERIT;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, UPDATE, INSERT, DELETE
ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA
public TO app_user;

The code above will update privileges on existing tables. To have this privilege for future tables, you’ll need to change the default privileges:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, UPDATE, INSERT, DELETE
ON TABLES TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT,
UPDATE ON SEQUENCES TO app_user;

2. Policies

“To specify which rows are visible or modifiable according to a policy, an expression is required that returns a Boolean result. This expression will be evaluated for each row prior to any conditions or functions coming from the user’s query… Rows for which the expression does not return true will not be processed” (taken from PostgreSQL’s Documentation).

The entities of the example in this post will be user doctor patient:

We’d like to create a policy allowing a user to select data from patients table. If the user is a patient, he will be able to select only the record related to him. A doctor will be able to select all patients of which he is their doctor.

Because we are developing the system for a web app, it’s important to separate between the two types of users we have.

The first is the database user. This is the user that executes the queries.

The second type is an app user which is a record in the user table. This is the user that sends requests from the client to the we server, regardless of whether or not this user performed authentication successfully, the queries will be executed by the DB user.

How can we let the DB know who is the authenticated user requested the querying? We can use Postgres session variables.

In the beginning of any work with the DB connection we’ll assign the current user to a runtime variable:

SET app.current_user_id = :user_id

Before releasing the connection, we’ll need to reset the variable:

RESET app.current_user_id

The same assignment is also available for transaction scope:

BEGIN;
SET LOCAL app.current_user_id = :user_id
...
COMMIT;

There is no need to reset on transaction since this variable is only available locally in the specific transaction.

Ok, we have a DB user; we have runtime app user; now let’s write the policy!

We’ll start with the condition for a user that is a patient. The Policy is in a table scope, so that the name of it shouldn’t be unique between tables. We’ll call it patient_select:

CREATE POLICY patient_select on patient 
USING (user_id = ANY(
    ARRAY(SELECT id
    FROM user
    WHERE id = current_setting('app.current_user_id')::bigint)
);

The USING clause is the logic of the policy used to check existing rows, used in policies of SELECT, UPDATE and DELETE. For INSERTs there is an equivalent CHECK WITH that will run the condition on the input values.

current_settings is used to access the runtime variables.

Let’s add the condition for a user which is a doctor for selecting patients:

CREATE POLICY patient_select on patient 
USING (
       user_id = current_setting('app.current_user_id')::bigint
       OR
       doctor_id = ANY(
             ARRAY(SELECT id 
                   FROM doctor
                   WHERE user_id = current_setting('app.current_user_group_id')::bigint
                   LIMIT 1
      );

That’s almost it! All we have to do now is to enable RLS on patients table and we’re done!

3. Enable row level security

ALTER TABLE patients ENABLE ROW LEVEL SECURITY;

That was easy 🙂

The result:

Without RLS:

SELECT * FROM patient
INNER JOIN doctor on
patient.doctor_id = doctor.id
WHERE user_id = :user_id
OR doctor.user_id = :user_id

With RLS:

SET app.current_user_id = :user_id
SELECT * FROM patient

That’s real nice! What’s even better is that you can built policies that rely on other policies. This is super elegant and safe.

Pros and cons

Pros:

RLS reduces the application development complexity. Without RLS, the filters will need to be written in each and every query. With RLS user_1 sees only what user_1 is allowed to see without changing the application code much.
With RLS, security is at the DB level and will work for every connection, meaning it does not depend on the servers API layer, much safer.
The DB backup and restore will keep RLS intact.

Cons:

More DB-centric work and a small amount of performance penalty.
Developers who aren’t strong on the DB side, troubleshooting RLS might take them a bit of time.