Re: Enforcing database encoding and locale match

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Gregory Stark wrote:
>> "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>> Another possibility is to treat the case as a WARNING if you're
>>> superuser and an ERROR if you're not. This would satisfy people
>>> who are uncomfortable with the idea that CREATEDB privilege comes
>>> with a built-in denial-of-service attack, while still leaving a
>>> loophole for anyone for whom the test didn't work properly.
>>
>> That sounds like a good combination
> +1

After further experimentation I want to change the proposal a bit.
AFAICS, if we recognize the nl_langinfo(CODESET) result, there is
no reason not to trust the answer, so we might as well throw an
error always. The case that is problematic is where we can get a
CODESET string but we don't recognize it. In this case it seems
appropriate to do

ereport(WARNING,
(errmsg("could not determine encoding for locale \"%s\": codeset is \"%s\"",
ctype, sys),
errdetail("Please report this to <pgsql-bugs(at)postgresql(dot)org>.")));

and then let the user do what he wants.

There need to be two exceptions to the error-on-mismatch policy.

First off, if the locale is C/POSIX then we can allow any encoding.

Second, it appears that we have to allow SQL_ASCII encoding to be
selected regardless of locale; if we don't, the "make installcheck"
regression tests fail, because they try to do exactly that; and I'm
sure that there are other users out there who don't (think they)
care about encoding. This is not quite as bad as the generic mismatch
case, because the backend will never try to do encoding conversion
and so the recursive-error panic can't happen. But you could still
have unexpected sorting behavior and probably index corruption.

What I propose is that we allow SQL_ASCII databases to be created
when the locale is not C, but only by superusers.

Comments?

regards, tom lane