Row Level Security

Row Level Security (RLS)

At the basic level, Row Level Security (RLS) prevents a query from returning rows the user shouldn’t have access to within that a specific context. For instance, if RLS is enabled on a table and configured to append foo = 'bar' to your query, then if you run:

 Copy
SELECT * FROM contacts;

When enabled, RLS will always append that conditional, and Postgres will transform it to run:

 Copy
SELECT * FROM contacts WHERE foo = 'bar';

If RLS the configured is to append foo = 'bar' and baz = 'qux' to ALL queries, then if you run:

 Copy
INSERT INTO 
	contacts (name, email) 
VALUES ('Bruce Wayne', 'bruce@wayneenterprises.com');

It will always append that condition, and Postgres will transform it to run:

 Copy
INSERT INTO 
	contacts (name, email, foo, baz) 
VALUES ('Bruce Wayne', 'bruce@wayneenterprises.com', 'bar', 'quz')

In this way RLS can restrict SELECT, UPDATE, DELETE, and INSERT. We’ll walk through each in the examples below. All following queries work, so use the Postgres terminal to the right to test them out.

For this example, we will use a dataset that you might find in a CRM. The tables will be:

• contacts: an external contact
• opportunities: a contact who may potentially want to buy your product
• users: your internal users of the CRM
• team: a group of people who work together

In this scenario with a CRM, we’ll show a few different scenarios. The first scenario will be as if each “user” gets their own Postgres login. The next scenario would be if you are running an application that has access to teams.

Without Row Level Security

Without RLS, if a user has access to run SELECT against a table, then a user has access to the entire table. For instance, let’s create a new user called appuser:

 Copy
CREATE ROLE appuser LOGIN;
\x

Since we are running as a superuser within the playground Postgres, we can now start behaving as the user with this command. We use this command multiple times in this tutorial to switch roles:

 Copy
SET ROLE appuser; -- this is possible in Postgres as the superuser, or on any granted roles

Now, let’s try to query the accounts table, and you’ll get a permissions error:

 Copy
SELECT * FROM contacts;

After which, you should see permission denied for table accounts. So, let’s fix the permissions issue by acting as the root user, assigning permissions, and come back to the appuser user:

 Copy
SET ROLE postgres;
GRANT SELECT ON TABLE contacts TO appuser;
SET ROLE appuser;

Now, if you run the SELECT statement above, you’ll see that no rows are returned:

 Copy
SELECT * FROM contacts;

Great, so let’s give the appuser access to the rest of the databases:

 Copy
SET ROLE postgres;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO appuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO appuser;
SET ROLE appuser;

Great, so now the appuser user can do all things on all tables, like so:

 Copy
SELECT
	*
FROM contacts;

If you look at the response, you’ll see that the user called appuser has access to all records on the contacts table for all queries. Postgres has a solution for this.

Enabling RLS per User

To table row, level security on the contacts table, run the following:

 Copy
SET ROLE postgres;
ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;
SET ROLE appuser;

Now, because we have enabled ROW LEVEL SECURITY and because we are not a Postgres superuser, if you run the following you see that it returns zero rows:

 Copy
SELECT * FROM contacts;

Let’s grant permission to a certain value of rows for the appuser:

 Copy
SET ROLE postgres;
CREATE POLICY appuser_contacts_policy ON contacts FOR ALL 
	TO appuser
USING (account_id = 1);
SET ROLE appuser;

Now, if you run the following, you’ll see that you access all the contacts where account_id = 1:

 Copy
SELECT * FROM contacts;

But, if you try to join from the accounts table with a different value for account_id, you’ll see that it returns the account, but does not return the contacts associated with the account:

 Copy
SELECT 
	*
FROM accounts
LEFT JOIN contacts ON accounts.id = contacts.account_id 
WHERE accounts.id = 2;

If you do SET ROLE postgres; and run the above query, you’ll see a list of all rows.

When creating policies with explicit values, the functionality is limited. Postgres does have the capability to create roles for each user and grant them to other roles. Below, we create a new role called account_2, with it we create a RLS policy to access where account_id = 2 , and we give appuser the ability to change their role to user_2 . Run and examine each of the commands below:

 Copy
