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

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

From
"Zoltan Kovacs"
Date:
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

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

From
Kovács Zoltán
Date:
Dear Tom, thank you for the explanation.

2010/2/24 Tom Lane <tgl@sss.pgh.pa.us>
"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.

Yes, this is the case for me.

What can happen if I use an unsafe combination? Can the backend unexpectedly die? Can this cause data corruption?

Thank you for your answer in advance.

Best regards, Zoltan
 

                       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