Monday, August 17, 2015

Excited for Postgres 9.5(Row Level Security)

Every year around this time I get very excited about the new features that are getting ready to be released in the next version of Postgres. This year is no exception, There is a trove of new features that have me salivating. Today I am going to talk about one of these new features, Row Level Security. I hope to talk about some of the other features soon.

As an architect/developer of SaaS apps I can't help but be intrigued by the new Row Level Security model. Right now SaaS apps typically implement security either at the application level or by having seperate databases or schemas per customer. Each of these methodoligies has its pluses and minuses, but now we will have a new option to consider.

Application Level Security

This is what most people use when they write applications. You simply add a column to your tables to limit them to a specific user or customer and make sure you always query with those columns in your where clause or join somewhere. This works great 99% of the time, but what happens when some big customer comes up to you and wants to connect their data in your app to a custom reporting system, or they exercise the cluase in their contract where they want all their data from your database becuase they are leaving you? Another place where this could be an issue is what if your application layer is vulnerable to a SQL injection attack or some other nefarious attack. With application level security the attacker now has access to all the data in your database.

Seperate Databases/Schemas

This model of development solves the problems from above very effectively. It also has the added benfit of allowing you to shard your data horizontally across many machines which can be very advantageous as your company grows. There are however tradeoffs, and one of the big ones is complexity. You now have to track which database and schema to connect to for each user/customer, you have to figure out how to mange pools of connections for each database to each web box. Superuser/Administration activities become a big problem. Shared data is now a problem. Running analytics and metrics on your system is much harder.

Row Level Security

RLS gives you the ability to have the advantages of both with the exception of a convienent sharding mechanism. Using RLS you set up policies for tables which are nothing more then where clauses or check constraints that are applied on the table for every query. Now that you can move those security checks to the database It becomes easy to give access to only a customers data. There is also an option on pg_dump to enable RLS so you can easily only dump a users data. If your web box becomes compromised the only data that can be accessed now is the data for the customer that is logged in. (Clearly if the comprmoised user is a superuser the original problem persists.) 

I can't wait to try these features out in production and see if they deliver on their promises.