A few days ago I've blogged about the common issues with roles and privileges we discover during security reviews.
Of course, PostgreSQL offers many advanced security-related features, one of them being Row Level Security (RLS), available since PostgreSQL 9.5.
As 9.5 was released in January 2016 (so just a few months ago), RLS is fairly new feature and we're not really dealing with many production deployments yet. Instead RLS is a common subject of "how to implement" discussions, and one of the most common questions is how to make it work with application-level users. So let's see what possible solutions there are.
One of the services we offer are security reviews (or audits, if you want), covering a range of areas related to security. It may be a bit surprising, but a topic that often yields the most serious issues is roles and privileges. Perhaps the reason why roles and privileges are a frequent source of issues is that it seems to be quite simple and similar to things the engineers are familiar with (e.g. Unix system of users and groups), but it turns out there are a few key differences with major consequences.
The other parts are either very straightforward and understandable even for sysadmins without much PostgreSQL experience (e.g. authentication config in pg_hba.conf), or the engineers recognize the complexity and take their time to familiarize with the details (a good example of this is (more…)
PostgreSQL 9.5 adds declarative row security. You can declare policies on tables and have them enforced automatically - for example, allowing user joe to only see rows with the owner column equal to joe.
This is a great feature, and it's been a long time coming. It didn't make it into PostgreSQL 9.4, but automatically updatable security_barrier views did. They and LEAKPROOF functions form part of the foundation on which row security is built. You can use these pieces without the declarative policy support to achieve row-security-like effects in 9.4.
I discussed security_barrier views earlier. That post contains examples of how information can be leaked from a view and how security_barrier views prevent such leaks. I'll assume you're familiar with the principles in the rest of this post, (more…)
In the next week I will be writing a series of posts about the row-security work I've been doing for PostgreSQL 9.4 as part of the EU's AXLE project. I will be outlining the history, approaches tried, current status, remaining issues, and future work required.
To open the series, I'd like to talk about what row security is good for and why you might want it.
The purpose of the row-security feature is to allow different users to see different subsets of the data in the same table, according to admin-provided security rules. Security policy can use arbitrary SQL expressions to control data visibility, including sub-queries against other tables.
If you're paying attention, this will sound a lot like a view. You're not mistaken - row security is not unlike transparently replacing (more…)
You might have seen the support added for security_barrier views in PostgreSQL 9.2. I've been looking into that code with an eye to adding automatic update support for them as part of progressing row-level security work for the AXLE project, and I thought I'd take the chance to explain how they work.
Robert already explained why they're useful and what they protect against. (It turns out it's also discussed in what's new in 9.2). Now I want to go into how they work and discuss how security_barrier views interact with automatically updatable views.
A normal simple view is expanded in a macro-like fashion as a subquery which is then usually optimized away by pulling its predicate up and appending it to the quals of the containing query. That might make more sense with an (more…)