Skip site navigation (1) Skip section navigation (2)

[v9.2] Fix Leaky View Problem

From: Kohei Kaigai <Kohei(dot)Kaigai(at)EMEA(dot)NEC(dot)COM>
To: Noah Misch <noah(at)2ndquadrant(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Kohei KaiGai<kaigai(at)kaigai(dot)gr(dot)jp>
Subject: [v9.2] Fix Leaky View Problem
Date: 2011-08-24 12:38:12
Message-ID: D0C1A1F8BF513F469926E6C71461D9EC04C49D@EX10MBX02.EU.NEC.COM (view raw or flat)
Thread:
Lists: pgsql-hackers
Hi,

The attached patches try to tackle our long-standing leaky view problem, and were revised according to the discussion we had in the commit-fest 1st.

We already knew two scenarios to leak contents of invisible tuples using functions with side-effects; such as error messages containing its arguments.

The first one was the order of execution of qualifiers within a scan plan. Query optimizer shall pull-up simple sub-queries into inner-join due to the performance gain, however, it possibly cause a problem that functions supplied at outside of the sub-query is launched earlier than functions come from inside of the sub-query; depending on the cost estimation. In the result, it allows users to reference contents of invisible tuples (to be filtered by view), if they provide a function with side-effects as a part of WHERE clause.

The second one was the distribution policy of qualifiers. In the case when a view (that intends row-level security) contains JOIN clause, we hope the qualifiers supplied from outside of the view to be launched after the table join, because the view may filter out some of tuples during checks of its join condition. However, the current query optimizer will distribute a qualifier that reference only one-side of the join into inside of the join-loop to minimize number of tuples to be joined. In the result, it also allows users to reference contents of invisible tuples.

In the commit-fest 1st, we made a consensus that a part of views should perform as "security barrier" that enables to prevent unexpected push-down and execution order of qualifiers; being marked by creator of the view.
And, we also made a consensus obviously secure functions should be allowed to push-down across security barrier; to minimize unnecessary performance damages.

The part-1 patch implements SQL enhancement stuffs; (1) add reloption support on RELKIND_VIEW with "security_barrier" bool variable (2) add pg_proc.proleaky flag to show whether the function is possibly leaky, or not.
The (2) is new stuff from the revision in commit-fest 1st. It enables to supply "NOLEAKY" option on CREATE FUNCTION statement, then the function is allowed to distribute across security barrier. Only superuser can set this option.

  Example)
    CREATE FUNCTION safe_func(text) RETURNS bool
        LANGUAGE 'C' NOLEAKY AS '$libdir/safe_lib', 'safe_func';
                     ^^^^^^^
A patch to add a new field into pg_proc always takes a large chunk, so the attached proctrans.php is the script I used to append a new field to the existing functions. Right now, I marked it true (= possibly leaky), because we need to have a discussion what shall be none-leaky functions in the default.

The part-2 patch is same as we had discussed in the commit fest. Here is not updates except for rebasing to the latest tree. It enables to remember the nest level of the qualifier being originally used, and utilize it to sort order of the qualifiers.

The part-3 patch was a bit revised, although its basic idea has not been changed.
It enables to prevent qualifiers come from outside of security barrier being pushed down into inside of the security barrier, even if it references only a part of relations within the sub-query expanded from a view with "security_barrier" flag.

Thanks,
--
NEC Europe Ltd, SAP Global Competence Center
KaiGai Kohei <kohei(dot)kaigai(at)emea(dot)nec(dot)com>

Attachment: proctrans.php
Description: application/octet-stream (286 bytes)
Attachment: pgsql-v9.2-fix-leaky-view-part-3.v1.patch
Description: application/octet-stream (27.9 KB)
Attachment: pgsql-v9.2-fix-leaky-view-part-2.v1.patch
Description: application/octet-stream (31.1 KB)
Attachment: pgsql-v9.2-fix-leaky-view-part-1.v1.patch
Description: application/octet-stream (814.0 KB)

Responses

pgsql-hackers by date

Next:From: Alexander KorotkovDate: 2011-08-24 13:57:28
Subject: Re: WIP: Fast GiST index build
Previous:From: Dougal SutherlandDate: 2011-08-24 11:33:54
Subject: patch to slightly improve clarity of a comment in postgresql.conf.sample

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group