Enforcing database encoding and locale match

I was reminded again just now of the bad consequences of selecting a
database encoding that is not compatible with your LC_CTYPE setting:
http://archives.postgresql.org/pgsql-bugs/2007-09/msg00158.php
Aside from that one, which is perilously close to being a denial of
service attack, there are known problems with sorting, upper()/lower()
behavior, etc etc. We're going to keep hearing those types of
complaints until we do something about enforcing that people don't use
an incompatible encoding.

This has been discussed before, of course, and has foundered on the
problem that there's no very reliable/portable way to determine what
encoding is implied by LC_CTYPE. We do have code in initdb that
purports to determine this on common platforms, but I've never trusted
it very much, because it isn't stressed hard in common use. So the
problem is how to develop some trust in it.

It occurs me that what we could do is put that code into CREATE
DATABASE, but have it throw a WARNING not an ERROR if it thinks the
encoding doesn't match the locale. That would be sufficiently in
people's faces that we'd hear about it if it didn't work. After a
release cycle or so of not hearing complaints, we could promote the
warning to an error.

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.

Comments?

regards, tom lane