SET ROLE postgres;
DROP POLICY appuser_contacts_policy ON contacts;
CREATE ROLE account_2;
CREATE POLICY appuser_contacts_policy_account_2 ON contacts FOR ALL 
	TO account_2
USING (account_id = 2);
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO account_2;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO account_2;
GRANT account_2 TO appuser;
SET ROLE appuser;

To use this new functionality, we can do the following:

 Copy
SET ROLE account_2;
SELECT 
	*
FROM accounts
LEFT JOIN contacts ON accounts.id = contacts.account_id 
WHERE accounts.id = 2;
SET ROLE appuser;

By doing it this way, the appuser does not have privilege by default, but gains restricted privilege as it changes roles. Good news, there is a better way without having to manage multiple policies and privileges.

Using RLS with a session variable

Instead of using a fixed account, we can use the value of a variable. Then, we can dynamically limit query breadth to a single value. Let’s drop and create a new policy that uses a variable:

 Copy
SET ROLE postgres;
DROP POLICY appuser_contacts_policy ON contacts;
DROP POLICY appuser_contacts_policy_account_2 ON contacts;
CREATE POLICY appuser_contacts_policy ON contacts FOR ALL
	TO appuser
USING (account_id = NULLIF(current_setting('rls.account_id', false), '')::integer);
SET ROLE appuser;

This looks more complicated, but it’s not as complicated as it looks. Basically, it extracts a value from rls.account_id and converts it to an integer to be compared to account_id. Now, to run a query as the appuser user, we do can do the following:

 Copy
SET rls.account_id = 1;
SELECT * FROM contacts;

And, if we want to query from the second account_id, then we run:

 Copy
SET rls.account_id = 2;
SELECT * FROM contacts;

If you compare the two queries above, you’ll see that the query is limited to the the value for rls.account_id. If you are programming this into an application, when switching between accounts, change the rls.account_id value.

Works on INSERT, UPDATE, and DELETE

So far, we have been using CREATE POLICY policy_name FOR ALL. The FOR ALL clause applies the policy to UPDATE, SELECT, INSERT, and DELETE. Instead of ALL, you can also apply the policy on specific actions. When using these policies, actions that are omitted are not permitted.

Using the FOR ALL policy, if we run an INSERT, you’ll see that it fails if you try to insert a mismatched value between rls.account_id and the record’s account_id:

 Copy
SET rls.account_id = 2;
INSERT INTO contacts (name, email, account_id, created_at, updated_at) 
VALUES ('Bruce Wayne', 'bruce@wayneenerprises.com', 2, now(), now()) 
RETURNING id, account_id;

The above is successful, but the following will fail with new row violates row-level security policy for table "contacts”:

 Copy
SET rls.account_id = 2;
INSERT INTO contacts (name, email, account_id, created_at, updated_at) 
VALUES ('Joker', 'joker@laughfactory.com', 1, now(), now()) 
RETURNING id, account_id;

An update will throw the same error if you try to change an account_id:

 Copy
SET rls.account_id = 2;
UPDATE contacts
SET account_id = 1
WHERE account_id = 2;

You can selectively create policies on any of SELECT, UPDATE, INSERT, or DELETE. Below, we create individual policies for each command, except DELETE. Then we run the appuser user will not have permission to delete any rows, even those available:

 Copy
SET ROLE postgres;
DROP POLICY appuser_contacts_policy ON contacts;
CREATE POLICY appuser_contacts_select_policy ON contacts FOR SELECT
	TO appuser
USING (account_id = NULLIF(current_setting('rls.account_id', false), '')::integer);
CREATE POLICY appuser_contacts_update_policy ON contacts FOR UPDATE
	TO appuser
USING (account_id = NULLIF(current_setting('rls.account_id', false), '')::integer);
CREATE POLICY appuser_contacts_insert_policy ON contacts FOR INSERT
	TO appuser
USING (account_id = NULLIF(current_setting('rls.account_id', false), '')::integer);
SET ROLE appuser;

Now, if we run a delete command, it will return DELETE 0 :

 Copy
DELETE FROM contacts;

Summary

Row Level Security is a powerful feature, and when using variables, it is a flexible. Yes, you do have to start managing permissions, but some use cases call for this level of protection. We often see people move this direction in a multi-tenant world.