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 / How do PostgreSQL security_barrier views work?
craig.ringer

How do PostgreSQL security_barrier views work?

November 7, 2013/8 Comments/in Craig's PlanetPostgreSQL, PostgreSQL /by craig.ringer

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.

Normal 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 example.Given table:

CREATE TABLE t AS SELECT n, 'secret'||n AS secret FROM generate_series(1,20) n;

and view:

CREATE VIEW t_odd AS SELECT n, secret FROM t WHERE n % 2 = 1;

a query like:

SELECT * FROM t_odd WHERE n < 4

is view-expanded inside the query rewriter into a parse tree representation of a query like:

SELECT * FROM (SELECT * FROM t WHERE n % 2 = 1) t_odd WHERE n < 4

which the optimizer then flattens into a single pass query by eliminating the subquery and appending the WHERE clause terms to the outer query, producing:

SELECT * FROM t t_odd WHERE (n % 2 = 1) AND (n < 4)

Even though you can’t see the intermediate queries directly and they never exist as real SQL, you can observe this process by enabling debug_print_parse = on, debug_print_rewritten = on and debug_print_plan = on in postgresql.conf. I won’t reproduce the parse and plan trees here as they’re quite large and easy to generate based on the examples above.

The problem with using views for security

You might think that granting somebody access to the view without granting them access to the underlying table would stop them seeing even-numbered rows. Initially it looks like that’s true:

regress=> SELECT * FROM t_odd WHERE n < 4;
 n | secret  
---+---------
 1 | secret1
 3 | secret3
(2 rows)

but when you look at the plan you might see a potential problem:

regress=> EXPLAIN SELECT * FROM t_odd WHERE n < 4;
                    QUERY PLAN                     
---------------------------------------------------
 Seq Scan on t  (cost=0.00..31.53 rows=2 width=36)
   Filter: ((n < 4) AND ((n % 2) = 1))
(2 rows)

The view subquery has been optimized away, with the view’s qualifiers appended directly to the outer query.

In SQL, AND and OR aren’t ordered. The optimizer/executor are free to run whichever branch they think is more likely to give them a quick answer and possibly let them avoid running the other branches. So if the planner thinks that n < 4 is much faster than n % 2 = 1 it’ll evaluate that first. Seems harmless, right? Try:

regress=> CREATE OR REPLACE FUNCTION f_leak(text) RETURNS boolean AS $$
BEGIN
  RAISE NOTICE 'Secret is: %',$1;
  RETURN true;
END;
$$ COST 1 LANGUAGE plpgsql;

regress=> SELECT * FROM t_odd WHERE f_leak(secret) AND n < 4;
NOTICE:  Secret is: secret1
NOTICE:  Secret is: secret2
NOTICE:  Secret is: secret3
NOTICE:  Secret is: secret4
NOTICE:  Secret is: secret5
NOTICE:  Secret is: secret6
NOTICE:  Secret is: secret7
NOTICE:  Secret is: secret8
NOTICE:  Secret is: secret9
NOTICE:  Secret is: secret10
NOTICE:  Secret is: secret11
NOTICE:  Secret is: secret12
NOTICE:  Secret is: secret13
NOTICE:  Secret is: secret14
NOTICE:  Secret is: secret15
NOTICE:  Secret is: secret16
NOTICE:  Secret is: secret17
NOTICE:  Secret is: secret18
NOTICE:  Secret is: secret19
NOTICE:  Secret is: secret20
 n | secret  
---+---------
 1 | secret1
 3 | secret3
(2 rows)

regress=> EXPLAIN SELECT * FROM t_odd WHERE f_leak(secret) AND n < 4;
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on t  (cost=0.00..34.60 rows=1 width=36)
   Filter: (f_leak(secret) AND (n < 4) AND ((n % 2) = 1))
(2 rows)

Whoops! As you can see, the user-supplied predicate function was considered to be cheaper to run than the other tests, so it was passed every row before the view’s predicate had excluded it. A malicious function could use the same trick to copy the row.

security_barrier views

security_barrier views fix that by forcing the qualifiers on the view to be executed first, before any user-supplied qualifiers run. Instead of expanding the view and appending any view qualifiers to the outer query, they replace the reference to the view with a subquery. This subquery has the security_barrier flag set on its range-table entry, which tells the optimizer it shouldn’t flatten the subquery or push outer query conditions down into it like it would for a normal subquery.

