Re: 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)