From 57ff3404e58e258c85a6aeae445d7023a141f0aa Mon Sep 17 00:00:00 2001 From: Ben Burwell Date: Thu, 2 Apr 2020 09:25:06 -0400 Subject: Add PostgreSQL RLS post --- ...20-04-02-row-level-security-postgresql-views.md | 178 +++++++++++++++++++++ 1 file changed, 178 insertions(+) create mode 100644 _posts/2020-04-02-row-level-security-postgresql-views.md 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. + + + +**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. -- cgit v1.2.3