Re: [HACKERS] [pgsql-hackers] Daily digest v1.10705 (13 messages)
On Thu, 2010-06-03 at 05:53 -0300, pgsql-hackers-ow...@postgresql.org wrote: [ . . . ] In my current idea, when a qual-node that contains FuncExpr tries to reference a part of relations within a security view, its referencing relations will be expanded to whole of the security view at distribute_qual_to_rels(). [ . . . ] I may be missing something here but this seems a bit too simplistic and, I think, fails to deal with an important use case. Security views, that do anything useful at all, tend to introduce performance issues. I am concerned that placing a conceptual barrier between the secured and unsecured parts of queries is going to unnecessarily restrict what the optimiser can do. For example consider that we have three secured views, each of the form: create view s_x as select * from x where i_can_see(x.key); and consider the query: select stuff from s_x inner join s_y on s_y.key = s_x.key inner join s_z on s_z.key = s_x.key where fn(s_x.a) = 3; The optimiser ought to be able to spot the fact that i_can_see() need only be called once for each joined result. By placing a barrier (if I understand your proposal correctly) between the outermost joins and the inner views, doesn't this optimisation become impossible? I think a simpler solution may be possible here. If you can tag the function i_can_see() as a security function, at least in the context of its use in the security views, and then create the rule that security functions are always considered to be lower cost than user-defined non-security functions, don't we achieve the result of preventing the insecure function from seeing rows that it shouldn't? I guess my concern is that a query may be constructed a=out of secured and unsecured parts and the optimiser should be free to group all of the secured parts together before considering the unsecured parts. Sorry for the imprecise language and terminolgy, and also if I have completely misunderstood the implications. Best Wishes __ Marc (the veil guy) signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [pgsql-hackers] Daily digest v1.10705 (13 messages)
On Thu, Jun 3, 2010 at 1:23 PM, Marc Munro m...@bloodnok.com wrote: On Thu, 2010-06-03 at 05:53 -0300, pgsql-hackers-ow...@postgresql.org wrote: [ . . . ] In my current idea, when a qual-node that contains FuncExpr tries to reference a part of relations within a security view, its referencing relations will be expanded to whole of the security view at distribute_qual_to_rels(). [ . . . ] I may be missing something here but this seems a bit too simplistic and, I think, fails to deal with an important use case. If anything, you're putting it mildly. This is quite a bit too simplistic and fails to deal with several important issues, at least some of which have already been mentioned on this thread. The optimiser ought to be able to spot the fact that i_can_see() need only be called once for each joined result. By placing a barrier (if I understand your proposal correctly) between the outermost joins and the inner views, doesn't this optimisation become impossible? I think a simpler solution may be possible here. If you can tag the function i_can_see() as a security function, at least in the context of its use in the security views, and then create the rule that security functions are always considered to be lower cost than user-defined non-security functions, don't we achieve the result of preventing the insecure function from seeing rows that it shouldn't? So, yes and no. You DO need a security barrier between the view and the rest of the query, but if a function can be trusted not to do evil things, then it should be allowed to be pushed down. What we need to prevent is the pushdown of untrusted functions (or operators). A (very) important part of this problem is determining which quals are safe to push down. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
