Re: [v9.2] Fix leaky-view problem, part 1 - Mailing list pgsql-hackers

The attached patches are revised version.

The part-0 provides 'security_barrier' option for view definition, and performs
as a common basis of part-1 and part-2 patches.
Syntax is extended as follows:

  CREATE VIEW view_name [WITH (param [=value])] AS query;

We can also turn on/off this security_barrier setting by ALTER TABLE with
SET/RESET options.

The part-1 patch enforces the qualifiers originally located under the security
barrier view to be launched prior to ones supplied on upper level.
The differences from the previous version is this barrier become conditional,
not always. So, existing optimization will be applied without any changes
onto non-security-barrier views.

Example)
postgres=# CREATE FUNCTION f_leak(text,text) RETURNS bool
             COST 0.0001 LANGUAGE 'plpgsql'
             AS 'begin raise notice ''% => %'', $1, $2; return true; end';
CREATE FUNCTION
postgres=#   CREATE TABLE credit_card (cname text, cnumber text, cexpired text);
  INSERT INTO credit_card (cname, cnumber, cexpired)
CREATE TABLE
postgres=#   INSERT INTO credit_card (cname, cnumber, cexpired)
                 VALUES ('alice', '1111-2222-3333-4444', '07/2014'),
                               ('bob', '5555-6666-7777-8888', '11/2013'),
                               ('eve', '1234-5678-9012-3456', '05/2015');
INSERT 0 3
postgres=#   CREATE VIEW my_credit_card AS SELECT * FROM credit_card
WHERE cname = getpgusername();
CREATE VIEW
postgres=#   CREATE VIEW my_credit_card_sec WITH (security_barrier) AS
                 SELECT * FROM credit_card WHERE cname = getpgusername();
CREATE VIEW
postgres=# GRANT SELECT ON my_credit_card TO public;
GRANT
postgres=# GRANT SELECT ON my_credit_card_sec TO public;
GRANT
postgres=# SET SESSION AUTHORIZATION alice;
SET
postgres=> SELECT * FROM my_credit_card WHERE f_leak(cnumber,cexpired);
NOTICE:  1111-2222-3333-4444 => 07/2014
NOTICE:  5555-6666-7777-8888 => 11/2013
NOTICE:  1234-5678-9012-3456 => 05/2015
 cname |       cnumber       | cexpired
-------+---------------------+----------
 alice | 1111-2222-3333-4444 | 07/2014
(1 row)

postgres=> SELECT * FROM my_credit_card_sec WHERE f_leak(cnumber,cexpired);
NOTICE:  1111-2222-3333-4444 => 07/2014
 cname |       cnumber       | cexpired
-------+---------------------+----------
 alice | 1111-2222-3333-4444 | 07/2014
(1 row)

postgres=> EXPLAIN SELECT * FROM my_credit_card WHERE f_leak(cnumber,cexpired);
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on credit_card  (cost=0.00..21.20 rows=1 width=96)
   Filter: (f_leak(cnumber, cexpired) AND (cname = (getpgusername())::text))
(2 rows)

postgres=> EXPLAIN SELECT * FROM my_credit_card_sec WHERE
f_leak(cnumber,cexpired);
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on credit_card  (cost=0.00..21.20 rows=1 width=96)
   Filter: ((cname = (getpgusername())::text) AND f_leak(cnumber, cexpired))
(2 rows)

Thanks,

2011/7/3 Robert Haas <robertmhaas@gmail.com>:
> On Sat, Jul 2, 2011 at 3:46 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> On Sat, Jul 2, 2011 at 1:54 PM, Kohei KaiGai <kaigai@kaigai.gr.jp> wrote:
>>>> BTW, regarding to the statement support for security barrier views,
>>>> the following syntax might be more consistent with existing ones:
>>>>  CREATE VIEW view_name WITH ( param [=value]) AS query ... ;
>>>> rather than
>>>>  CREATE SECURITY VIEW view_name AS query ...;
>>>>
>>>> Any comments?
>>
>>> I think I mildly prefer CREATE SECURITY VIEW to the parameter syntax
>>> in this case, but I don't hate the other one.
>>
>> The WITH idea seems a bit more future-proof; in particular it would
>> easily accommodate specifying a security type, if we decide we need
>> various levels of leak-proof-ness.
>
> Or other kinds of view options.  I'm not going to argue against that
> too forcefully, since I've advocated introducing that sort of syntax
> elsewhere.  I think it's mostly that I thought this feature might be
> significant enough to merit a syntax that would make it a little more
> prominent, but perhaps not.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>



--
KaiGai Kohei <kaigai@kaigai.gr.jp>