Re: [HACKERS] proposal: session server side variables

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@db> CREATE FUNCTION secfunc() RETURNS TEXT SECURITY DEFINER
>               AS $$ SELECT CURRENT_USER::TEXT; $$ LANGUAGE SQL;
>
>  fabien@db> CREATE FUNCTION secfunc2() RETURNS TEXT SECURITY DEFINER
>             AS $$ SELECT secfunc() || ' - ' || CURRENT_USER; $$ LANGUAGE
> SQL;
>
>  *@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.
-- 
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)