Re: proposal: session server side variables

On 11 January 2017 at 06:09, Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> wrote:

> I'm not so sure about Craig precise opinion, but I cannot talk in his name.
> I think that I understood that he points out that there exists a situation
> where the use case is okay despite an untransactional variable: if the
> containing transaction is warranted not to fail, and probably (provably?) a
> read-only transaction is enough for that. Okay, sure...

No.

I'm saying that if you do a series of checks, then set the variable
last, it does not matter if the xact somehow aborts after setting the
variable. You have already done all your checks and are satisfied that
the user has the appropriate access level.

This does not cover all use cases. It's not suitable if the checks
involve writes to the database since you can then have a situation
where the writes are lost but the variable setting retained.

However, it does cover some common and useful ones like looking up
external services, possibly expensive queries, etc, and setting a
variable to cache "yup, access approved".

> I made the assumption that PostgreSQL is about keeping data safe and secure,
> and that misleading features which do not comply with this goal should be
> kept out.

This is hyperbolic. Every system has rules and restrictions.
Appropriately documented, I don't see a problem.

Should we also remove sequences because they violate transactional
boundaries and users get confused by them? You won't mind that you can
only use synthetic keys completely serially, right?

Sometimes compromises are a thing.

Now, I have already said that I actually agree that transactional vars
are probably a good default and something we should have if we do
this. But they are not the One True And Only Way.

> I'm clearly wrong: some people are okay with a security feature proven not
> to work in some case, if it works for their particular (read-only) case.

Many normal things work only in some cases.

COMMIT can be indeterminate if you lose your connection after sending
COMMIT and before getting a reply. Did my transaction commit? Um, I
dunno. (Yes, I know we have 2PC). That's pretty fundmental...

So. I would like transactional variables and think you have made a
good case for them. If they prove impractical, I think variables with
access controls that are not transactional are also OK though less
useful, so long as they are clearly documented.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services