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)