Re: [v9.2] Fix leaky-view problem, part 1
От | Kohei KaiGai |
---|---|
Тема | Re: [v9.2] Fix leaky-view problem, part 1 |
Дата | |
Msg-id | CADyhKSUFiJfjgaNA10BgyD_xoEUqsdcTkgwBrxWAbNgNAyiCOw@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [v9.2] Fix leaky-view problem, part 1 (Robert Haas <robertmhaas@gmail.com>) |
Ответы |
Re: [v9.2] Fix leaky-view problem, part 1
|
Список | 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>
Вложения
В списке pgsql-hackers по дате отправления: