Re: [HACKERS] proposal: session server side variables

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.

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.

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)