---
title: How to Add Row Level Security to Views in PostgreSQL
---

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.

<!--more-->

**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.