[Windows] Error message from pgsql server is not decoded correctly when the PGconn status is ConnStatus.BAD

[lyq1996]

First of all, sorry for my poor english..

I am writing a library for pgsql to crack the password of a database user from a password hash, and I use psycopg3 to verify that the password is correct. I found that when the connection is not established and the encoding is not utf-8, psycopg3 does not seem to correctly decode the error message from the server.

My pgsql server runs on windows 10, and the server version is 13.3 and encoding GBK(CP936) for messages displayed.
postgresql.conf:

# These settings are initialized by initdb, but they can be changed.
lc_messages = 'Chinese (Simplified)_China.936'			# locale for system error message
					# strings
lc_monetary = 'Chinese (Simplified)_China.936'			# locale for monetary formatting
lc_numeric = 'Chinese (Simplified)_China.936'			# locale for number formatting
lc_time = 'Chinese (Simplified)_China.936'				# locale for time formatting

For example, If the user's password was wrong or datebase for login doesn't existed, the obj.status will be ConnStatus.BAD(https://github.com/psycopg/psycopg/blob/master/psycopg/psycopg/pq/misc.py#L64), and then the msg will decoded as utf-8 by default and cause an error, then the part of msg which can not be decoded will be replaced by '\ufffd'.

Here is the exmaple code:

# coding=utf-8
import time
import psycopg

try:
    conn = psycopg.connect("user=user1 password='123456' host='127.0.0.1' port='5432' client_encoding='GBK'")
except psycopg.Error as ex:
    print(str(ex))

The error message should be "connection failed: 致命错误: 数据库 "user1" 不存在" which meaning "FATAL: database "user1" does not exist\n".

When the connection established, the error message can be decoded correctly according to the given client_encoding:

# coding=utf-8
import time
import psycopg

try:
    conn = psycopg.connect("user=postgres password='123456' host='127.0.0.1' port='5432' client_encoding='GBK'")
    conn.execute("""
                select * from qwertsdnmsdjffks;)
                """)
    conn.commit()
except psycopg.Error as ex:
    print(str(ex))

Hope you can confirm and fix, thanks a lot.

[dvarrazzo]

I assume that, when that error happens on connection, we don't know the server encoding yet.

I assume that you assume that psycopg would use the encoding specified in the connection string. It doesn't: that encoding is passed to the server and then psycopg reads it back from the connection, but that doesn't happen when the connection is returned bad. I can see if it is not too difficult to add it.

What happens if you don't specify the encoding in the connection string?

IMO, sending an error message without exploding is a feat already. Error reporting is the only place in the library with an error=replace policy: I'm glad it works...

[lyq1996]

I assume that, when that error happens on connection, we don't know the server encoding yet.

I assume that you assume that psycopg would use the encoding specified in the connection string. It doesn't: that encoding is passed to the server and then psycopg reads it back from the connection, but that doesn't happen when the connection is returned bad. I can see if it is not too difficult to add it.

What happens if you don't specify the encoding in the connection string?

IMO, sending an error message without exploding is a feat already. Error reporting is the only place in the library with an error=replace policy: I'm glad it works...

Yes, at least error replace works. I also tried psycopg2, the error message is empty and pgcode, pgerror is None if connection was bad.

If I don't specify the encoding in the connection string, the error message from server still garbled when connection bad, and the error message after the connection is successful also becomes garbled.

I hate GBK :(

[dvarrazzo]

I hate GBK :(

Why is not the database in utf8 encoding?

If I don't specify the encoding in the connection string, the error message from server still garbled when connection bad, and the error message after the connection is successful also becomes garbled.

That's interesting, it's not what I would have expected.

Would you be able to create a docker image with a server configured to reproduce the issue? That would make simpler to reproduce and test the problem.

[lyq1996]

Because the early Win10 uses GBK as the default Chinese encoding, I installed pgsql-server from the official installer without any changes. It uses GBK by default to display error messages (postgresql lc_messages field). If I change lc_messages to utf-8, the problem gone..

Would you be able to create a docker image with a server configured to reproduce the issue? That would make simpler to reproduce and test the problem.

I try to built a docker based on postgres:14.1-bullseye, then I set lc_messages to zh_CN.gbk and locale to zh_CN.utf-8, but I found that no matter specified client_encoding to gbk or empty, there is no decode problem. When the connection bad, the error message is always encoded by utf-8. When the connection ok, the error message is encoded by client_encoding arguments.

I also try to build pgsql docker with windows image, but it is too complex, so I gave up...

I guess the cause of problem is that before connection is established, the server will not accept client_encoding argument and reply error message encoded by the vaule of locale environment? or lc_messages from postgresql.conf?

[dvarrazzo]

I was able to reproduce the issue. I should be able to fix the problem. Yes, probably when the error is on connection the server replies in the encoding specified in lc_messages. We can check if there is an encoding in the connection string and try to decode the error using it.

[dvarrazzo]

Fix will be released in psycopg 3.0.8. and only if the client_encoding is specified in the connection string (otherwise we have no information at all about the encoding of the error messages).

I have debugged the issue on Linux and using the EUC-JP encoding: please test with the maint-3.0 or the master branch to verify that the issue is solved for you too on Windows/BGK. Thank you!

[lyq1996]

Fix will be released in psycopg 3.0.8. and only if the client_encoding is specified in the connection string (otherwise we have no information at all about the encoding of the error messages).

I have debugged the issue on Linux and using the EUC-JP encoding: please test with the maint-3.0 or the master branch to verify that the issue is solved for you too on Windows/BGK. Thank you!

I tested Windows/GBK with psycopg(3.1.dev0) and it works, thank you!