回复：回复：A question about leakproof

[qiumingcheng]

> you seem to be imagining that changes in a query's plan on the basis of 
> changes in collected statistics have something to do with this. They do not.
Sorry, I may not fully understand what you mean. I mean that after my tests, 
the execution results of this SQL (explain select * from tb_a_date_v1) 
execution plan are different under different users, which is really related to 
the parameter proleakproof.
If the 2 below is changed to 'return true' , the execution plan will be 
indexscan
--
发件人：Tom Lane 
发送时间：2022年10月17日(星期一) 11:33
收件人：qiumingcheng 
抄　送：Julien Rouhaud ; pgsql-general 

主　题：Re: 回复：A question about leakproof
"qiumingcheng"  writes:
> 1. In the test example I gave, the in4eq function's proleakproof=true, but 
> its actual test result is leaking. Does that mean you will adjust it to 
> proleakproof=false later?
int4eq is about as leakproof as a function could possibly be: it does
not leak, it's plain from the code of the function that it does not
leak, and it calls no other code that might accidentally introduce
a leak in future.
I think you do not understand what that property actually means.
Per the CREATE FUNCTION man page:
 LEAKPROOF indicates that the function has no side
 effects. It reveals no information about its arguments other than by
 its return value. For example, a function which throws an error message
 for some argument values but not others, or which includes the argument
 values in any error message, is not leakproof.
Please note that this definition talks only about the behavior
of the function itself. Re-reading your email, you seem to be
imagining that changes in a query's plan on the basis of changes in
collected statistics have something to do with this. They do not.
 regards, tom lane


temp4cj.png
Description: Binary data

Re: 回复：A question about leakproof

[David G. Johnston]

On Sun, Oct 16, 2022 at 8:33 PM Tom Lane  wrote:

> "qiumingcheng"  writes:
> > 1. In the test example I gave, the in4eq function's proleakproof=true,
> but its actual test result is leaking. Does that mean you will adjust it to
> proleakproof=false later?
>
> int4eq is about as leakproof as a function could possibly be: it does
> not leak, it's plain from the code of the function that it does not
> leak, and it calls no other code that might accidentally introduce
> a leak in future.
>
> I think you do not understand what that property actually means.
> Per the CREATE FUNCTION man page:
>
>LEAKPROOF indicates that the function has no side
>effects.  It reveals no information about its arguments other than
> by
>its return value.  For example, a function which throws an error
> message
>for some argument values but not others, or which includes the
> argument
>values in any error message, is not leakproof.
>
> Please note that this definition talks only about the behavior
> of the function itself.  Re-reading your email, you seem to be
> imagining that changes in a query's plan on the basis of changes in
> collected statistics have something to do with this.  They do not.
>
>
You omitted including the part about when the system even has to care about
leakproof:

"This affects how the system executes queries against views created with
the security_barrier option or tables with row level security enabled."

A non-leakproof function must not be called with inputs that the current
user is not allowed to see.  Allowed being the operative word, if they
could see the value if not for other non-security-related conditions in the
query it is acceptable to call the function with those inputs even if the
end result is simply going to be thrown away later (while inefficient, this
is why function cost is tunable).  None of your examples prohibit any row
from being processed by any function and so your spy function may see any
and all rows present in the table.

A leakproof function is allowed to process data that the current user is
not allowed to see - because if the input row ends up being filtered out
the values of the input arguments will never be viewable by the user.  They
will neither be in the query output, nor able to be read or inferred by
some kind of side-effect.  Your spy function, which is not leakproof,
should never see such prohibited rows - which you seem to have confirmed.
The planner indeed must ensure other security-related filters are applied
first.

David J.

Re: 回复：A question about leakproof

[Tom Lane]

"qiumingcheng"  writes:
> 1. In the test example I gave, the in4eq function's proleakproof=true, but 
> its actual test result is leaking. Does that mean you will adjust it to 
> proleakproof=false later?

int4eq is about as leakproof as a function could possibly be: it does
not leak, it's plain from the code of the function that it does not
leak, and it calls no other code that might accidentally introduce
a leak in future.

I think you do not understand what that property actually means.
Per the CREATE FUNCTION man page:

   LEAKPROOF indicates that the function has no side
   effects.  It reveals no information about its arguments other than by
   its return value.  For example, a function which throws an error message
   for some argument values but not others, or which includes the argument
   values in any error message, is not leakproof.

Please note that this definition talks only about the behavior
of the function itself.  Re-reading your email, you seem to be
imagining that changes in a query's plan on the basis of changes in
collected statistics have something to do with this.  They do not.

regards, tom lane

回复：A question about leakproof

[qiumingcheng]

> Have you looked at
> https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2#Security_barriers_and_Leakproof
>  
>   >?
Yes, if I use securtiy_barrierys, it do work, but it still can't use index, I 
guess it may cause performance problems, right ?
>Also: the fact that a built-in function is not marked leakproof
>doesn't mean that it isn't leakproof. It could just mean that
>we haven't looked at it closely, or that there's too much code
>involved to have much confidence that it would stay leakproof.
1. In the test example I gave, the in4eq function's proleakproof=true, but its 
actual test result is leaking. Does that mean you will adjust it to 
proleakproof=false later?
2. What basis do you set proleakproof of in4eq function to true? How should I 
judge whether a function should be marked as proleakproof.Can you give a 
function that will not leak?
--
发件人：Tom Lane 
发送时间：2022年10月17日(星期一) 09:54
收件人：Julien Rouhaud 
抄　送：qiumingcheng ; pgsql-general 

主　题：Re: A question about leakproof
Julien Rouhaud  writes:
> On Mon, Oct 17, 2022 at 09:15:20AM +0800, qiumingcheng wrote:
>> After testing, we don't find the difference between functions of
>> proleakproof=true and functions of proleakproof=false (the function is
>> described in pg_proc).
> Have you looked at
> https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2#Security_barriers_and_Leakproof
>  
>   >?
Also: the fact that a built-in function is not marked leakproof
doesn't mean that it isn't leakproof. It could just mean that
we haven't looked at it closely, or that there's too much code
involved to have much confidence that it would stay leakproof.
 regards, tom lane

Re: A question about leakproof

[Tom Lane]

Julien Rouhaud  writes:
> On Mon, Oct 17, 2022 at 09:15:20AM +0800, qiumingcheng wrote:
>> After testing, we don't find the difference between functions of
>> proleakproof=true and functions of proleakproof=false (the function is
>> described in pg_proc).

> Have you looked at
> https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2#Security_barriers_and_Leakproof?

Also: the fact that a built-in function is not marked leakproof
doesn't mean that it isn't leakproof.  It could just mean that
we haven't looked at it closely, or that there's too much code
involved to have much confidence that it would stay leakproof.

regards, tom lane

Re: A question about leakproof

[Julien Rouhaud]

Hi,

On Mon, Oct 17, 2022 at 09:15:20AM +0800, qiumingcheng wrote:
> Hello， My questions are as follows：
> Problem description
> After testing, we don't find the difference between functions of
> proleakproof=true and functions of proleakproof=false (the function is
> described in pg_proc). Can you give specific examples to show that functions
> of proleakproof=true are more secure or can prevent data disclosure than
> functions of proleakproof=false. My related testing process is as follows
> (the rsp_user and wumk used below are the two database users that have been
> created).

Have you looked at
https://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.2#Security_barriers_and_Leakproof?