Postgres Pro
Downloads
Home   >   mailing lists

Thread: [EUC_CN] Failed to connect through user created user/database usingsimplified Chinese characters

[EUC_CN] Failed to connect through user created user/database usingsimplified Chinese characters

From
Neha Sharma
Date:
Hi,
It was observed that when we try to connect through a user/database created using
simplified Chinese characters on EUC_CN server_encoding, it fails giving error that
the object does not exists.  Whereas if we query the system table we can find their entries
there.

Data setup:
.) set the locale to zh_CN.UTF8
[neha@localhost bin]$ locale
LANG=zh_CN.UTF-8
LC_CTYPE="zh_CN.UTF-8"
LC_NUMERIC=zh_CN.UTF-8
LC_TIME=zh_CN.UTF-8
LC_COLLATE="zh_CN.UTF-8"
LC_MONETARY=zh_CN.UTF-8
LC_MESSAGES="zh_CN.UTF-8"
LC_PAPER=zh_CN.UTF-8
LC_NAME="zh_CN.UTF-8"
LC_ADDRESS="zh_CN.UTF-8"
LC_TELEPHONE="zh_CN.UTF-8"
LC_MEASUREMENT=zh_CN.UTF-8
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=

.) Perform initdb
[neha@localhost bin]$ ./initdb -D /tmp/data --locale=zh_CN

Observation:
postgres=# show server_encoding ;
 server_encoding
-----------------
 EUC_CN
(1 行记录)

postgres=# show client_encoding ;
 client_encoding
-----------------
 UTF8
(1 行记录)

postgres=# create user 伐角 with password '规';
CREATE ROLE
postgres=# create database 谨角 ;
CREATE DATABASE
postgres=# alter role 伐角 with login ;
ALTER ROLE
postgres=# \c - 伐角
FATAL:  role "伐角" does not exist
Previous connection kept
postgres=# \c 谨角
FATAL:  database "谨角" does not exist
Previous connection kept
postgres=# select * from pg_user where usename='伐角';
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 伐角    |    16384 | f           | f        | f       | f            | ******** |          |
(1 row)
postgres=# select * from pg_database where datname='谨角';
 datname | datdba | encoding | datcollate | datctype | datistemplate | datallowconn | datconnlimit | datlastsysoid | datfrozenxid | datminmxid | dattablespace | datacl
---------+--------+----------+------------+----------+---------------+--------------+--------------+---------------+--------------+------------+---------------+--------
 谨角    |     10 |        2 | zh_CN      | zh_CN    | f             | t            |           -1 |         13286 |          561 |          1 |          1663 |
(1 row)


Just wondering, is there anything wrong I am doing or it's a bug?


Thanks.
--
Regards,
Neha Sharma

Re: [EUC_CN] Failed to connect through user created user/database using simplified Chinese characters

From
Tom Lane
Date:
Neha Sharma <neha.sharma@enterprisedb.com> writes:
> It was observed that when we try to connect through a user/database
> created using simplified Chinese characters on EUC_CN server_encoding,
> it fails giving error that the object does not exists.  Whereas if we
> query the system table we can find their entries there.

This looks like an encoding conversion problem.  The connection request
has to spell the user name bitwise identically to the way it is stored
in pg_authid; no conversions will be applied at that point.  You have

> postgres=# show server_encoding ;
>  server_encoding
> -----------------
>  EUC_CN
> (1 行记录)

> postgres=# show client_encoding ;
>  client_encoding
> -----------------
>  UTF8
> (1 行记录)

> postgres=# create user 伐角 with password '规';
> CREATE ROLE

so what this did, likely, is transmit the characters to the server
in UTF8, then the server converted them to EUC_CN, and stored them
in the catalogs in EUC_CN.  But here:

> postgres=# \c - 伐角
> FATAL:  role "伐角" does not exist
> Previous connection kept

those characters are again being sent to the server in UTF8,
and it has no context that would tell it to convert to EUC_CN.

This is all pretty messy, and I've not heard any fix proposals that
wouldn't amount to shifting the pain to somebody else's use-case.
The short answer, if you want to use non-ASCII characters in user names,
passwords, or database names, is to always run the server and the client
with the same encodings.

            regards, tom lane