Re: proposal: session server side variables

2016-12-28 19:17 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 12/28/16 11:29 AM, Fabien COELHO wrote:
>
>>
>> Hello Jim,
>>
>> 1) Variables would be completely non-transactional. [...] A solution
>>> to this problem would be to provide a plpgsql equivalent to plperl or
>>> plpython's session hashes.
>>>
>>
>> That is what I have in mind with "session variables" à la MS/MY SQL, but
>> at the SQL level, not PL/pgSQL level.
>>
>
> I'm just saying there might be use for a plpgsql equivalent to the session
> hashes that other PLs provide, but that's a different issue.
>
> users are forced to create accessor functions,
>>>
>>
>> Yes if the private variable should be accessed. If the variable is
>> private, then it is private and nothing is needed. Idem for public.
>>
>
> Why force the extra busywork? Just allow for them to be public.
>
> For that matter, if we're going to start introducing private objects, that
> certainly needs to be thought through.
>
> and you run a serious risk of confusion from getting the function
>>> ownerships wrong.
>>>
>>
>> One can get the permissions on special session variable wrong as well...
>> I do not see how it differs.
>>
>
> It's a lot harder to mess up an explicit grant than it is to mess up
> object ownership.
>
> More importantly, the security definer trick you're suggesting has a
>>> fatal flaw: you can't call one SECDEF function from another SECDEF
>>> function.
>>>
>>
>> I do not see why there would be such a restriction?
>>
>> postgres(at)db> CREATE FUNCTION secfunc() RETURNS TEXT SECURITY DEFINER
>> AS $$ SELECT CURRENT_USER::TEXT; $$ LANGUAGE SQL;
>>
>> fabien(at)db> CREATE FUNCTION secfunc2() RETURNS TEXT SECURITY DEFINER
>> AS $$ SELECT secfunc() || ' - ' || CURRENT_USER; $$ LANGUAGE
>> SQL;
>>
>> *(at)db> SELECT secfunc2(); -- returns: "postgres - fabien" from both
>> sides...
>>
>
> Perhaps I've got the restrictions on SECDEF wrong, but I know there's
> problems with it. Certainly one issue is you can't change roles back to the
> calling user.
>
> Maybe they would be workable in this case, but it's just a bunch of extra
> busywork for the user that serves no real purpose.
>
> We should protect for the possibility of truly global (as in
>>> cross-session) variables.
>>>
>>
>> Yes, why not... Although having "cross-session session variable" seems
>> to create some problems of its own... Would they be cross-database as
>> well?
>>
>
> Yes. It'd be a shared catalog.
>
> ...
>
> Yes, you could simulate the same thing with functions, but why make
>>> users do all that work if we could easily provide the same functionality?
>>>
>>
>> The easy is unclear. Eg if special declared with permissions partially
>> persistent session variables preclude future basic session variables, or
>> their efficiency, or their syntax, it would be a problem. Hence the
>> point of discussing before proceeding.
>>
>
> Then IMHO what needs to happen is to have a discussion on actual syntax
> instead of calling into question the value of the feature. Following this
> thread has been very painful because the communications have not been very
> clear. Focus on grammar would probably be a big improvement in that regard.

I don't think. There are some significant questions:

1. Should be "variables" fixed in schema? Should be metadata persistent?
2. Should we use GRANT/REVOKE statements for "variables"?
3. Should be "variable" name unique in schema like tables, indexes,
sequences?
4. The content of "variables" should be nontransactional or transactional.
5. What mode we should to support, what mode will be default "unshared",
"shared"

That is all. All discussion is about these questions. These questions
creates multidimensional space, that can be covered. But we cannot to
implement all in one stage.

We can have schema unbound variables declared by

DECLARE @xx AS int, and accessed with get('@xx') and set('@xx', val)

and we have to have bound variables created by

CREATE VARIABLE public.xx AS int and accessed with get('public.xx'),
set('public.xx', val)

We can have both - I don't see any problem. Implementation DECLARE
statement doesn't eliminate implementation CREATE statement. I can
understand so somebody doesn't need secure variables - so it doesn't need
CREATE statement. But if you need secure variables then some PRIVATE flags
is minimally redundant to our standard security design.

My proposal doesn't eliminate Fabien's proposal - proposal by Fabien can be
good enough for Fabien - and for interactive work, but is not good enough
for some checking and security usage.

Regards

Pavel

>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
> 855-TREBLE2 (855-873-2532)
>