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.