Add PostgreSQL RLS post

1 files changed, 178 insertions, 0 deletions

diff --git a/_posts/2020-04-02-row-level-security-postgresql-views.md b/_posts/2020-04-02-row-level-security-postgresql-views.md
new file mode 100644
index 0000000..4231348
--- /dev/null
+++ b/_posts/2020-04-02-row-level-security-postgresql-views.md
@@ -0,0 +1,178 @@
+---
+title: How to Add Row Level Security to Views in PostgreSQL
+---
+
+Recently, for a project at work, I needed to store some customer-specific data
+in a PostgreSQL database and, using the PostgreSQL RBAC system, grant customers
+access to only their data in the shared tables. Fortunately, PostgreSQL has
+support for row level security which helps us do exactly that.
+
+However, 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! However, 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
+
+    # When you're done, remember to stop the container:
+    $ docker stop rlslab
+
+**Okay, 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
+
+Now, we'll 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`.
+
+Now, 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
+
+Now, 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, in
+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, of course,
+because then all the customer users would just see `customer_a`'s data.
+
+One solution would be 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
+    $$;
+
+Now, 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:
+
+    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.