About Me!
This blog is about my musings and thoughts. I hope you find it useful, at most, and entertaining, at least.
Other Pages
Presence Elsewhere
Row Level Security in PostgreSQL 9.5
Date: 2015-08-21
Tags: postgres development
I am extremely excited about What’s new in PostgreSQL 9.5’s_new_in_PostgreSQL_9.5 that I wanted to try it out before its final release. Here I’ll be discussing the new Row Level Security (RLS) features in PostgreSQL 9.5. All examples here are done in postgresql-9.5 α2.
RLS is a feature that allows the contents of a table to be filtered before being returned to the user. For instance, if you have a web store and would like to allow a user to only see their own orders, traditionally we would filter with an explicit WHERE clause.
Setup:
rlstest=# create table orders (order_id serial primary key, amount integer not null check (amount > 0.0), time_placed timestamptz default current_timestamp, customer text); CREATE TABLE rlstest=# insert into orders (amount, customer) values (1000, 'honor'), (2175, 'nimitz'); INSERT 0 2
Honor is viewing a page that shows her orders.
rlstest=# select order_id, amount, customer from orders where customer = 'honor';
order_id | amount | customer
----------+--------+----------
1 | 1000 | honor
(1 row)
This is all fine and dandy, until someone, somewhere, inadvertently or maliciously leaves off or makes ineffective the access control clause
rlstest=# select order_id, amount, customer from orders;
order_id | amount | customer
----------+--------+----------
1 | 1000 | honor
2 | 2175 | nimitz
(2 rows)
Whoops!
How can RLS help to prevent issues such as these? The database can be told to only allow different roles to see only certain roles, defined once, not for every query. This differes from the normal GRANTs you could give roles because they filter the rows, not simply allow or disallow operations on a given table as a whole.
Set up: Let’s create the honor and nimitz roles. Note, this does mean that each of your application users would need a corresponding user in the database.
rlstest=# create role honor; CREATE ROLE rlstest=# create role nimitz; CREATE ROLE
Now let’s enable RLS on our orders table and create a read-only policy if you’re the customer of the order.
rlstest=# alter table orders enable row level security; ALTER TABLE rlstest=# create policy orders_reader on orders for select using (customer = current_user); CREATE POLICY
Now, let’s switch roles to honor and look at our order!
rlstest=> select order_id, amount, customer, time_placed from orders; ERROR: permission denied for relation orders
Oh No!
RLS does not supercede the normal GRANT allowances. If the role can’t see a table, then the RLS policy can’t change that! set role *YOUR USER* and GRANT select on orders. Now try again.
rlstest=# grant select on orders to honor;
GRANT
rlstest=# grant select on orders to nimitz;
GRANT
rlstest=# set role honor;
SET
rlstest=> select order_id, amount, customer, time_placed from orders;
order_id | amount | customer | time_placed
----------+--------+----------+------------------------------
1 | 1000 | honor | 2015-08-21 14:05:13.03572-04
(1 row)
Success!
rlstest=# set role nimitz;
SET
rlstest=> select order_id, amount, customer, time_placed from orders;
order_id | amount | customer | time_placed
----------+--------+----------+------------------------------
2 | 2175 | nimitz | 2015-08-21 14:05:13.03572-04
(1 row)
Yes!
Now, being good database developers, we normalize our databases and wouldn’t store the customer’s username in the order. So, let’s create a customers table and update our orders table. (Remember to switch back to your role!)
rlstest=# create table customers (customer_id serial primary key, username text not null unique); -- You could imagine a real table having more fields for email, phone, address, &c
CREATE TABLE
rlstest=# insert into customers (username) values ('honor'), ('nimitz');
INSERT 0 2
rlstest=# grant select on customers to honor;
GRANT
rlstest=# grant select on customers to nimitz;
GRANT
rlstest=# alter table orders add column customer_id integer references customers;
ALTER TABLE
rlstest=# update orders set customer_id = (select customer_id from customers where username = orders.customer);
UPDATE 2
rlstest=# alter table orders alter column customer_id set not null; -- We couldn't do this when we created the column, because on creation the column has the default (NULL in this case) value
ALTER TABLE
rlstest=# alter table orders drop column customer;
ERROR: cannot drop table orders column customer because other objects depend on it
DETAIL: policy orders_reader on table orders depends on table orders column customer
HINT: Use DROP ... CASCADE to drop the dependent objects too.
Good, we can’t drop the column because there is a policy on it!
rlstest=# alter table orders drop column customer cascade; NOTICE: drop cascades to policy orders_reader on table orders ALTER TABLE
Wait! Won’t everyone be able to see everything!
rlstest=# set role honor; SET rlstest=> select * from orders; order_id | amount | time_placed | customer_id ----------+--------+-------------+------------- (0 rows)
OK, well, still breaks things, but things breaking by not leaking information is a good way to break! Go back to your role.
So, what we want to do is allow people to see orders if their username is the current role, and the orders.customer_id is the same as that customers.customer_id. If we were writing a straight SQL query, we’d do something like
select * from orders inner join customers using (customer_id) where username = current_user;
However, we need to do this row by row, not as a set.
rlstest=# create policy orders_reader on orders for select using ( (select true from customers where username = current_user and customers.customer_id = orders.customer_id) );
CREATE POLICY
test=# set role honor;
SET
rlstest=> select * from orders;
order_id | amount | time_placed | customer_id
----------+--------+------------------------------+-------------
1 | 1000 | 2015-08-21 14:05:13.03572-04 | 1
(1 row)
rlstest=> set role nimitz;
SET
rlstest=> select * from orders;
order_id | amount | time_placed | customer_id
----------+--------+------------------------------+-------------
2 | 2175 | 2015-08-21 14:05:13.03572-04 | 2
(1 row)
But wait! Now everyone can see all the customers!
rlstest=# set role nimitz;
SET
rlstest=> select * from customers;
customer_id | username
-------------+----------
1 | honor
2 | nimitz
(2 rows)
Well, if we have phone numbers and other PII in there, that’s not good! No back to your user and enable RLS for customers!
rlstest=# alter table customers enable row level security; ALTER TABLE
Remember how we couldn’t see any orders before we created policies? Same is true.
rlstest=> select * from customers; customer_id | username -------------+---------- (0 rows)
Same true here! Since the policy for orders depends on the customers, will it affect how we see orders?
rlstest=> select * from orders; order_id | amount | time_placed | customer_id ----------+--------+-------------+------------- (0 rows)
Yes! Yes it does! Since the policy (or lack there of) won’t let us see any customers, none of the customers match the criteria for the orders policy!
Go back to your users and lets create a read-only policy for customers.
rlstest=# create policy customer_reader on customers for select using (username = current_user); CREATE POLICY
Now, let’s try this again
rlstest=# set role nimitz;
SET
rlstest=> select * from customers;
customer_id | username
-------------+----------
2 | nimitz
(1 row)
rlstest=> select * from orders;
order_id | amount | time_placed | customer_id
----------+--------+------------------------------+-------------
2 | 2175 | 2015-08-21 14:05:13.03572-04 | 2
(1 row)
Awesome! It works again!
But wait! In the policy for orders, we are explicitly limiting the customers based on username, and we do that on the policy for the customers too, so why do double work? Additionally, what if we ever want to change the rules for who can see customers (maybe we’ll add customers who can manage other customers, like a business account). As your role:
rlstest=# drop policy orders_reader on orders; DROP POLICY rlstest=# create policy orders_reader on orders for select using ( (select true from customers where customers.customer_id = orders.customer_id) ); CREATE POLICY
Let’s test it out!
rlstest=> set role honor;
SET
rlstest=> select * from customers;
customer_id | username
-------------+----------
1 | honor
(1 row)
rlstest=> select * from orders;
order_id | amount | time_placed | customer_id
----------+--------+------------------------------+-------------
1 | 1000 | 2015-08-21 14:05:13.03572-04 | 1
(1 row)
rlstest=> set role nimitz;
SET
rlstest=> select * from customers;
customer_id | username
-------------+----------
2 | nimitz
(1 row)
rlstest=> select * from orders;
order_id | amount | time_placed | customer_id
----------+--------+------------------------------+-------------
2 | 2175 | 2015-08-21 14:05:13.03572-04 | 2
(1 row)
Awesome! Now we are controlling the majority of our access control directly in the database, and what’s more, we’re controlling its granulary per table without having to cross-filter from one table to another, enabling us to work on the access control without leaking permissions without repeating yourself (“Keep it DRY”).
There is a caveat to all of this, though: views. Views run as the user who created them, not the user who executes them. As such, if your table-owner were to create a view, the view would have the RLS permissions of the table owner, namly, all permissions. As your role:
rlstest=# create view customer_view as select customer_id, username from customers;
CREATE VIEW
rlstest=# drop policy orders_reader on orders;
DROP POLICY
rlstest=# create policy orders_reader on orders for select using ( (select true from customer_view where customer_view.customer_id = orders.customer_id) );
CREATE POLICY
rlstest=# set role honor;
SET
rlstest=> select * from customers;
customer_id | username
-------------+----------
1 | honor
(1 row)
rlstest=> set role jim;
SET
rlstest=# grant select on customer_view to honor;
GRANT
rlstest=# grant select on customer_view to nimitz;
GRANT
rlstest=# set role honor;
SET
rlstest=> select * from customers;
customer_id | username
-------------+----------
1 | honor
(1 row)
rlstest=> select * from customer_view;
customer_id | username
-------------+----------
1 | honor
2 | nimitz
(2 rows)
rlstest=> select * from orders;
order_id | amount | time_placed | customer_id
----------+--------+------------------------------+-------------
1 | 1000 | 2015-08-21 14:05:13.03572-04 | 1
2 | 2175 | 2015-08-21 14:05:13.03572-04 | 2
(2 rows)
As you can see, the view is being run as the table owner and as such allows access to all rows.
One work around (if you really need to use a view, say hierarchical permissions), functions in a are excuted as the caller not the owner of the view.
rlstest=# create function can_see_customer(username text) returns boolean as $$begin
return current_user = username;
end;
$$
language 'plpgsql';
CREATE FUNCTION
rlstest=# create or replace view customer_view as (select * from customers where can_see_customer(username));
CREATE VIEW
rlstest=# set role honor;
SET
rlstest=> select * from customer_view;
customer_id | username
-------------+----------
1 | honor
(1 row)
rlstest=> select * from orders;
order_id | amount | time_placed | customer_id
----------+--------+------------------------------+-------------
1 | 1000 | 2015-08-21 14:05:13.03572-04 | 1
(1 row)
While this isn’t perfect, because you’re duplicating the access rules (to the customers in this example).
Now that we have policies working, we can look at them by describing a table in psql.
rlstest=# \d customers
Table "public.customers"
Column | Type | Modifiers
-------------+---------+-----------------------------------------------------------------
customer_id | integer | not null default nextval('customers_customer_id_seq'::regclass)
username | text | not null
Indexes:
"customers_pkey" PRIMARY KEY, btree (customer_id)
"customers_username_key" UNIQUE CONSTRAINT, btree (username)
Referenced by:
TABLE "orders" CONSTRAINT "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
Policies:
POLICY "customer_reader" FOR SELECT
USING ((username = ("current_user"())::text))
rlstest=# \d orders
Table "public.orders"
Column | Type | Modifiers
-------------+--------------------------+-----------------------------------------------------------
order_id | integer | not null default nextval('orders_order_id_seq'::regclass)
amount | integer | not null
time_placed | timestamp with time zone | default now()
customer_id | integer | not null
Indexes:
"orders_pkey" PRIMARY KEY, btree (order_id)
Check constraints:
"orders_amount_check" CHECK (amount::numeric > 0.0)
Foreign-key constraints:
"orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
Policies:
POLICY "orders_reader" FOR SELECT
USING (( SELECT true AS bool
FROM customer_view
WHERE (customer_view.customer_id = orders.customer_id)))
In a later article, I’ll discuss how RLS policies affect the query planner.