How to Add Row Level Security to Views in PostgreSQL

Posted on Thursday April 2, 2020

Recently, I needed to store some customer-specific data in a PostgreSQL database and grant customers access to only their data in the shared tables. Fortunately, PostgreSQL has support for row level security in conjunction with its RBAC model which helps us do exactly that.

While row level security does exactly what we need it to for tables, I ran into a challenge when I needed to apply the same row level security to views built from the tables: row level security is only available on tables, not on views! Luckily, I was able to find a way to accomplish what I needed to and learned some more about Postgres along the way.

How to follow along in a Docker “lab” with our schema and dummy data:

# Run the docker container:
$ docker run --rm --detach --name rlslab benburwell/postgres-rls-lab

# Connect to the database in the container using psql:
$ docker exec -it rlslab psql -U postgres

# Remember to stop the container when you’re done!
$ docker stop rlslab

Back to the good stuff:

Let’s start off by creating some tables that we’ll store customer-specific data in. To grant our customers access to only their data in these tables, we’ll be creating a role for each customer, e.g. customer_a, customer_b, and so on, and we’ll include a customer_user column on each table that specifies the role which should have access to that row:

CREATE TABLE milestones (
  id serial primary key,
  customer_user varchar,
  name varchar
);

CREATE TABLE milestone_events (
  milestone_id int,
  customer_user varchar,
  name varchar
);

Now, we’ll create the customer users. To simplify management, we can create a generic customer role that has the access we want each customer to have, and then just grant that role to new customers as we onboard them.

CREATE ROLE customer;
GRANT SELECT ON milestones TO customer;
GRANT SELECT ON milestone_events TO customer;

Next, we’ll create our individual customer roles and grant them the privileges from the generic customer role we just created:

CREATE ROLE customer_a;
CREATE ROLE customer_b;
GRANT customer TO customer_a, customer_b;

Next, let’s populate our milestones and milestone_events tables with some dummy data:

postgres=# SELECT * FROM milestones;
 id | customer_user |          name
----+---------------+---------------------------
  1 | customer_a    | A great milestone
  2 | customer_a    | Another milestone
  3 | customer_b    | Customer B milestone
  4 | customer_c    | Spooky invisible milestone

postgres=# SELECT * FROM milestone_events;
 milestone_id | customer_user |      name
--------------+---------------+----------------
            1 | customer_a    | First task
            1 | customer_a    | Second task
            2 | customer_a    | Another task
            3 | customer_b    | B event
            4 | customer_c    | Invisible task

Now, we’ll add the row-level security policies to these tables so that customer users only have access to the appropriate rows in these tables:

postgres=# ALTER TABLE milestones ENABLE ROW LEVEL SECURITY;
ALTER TABLE
postgres=# CREATE POLICY customer_access ON milestones
postgres-# FOR SELECT
postgres-# USING (customer_user = current_user);
CREATE POLICY

Let’s switch over to the customer_a role and check out the results:

postgres=# set role customer_a;
postgres=> select * from milestones;
 id | customer_user |       name
----+---------------+-------------------
  1 | customer_a    | A great milestone
  2 | customer_a    | Another milestone

Nice! Because of our row-level security policy on the milestones table, we only see the rows where customer_user matches our current user, customer_a.

It would be really nice to create a view for these tables so that we can see all the events with their related milestone names. Let’s jump back to the postgres role and create the view:

postgres=# CREATE VIEW milestone_events_view AS
postgres-# SELECT milestone_id, m.name as milestone_name, e.name as event_name
postgres-# FROM milestone_events e
postgres-# JOIN milestones m ON e.milestone_id = m.id;
CREATE VIEW
postgres=# GRANT SELECT ON milestone_events_view TO customer;
GRANT

Let’s switch back over to our customer_a role and take a look:

postgres=> SELECT * FROM milestone_events_view;
 milestone_id |       milestone_name       |   event_name
--------------+----------------------------+----------------
            1 | A great milestone          | First task
            1 | A great milestone          | Second task
            2 | Another milestone          | Another task
            3 | Customer B milestone       | B event
            4 | Spooky invisible milestone | Invisible task

Whoa! We shouldn’t be able to see all these other customers’ data! That was the whole point of the row level security policy we set up! As it turns out, PostgreSQL views always adhere to the permissions of their owner (in this case the postgres superuser) rather than the current user.

How can we fix this? Changing the owner of the view wouldn’t help us because then all the customer users would just see customer_a’s data.

My solution was to create a function that does the selection. In Postgres, functions can either be run with the privileges of the user who created them (by specifying SECURITY DEFINER), or as the user calling them (with SECURITY INVOKER).

CREATE FUNCTION customer_milestone_events()
RETURNS TABLE (
  milestone_id int,
  milestone_name varchar,
  event_name varchar
)
LANGUAGE sql
SECURITY INVOKER
AS $$
  SELECT milestone_id, m.name AS milestone_name, e.name AS event_name
  FROM milestone_events e
  JOIN milestones m ON e.milestone_id = m.id
$$;

In order to make the results conveniently available as a view, we can create a view based on this function:

CREATE VIEW pub_milestone_events AS SELECT * FROM customer_milestone_events();
GRANT SELECT ON pub_milestone_events TO customer;

Now, when we switch over to our customer_a role and query our new view, we only see the rows we’re supposed to see:

postgres=> select * from pub_milestone_events ;
 milestone_id |  milestone_name   |  event_name
--------------+-------------------+--------------
            1 | A great milestone | First task
            1 | A great milestone | Second task
            2 | Another milestone | Another task

And as customer_b:

postgres=> select * from pub_milestone_events ;
 milestone_id |    milestone_name    | event_name
--------------+----------------------+------------
            3 | Customer B milestone | B event

Tada! Row level security on views in PostgreSQL.