2016-12-28 17:53 GMT+01:00 Jim Nasby <jim.na...@bluetreble.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) >
