Row security in PostgreSQL – Overview

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 references to a table with an updatable security_barrier view over its contents. It must be a security barrier to prevent data from being leaked by user defined functions used as predicates. If row-security is enforcing write access control, it there behaves like a WITH CHECK OPTION security_barrier view.

There are a few reasons why row-security is desirable, and more useful than simply defining a view over each table.

Most importantly, row-security is pluggable – in addition to looking policies up from the system catalogs, it’s also possible to use a policy hook to supply arbitrary policy from extensions. This serves as a building block for adding SELinux-controlled row-access policies or other declarative security policies, and for using row security as the basis for row-level audit logging.

Unlike replacing a relation with an updatable security_barrier view, it’s also easy to add, change, or remove row-security policy on a table without redefining anything else in the database. Replacing a table with a view requires you to play games with access permissions and the search_path, and most importantly other views that referenced the original table do not automatically now reference the view since search_path references in views are resolved at CREATE VIEW time. So you need to change the definition of all other views that refer to the table so they use the security view over the table instead.

There’s also a more abstract benefit to row security. It separates the structure of the database from access policy, much like CSS separates presentation from semantic content (theoretically). Like CSS, the separation is imperfect but useful.

Finally, row-security aids the implementation of a variety of standards-mandated security models and structures, allowing new groups to consider using PostgreSQL where they were previously unable to do so. Implementations of PCI, HL7, etc are significantly eased by having something like row security available to encode the specified access policy.

In future posts, I’ll be writing about: the history of row security development in PostgreSQL; row security and inheritance; challenges in implementing row security in PostgreSQL; choices between different implementation approaches (optimizer vs rewriter etc); the status of the implementation to date; and what needs to be done to get it into the hands of users. Keep an eye on the row security tag.

The research leading to these results has received funding from the European Union’s Seventh Framework Programme (FP7/2007-2013) under grant agreement n° 318633