From 725661fe9d4e996d71843f00fc19b6d664b2aad5 Mon Sep 17 00:00:00 2001 From: Ben Burwell Date: Thu, 2 Apr 2020 10:23:17 -0400 Subject: Use fewer commas in RLS post --- ...20-04-02-row-level-security-postgresql-views.md | 57 +++++++++++----------- 1 file changed, 29 insertions(+), 28 deletions(-) (limited to '_posts') diff --git a/_posts/2020-04-02-row-level-security-postgresql-views.md b/_posts/2020-04-02-row-level-security-postgresql-views.md index 4231348..223cf88 100644 --- a/_posts/2020-04-02-row-level-security-postgresql-views.md +++ b/_posts/2020-04-02-row-level-security-postgresql-views.md @@ -2,16 +2,16 @@ 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. +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. -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. +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. @@ -23,10 +23,10 @@ learned some more about Postgres along the way. # 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: + # Remember to stop the container when you're done! $ docker stop rlslab -**Okay, back to the good stuff:** +**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 @@ -91,7 +91,7 @@ users only have access to the appropriate rows in these tables: postgres-# USING (customer_user = current_user); CREATE POLICY -Now, we'll switch over to the `customer_a` role and check out the results: +Let's switch over to the `customer_a` role and check out the results: postgres=# set role customer_a; postgres=> select * from milestones; @@ -103,9 +103,9 @@ Now, we'll switch over to the `customer_a` role and check out the results: 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: +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 @@ -115,7 +115,7 @@ see all the events with their related milestone names. Let's jump back to the 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: +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 @@ -127,17 +127,17 @@ Now, let's switch back over to our `customer_a` role and take a look: 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. +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, of course, -because then all the customer users would just see `customer_a`'s data. +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. -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`). +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 ( @@ -153,13 +153,14 @@ created them (by specifying `SECURITY DEFINER`), or as the user calling them 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: +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: +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 -- cgit v1.2.3