So with a security barrier view:

CREATE VIEW t_odd_sb WITH (security_barrier) AS SELECT n, secret FROM t WHERE n % 2 = 1;

we get:

regress=> SELECT * FROM t_odd_sb WHERE f_leak(secret) AND n < 4;
NOTICE:  Secret is: secret1
NOTICE:  Secret is: secret3
 n | secret  
---+---------
 1 | secret1
 3 | secret3
(2 rows)

regress=> EXPLAIN SELECT * FROM t_odd_sb WHERE f_leak(secret) AND n < 4;
                          QUERY PLAN                           
---------------------------------------------------------------
 Subquery Scan on t_odd_sb  (cost=0.00..31.55 rows=1 width=36)
   Filter: f_leak(t_odd_sb.secret)
   ->  Seq Scan on t  (cost=0.00..31.53 rows=2 width=36)
         Filter: ((n < 4) AND ((n % 2) = 1))
(4 rows)

The query plan should tell you what’s happening, though it doesn’t show the security barrier attribute in the explain output. The nested subquery forces a scan on t with the view qualifier, then the user-supplied function runs on the result of the subquery.

But. Wait a sec. Why is the user-supplied predicate n < 4 also inside the subquery? Isn’t that a potential security hole? If n < 4 is pushed down, why isn’t f_leak(secret)?

LEAKPROOF operators and functions

The explanation for that is that the < operator is marked LEAKPROOF. This attribute indicates that an operator or function is trusted not to leak information, so it can be safely pushed down through security_barrier views. For obvious reasons you can’t set LEAKPROOF as an ordinary user:

regress=> ALTER FUNCTION f_leak(text)  LEAKPROOF;
ERROR:  only superuser can define a leakproof function

and the superuser can already do whatever they want, so they don’t need to resort to playing tricks with functions leaking information to get past a security barrier view.

Why can’t you update security_barrier views

Simple views in PostgreSQL 9.3 are automatically updatable, but security_barrier views aren’t considered “simple”. That’s because updating views relies on being able to flatten the view subquery away, turning the update into a simple update on a table. The whole point of security_barrier views is to prevent that flattening. UPDATE cannot currently operate on a subquery directly, so PostgreSQL will reject any attempt to update a security_barrier view:

regress=> UPDATE t_odd SET secret = 'secret_haha'||n;
UPDATE 10
regress=> UPDATE t_odd_sb SET secret = 'secret_haha'||n;
ERROR:  cannot update view "t_odd_sb"
DETAIL:  Security-barrier views are not automatically updatable.
HINT:  To enable updating the view, provide an INSTEAD OF UPDATE trigger or an unconditional ON UPDATE DO INSTEAD rule.

It is this limitation that I’m interested in lifting as part of work to progress row-level security for the AXLE project. Kohei KaiGai has done some great work with row-level security, and features like security_barrier and LEAKPROOF have largely arisen out of his work toward adding row level security to PostgreSQL. The next challenge is how to deal with updates on a security barrier securely and in a way that will be maintainable into the future.

Why subqueries?

You might be wondering why we have to use subqueries for this. I did. The short version is that we don’t have to, but if we don’t use subqueries we instead have to create new order-sensitive variants of the AND and OR operators and teach the optimizer that it can’t move conditions across them. Since views are already expanded as subqueries, it’s much less complicated to just flag subqueries as fences that block pull-up / push-down.

There’s already a short-circuiting ordered operation in PostgreSQL though – CASE. The problem with using CASE that no operations may be moved across the boundary of a CASE, even LEAKPROOF ones. Nor can the optimizer make index use decisions based on expressions inside a CASE term. So if we used CASE as I asked about on -hackers we could never use an index to satisfy a user-supplied qualifier.

In the code

security_barrier support was added in 0e4611c0234d89e288a53351f775c59522baed7c. It was enhanced with leakproof support in cd30728fb2ed7c367d545fc14ab850b5fa2a4850. Credits appear in the commit notes. Thanks to everybody involved.

