Thread: BUG #5345: non-administrator users cannot create databases with special encoding

The following bug has been logged online:

Bug reference:      5345
Logged by:          Zoltan Kovacs
Email address:      kovzol@particio.com
PostgreSQL version: 8.3.9-0lenny1
Operating system:   Debian Linux 5.0.4
Description:        non-administrator users cannot create databases with
special encoding
Details:

I would like to create a database using SQL_ASCII encoding, but it is not
allowed for a normal user, only for the postgres user.

Details (sorry, it is partially in Hungarian):

postgres=# create user wmi2user with password 'wmi2pass' createdb
nocreateuser;
CREATE ROLE
...
postgres@wmi:~$ psql -l
        Adatbázisok listája
    Név    | Tulajdonos |  Kódolás
-----------+------------+-----------
 postgres  | postgres   | UTF8
 template0 | postgres   | UTF8
 template1 | postgres   | UTF8
 wmi       | postgres   | SQL_ASCII
(4 rows)

postgres@wmi:~$ createdb -E SQL_ASCII -U wmi2user wmi2db
createdb: database creation failed: ERROR:  encoding SQL_ASCII does not
match server's locale hu_HU.UTF-8
DETAIL:  The server's LC_CTYPE setting requires encoding UTF8.

postgres@wmi:~$ createdb -E SQL_ASCII wmi2db

The last command line was successful.

Summarizing: The operation is allowed for the administrator, but not for a
normal user. But I would like to create this database by a normal user.

"Zoltan Kovacs" <kovzol@particio.com> writes:
> I would like to create a database using SQL_ASCII encoding, but it is not
> allowed for a normal user, only for the postgres user.

There is an exception that will allow superusers to do that, but it's
not a good idea for them either.  The problem is not the encoding per
se, it's that it's unsafe in combination with a locale setting that
assumes UTF8.  The only safe locale for SQL_ASCII is C.

If you want to have one database that is C/SQL_ASCII and others that
have different locale settings, you need to upgrade to PG 8.4.  8.3
can only have one locale across the entire installation.

            regards, tom lane

Kovács Zoltán <kovzol@particio.com> writes:
> What can happen if I use an unsafe combination? Can the backend unexpectedly
> die? Can this cause data corruption?

Inconsistent ordering results and index corruption can be expected at
minimum.  I'm not sure about actual crashes.  Try looking through
the pgsql-bugs archives for related problem reports from PG versions
that didn't have the locale compatibility checks.
        regards, tom lane