2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Craig's PlanetPostgreSQL3 / Row security in PostgreSQL – Overview
craig.ringer

Row security in PostgreSQL – Overview

February 26, 2014/5 Comments/in Craig's PlanetPostgreSQL /by craig.ringer

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

Tags: axle, PostgreSQL, row security, security, security_barrier
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
5 replies
  1. Jérôme Radix
    Jérôme Radix says:
    February 26, 2014 at 12:17 pm

    Thanks for this research. I will follow your blog for this subject.

    I’ve made a little research by myself on this same subject for MySQL 5. I’ve called that “MySQL 5.0 Fine-Grained Access Control (FGAC)”

    http://www.jeromeradix.com/2006/12/mysql-50-fine-grained-access-control.html

    Reply
  2. Neil McGuigan
    Neil McGuigan says:
    February 26, 2014 at 6:55 pm

    This is the feature I am most looking forward to in 9.4. Nice work.

    Reply
    • craig.ringer
      craig.ringer says:
      February 27, 2014 at 4:08 am

      Unfortunately, at this point it’s not looking like it’s going to make the cut for 9.4. There are some significant issues remaining to address, as I’ll discuss later in the series.

      Hopefully updatable security barrier views will, though; if so, that’ll significantly ease row security.

      Reply

Trackbacks & Pingbacks

  1. Row Level Security | Axle Project says:
    November 18, 2014 at 7:20 pm

    […] about our work on Row Level Security on the 2ndQuadrant […]

    Reply
  2. Row Level Security | Axle Project says:
    August 25, 2014 at 12:09 pm

    […] Read about our work on Row Level Security […]

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Streaming replication slots in PostgreSQL 9.4 Announcing repmgr 2.0RC2
Scroll to top
×