Front page feature image is Security Barrier by Craig A. Rodway, on Flikr


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: leakproof, PostgreSQL, row security, security, security_barrier, view
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
8 replies
  1. Patrick
    Patrick says:
    June 17, 2016 at 11:59 am

    This is a nice read, but I feel left wanting a bit. I have two questions:

    Views can be cascaded: a view built on views built on views built on tables, etc. This whole “building” will be flattened by the optimizer. Presumably the security_barrier is only needed on those views where permissions are granted, correct? So, where a NOLOGIN role creates all tables and views and grants permissions, the following should be adequate:

    CREATE TABLE t1 (…);

    CREATE TABLE t2 (…);

    CREATE VIEW v1 AS
    SELECT some_columns FROM t1 WHERE c1 < 100;

    CREATE VIEW v2 WITH (security_barrier) AS
    SELECT v.c2, v.c3, t.c2 FROM t2 t JOIN v1 v USING (c1) WHERE t2.c3;

    GRANT SELECT ON v2 TO some_lowly_user;

    Which leads to the second question: If we GRANT SELECT ON v1 TO some_admin_user and add WITH (security_barrier) to view v1 because we are appropriately security-conscious (a.k.a. paranoid), will that pose a problem with the view hierarchy? In other words, can we have multiple security_barriers in a view hierarchy?

    Reply
    • craig.ringer
      craig.ringer says:
      June 17, 2016 at 1:09 pm

      All views grant the permissions of their owner for the purpose of the query that defines the view. Whether security_barrier views or not. If you can SELECT from a view, you do so as the view owner. Easy test:

      CREATE ROLE schema_owner;
      CREATE ROLE joe_user;
      
      SET ROLE schema_owner;
      CREATE TABLE t(a integer);
      REVOKE ALL ON t FROM public;
      CREATE VIEW v_t AS SELECT * FROM t;
      REVOKE ALL ON v_t FROM public;
      GRANT SELECT ON v_t TO joe_user;
      INSERT INTO t(a) VALUES (1);
      
      SET ROLE joe_user;
      SELECT * FROM t;
      SELECT * FROM v_t;
      

      So, in your example above, it’s adequate only if you revoke rights from tables t1 and t2 and view v1 from public And yes, in that case you do want a security barrier on v2 to stop them leaking information around the view.

      You can nest security barrier views. You may pay a performance price in terms of extra materialisation steps or similar if the outer views use any non-leakproof operators but it works just fine.

      Reply
  2. Dave Sharpe
    Dave Sharpe says:
    October 11, 2017 at 12:33 pm

    The error you hit provides the answer: “HINT: To enable updating the view, provide an INSTEAD OF UPDATE trigger” Why isn’t that good enough?

    Reply
  3. mkurz
    mkurz says:
    April 20, 2018 at 5:36 pm

    Postgres 10 does not use subqueries anymore. Instead quals will be ordered and prioritized now. More information can be found in the commit message of the change here: https://github.com/postgres/postgres/commit/215b43cdc8d6b4a1700886a39df1ee735cb0274d That should speed things up.

    Reply
    • craig.ringer
      craig.ringer says:
      April 21, 2018 at 6:33 am

      Thanks for pointing that out, appreciated.

      Reply
  4. Nathan Lacey
    Nathan Lacey says:
    October 16, 2019 at 6:59 pm

    There are still a lot of core postgres functions that aren’t marked leakproof. Example ->> operator for jsonb fields (jsonb_array_element_text or jsonb_object_field_text)
    With these functions not being marked as leakproof, we can have horrible performance when you enable RLS because the indexes on your tables that utilized jsonb functions, no longer get used because of the security barrier!
    Has there been any thought about making a postgres plug-in or fork that makes any required changes so all core functions could be marked as leak-proof. This would result in more consistent query performance when RLS is enabled.

    Reply
    • craig.ringer
      craig.ringer says:
      November 4, 2019 at 1:14 pm

      It doesn’t make sense to mark all core functions and operators as leakproof. That would defeat the purpose of RLS and make it trivial to bypass.

      If there are specific functions and operators you think should be leakproof but are not, please post to pgsql-hackers and supply a rationale for why they are safe to mark as leakproof. It’s unlikely that such changes would be backported into existing released versions, but it’s also possible for you to make site-local modifications to the PostgreSQL catalogs to mark individual things as leakproof.

      Reply
  5. PRASANTHI
    PRASANTHI says:
    December 18, 2019 at 9:46 am

    How to do RLS on materialized views?
    TYIA

    Reply

Trackbacks & Pingbacks

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
Postgres and devops: testing 9.3 with Vagrant and Puppet – part two PostgreSQL guts: What is “resjunk”?
Scroll to top
×