1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
|
---
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.
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
$$;
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.
|
