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)