Re: [HACKERS] proposal: schema variables

2018-08-22 9:00 GMT+02:00 Fabien COELHO <coelho@cri.ensmp.fr>:

>
> Hello Pavel,
>
> AFAICR, I had an objection on such new objects when you first proposed
>>> something similar in October 2016.
>>>
>>> Namely, if session variables are not transactional, they cannot be used
>>> to
>>> implement security related auditing features which were advertised as the
>>> motivating use case: an the audit check may fail on a commit because of a
>>> differed constraint, but the variable would keep its "okay" value unduly,
>>> which would create a latent security issue, the audit check having failed
>>> but the variable saying the opposite.
>>>
>>> So my point was that they should be transactional by default, although I
>>> would be ok with an option for having a voluntary non transactional
>>> version.
>>>
>>> Is this issue addressed somehow with this ?
>>>
>>
>>
>> 1. I respect your opinion, but I dont agree with it. Oracle, db2 has
>> similar or very similar feature non transactional, and I didnt find any
>> requests to change it.
>>
>
> The argument of authority that "X does it like that" is not a valid answer
> to my technical objection about security implications of this feature.
>
> 2. the prototype implementation was based on relclass items, and some
>> transactional behave was possible. Peter E. had objections to this design
>> and proposed own catalog table. I did it. Now, the transactional behave is
>> harder to implement, although it is not impossible. This patch is not
>> small
>> now, so I didnt implement it.
>>
>
> "It is harder to implement" does not look like a valid answer either.
>
> I have a strong opinion so default behave have to be non transactional.
>>
>
> The fact that you have a "strong opinion" does not really answer my
> objection. Moreover, I said that I would be ok with a non transactional
> option, provided that a default transactional is available.
>
> Transactional variables significantly increases complexity of this patch,
>> now is simple, because we can reset variable on drop variable command.
>> Maybe I miss some simply implementation, but I spent on it more than few
>> days. Still, any cooperation are welcome.
>>
>
> "It is simpler to implement this way" is not an answer either, especially
> as you said that it could have been on point 2.
>
>
> As I do not see any clear answer to my objection about security
> implications, I understand that it is not addressed by this patch.
>
>
> At the bare minimum, if this feature ever made it as is, I think that a
> clear caveat must be included in the documentation about not using it for
> any security-related purpose.
>
> Also, I'm not really sure how useful such a non-transactional object can
> be for other purposes: the user should take into account that the
> transaction may fail and the value of the session variable be inconsistent
> as a result. Sometimes it may not matter, but if it matters there is no
> easy way around the fact.
>

I agree, so it should be well documented to be clear, what is possible,
what not, and to be correct expectations.

This feature has two (three) purposes

1. global variables for PL language
2. holding some session based informations, that can be used in security
definer functions.
3. Because it is not transactional, then it allows write operation on read
only hot stand by instances.

It is not transactional safe, but it is secure in sense a possibility to
set a access rights. I understand, so some patterns are not possible, but
when you need hold some keys per session, then this simply solution can be
good enough. The variables are clean after session end.

I think it is possible for some more complex patterns, but then developer
should be smarter, and should to enocode state result to content of
variable. There is strong benefit - read write access to variables is very
cheap and fast.

I invite any patch to doc (or everywhere) with explanation and about
possible risks.

Regards

Pavel





> --
> Fabien.
>