Re: proposal: session server side variables

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

> On 12/28/16 9:57 AM, Fabien COELHO wrote:
>
>> * Other later triggers, etc, also reference USER_IS_AUDITOR
>>>
>>
>> The variable is not directly referenced, one would have to call
>> isUserAuditor() to access the private session value, but then you can
>> GRANT/REVOKE whatever you want on the access function.
>>
>
> Why force users to create Yet Another Function as a getter?
>
> There's 2 big points that I think keep getting missed:
>
> 1) Variables would be completely non-transactional. The only way you can
> do that today is to use a "non-standard" language (such as plperl or
> plpython), or by creating a custom GUC (which is ugly because it
> necessitates changing postgresql.conf and is only text). A solution to this
> problem would be to provide a plpgsql equivalent to plperl or plpython's
> session hashes. I'm sure there are use cases that would be satisfied by
> simple doing that, but...
>
> 2) Variables provide permissions. Theoretically you could allow the
> hypothetical plpgsql session variables in (1) to be marked private, but
> that means you now have to keep all those variables on a per-role basis,
> users are forced to create accessor functions, and you run a serious risk
> of confusion from getting the function ownerships wrong. That certainly
> seems no better than defining permanent variables and giving them
> permissions (as Pavel suggested). More importantly, the security definer
> trick you're suggesting has a fatal flaw: you can't call one SECDEF
> function from another SECDEF function. So as soon as you have multiple
> privileged roles making use of variables, there's a serious risk of not
> being able to make use of these private variables at all.
>
> Now maybe pg_class is absolutely the wrong place to store info about
> predefined variables, but that's an implementation detail, not a design
> flaw.
>

We can talk about implementation - for me a variable is a object that holds
data - more, I would to fix this object in schema without possible
collision with tables. I plan to support SELECT for access and UPDATE for
changes in future - so using pg_class is natural. On second hand - lot of
fields in pg_class are not used for variables everywhere. I would to fix
variables in some schema, but I would not to solve possible collision
between variables and other SQL objects - due possible direct access inside
SQL statements in future.

>
> Some other points:
> We should protect for the possibility of truly global (as in
> cross-session) variables. Presumably these would have to be pre-defined via
> DDL before use. These would be uniquely valuable as a means of
> communication between sessions that are connected to different databases. I
> could also see use in cross-database in-memory queues. AFAIK both of these
> would be pretty easy to do with the shared memory infrastructure we now
> have.
>

I didn't write any what close this possibility.

>
> It would be nice if we could come up with a plan for what permanently
> defined temp tables looked like, so the syntax and operation was similar to
> the permanently defined session variables that Pavel is proposing. That
> said, given how long that has been an open issue I think it's completely
> unfair to stonewall this feature if we can't get permanent temp tables
> figured out.
>
> While permanent temp tables would eliminate some objections to store
> "session variables", the fact still remains that any kind of table would
> still be MVCC, and that is NOT always what you want.
>
> It would be nice if whatever syntax was decided for defined session
> variables allowed room for "variables" that were actually MVCC, because
> sometimes that actually is what you want. 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? These should probably be called
> something other than "variables", but presumably all the other syntax and
> settings could be the same. Again, it's not the job of this proposal to
> boil that ocean, but it would be nice to leave the option open.
>
> --
> 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)
>