Empty OperationalError when I tried to connect with wrong credentials

[luisrojas17]

This is a bug tracker
If you have a question, such has "how do you do X with Python/PostgreSQL/psycopg2" please write to the mailing list or open a question instead.

Please complete the following information:

• OS: Windows 8
• Psycopg version: 2.9.3
• Python version: 3.10.4
• PostgreSQL version: PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit
• pip version: 22.0.4

Describe the bug
When I tried to connect to database with wrong credentials, I did not a descriptive message about the problem.
By the way, some time ago another user reported the same problem (Empty OperationalError on connect #417) but, until now there is not any solution (the issue was closed).

Please let us know:

1: what you did

• I defined two variables that wraps the credentials to connect to PostgreSQL database
• I defined a variable to receive the connection

   import psycopg2
   import os
   
   try:
       USER = os.environ['postgres']
       PASSWORD = os.environ['xyz1'] #Password is wrong
       conn = psycopg2.connect(host="localhost", port="5432", database="exampleDB", user=USER, password=PASSWORD)
       .....

   except psycopg2.OperationalError as err:
       print("Error: ", err)
       conn = None

   finally:
       if conn is not None:
           conn.close

2: what you expected to happen

• A descriptive message showing what happend

3: what happened instead

• I just got hardcode string 'Error:'

If possible, provide a script reproducing the issue.

[adikte]

I got exactly the same problem with the same code.

OS: Windows 10
Psycopg version: 2.9.1
Python version: 3.7
PostgreSQL version: PostgreSQL 14.2, compiled by Visual C++ build 1914, 64-bit
pip version: 22.0.4

I might have a few elements of comprehension. It might be related to the language of installation for your postgresql database.
I am using a french install , so in my postgresql.conf file i got a "lc_messages= 'French_France.1252'" which is related to the locale for system error message strings. And because of that all my error messages are in french, except for the OperationalError ones which are missing. If i change the variable lc_messages to 'en-US' the problem is solved. The OperationalError return the correct messages. But every error messages are in english.
So maybe the error message is not translated and doesn't exist so it can't be returned, or contain a non asci caracter and make something bug.

@luisrojas17 What language are you using for postgresql database ?

[dvarrazzo]

Sorry, I missed this message a few days ago.

Out of curiosity, could you try the same operations using psycopg 3? There is some extra care we take there with connection errors and I wonder if they would help in this case too. It is related more to encodings than to language though.

What are the encoding, and other parameters, of your database? You can check them with psql -l or with the \l command in psql interactive.

[adikte]

i tryed to install psycopg 3 but got :

ImportError: no pq wrapper available.
Attempts made:

• couldn't import psycopg 'c' implementation: No module named 'psycopg_c'
• couldn't import psycopg 'binary' implementation: No module named 'psycopg_binary'
• couldn't import psycopg 'python' implementation: libpq library not found

the other parameter of my database are :

 Nom     | PropriÚtaire | Encodage |  Collationnement   |    Type caract.    |    Droits d'accÞs

-------------+--------------+----------+--------------------+--------------------+-----------------------
mailinglist | postgres | UTF8 | French_France.1252 | French_France.1252 |
postgres | postgres | UTF8 | French_France.1252 | French_France.1252 |
template0 | postgres | UTF8 | French_France.1252 | French_France.1252 | =c/postgres +postgres=CTc/postgres
template1 | postgres | UTF8 | French_France.1252 | French_France.1252 | =c/postgres +postgres=CTc/postgres
test | postgres | UTF8 | French_France.1252 | French_France.1252 |

[dvarrazzo]

@adikte try pip install psycopg[binary]

Thank you for testing!

[adikte]

I followed your advice and installed psycopg 3 with the pip install psycopg[binary]. It does work properly which is great, thanks.
I run a few test to see how psycopg 3 behave, the good news is i got an error message in french, but their is still an encoding problem. I also noted that it is the same error message for different kind of connection problem (wrong user name, wrong password and expired validity date for password ).

If i set in my postgresql.conf file the lc_messages variable to 'French_France.1252'" i get :
connection failed: :1), port 5432 failed: FATAL: authentification par mot de passe �chou�e pour l'utilisateur � bob �

And if set in my postgresql.conf file the lc_messages variable to 'en.US'" i get :
connection failed: :1), port 5432 failed: FATAL: password authentication failed for user "bob"

@dvarrazzo Any advice on how to correct those problems ?

[dvarrazzo]

@adikte ISTM that you have inconsistent encodings. French_France.1252 is a single-byte encoding, UTF8 is multi-byte. I can see that psql is confused too: I don't know French but I assume that PropriÚtaire is not the correct spelling :D

Connection errors happen before the server had the chance to communicate the client encoding. In psycopg 3.0.8, we use the client encoding found in the connection string, if it is specified (psycopg/psycopg#194), otherwise we fall back on utf8. So, probably, if you add client_encoding=WIN1252 to the connection string, you might get the mot de passe message right. However, all the data must be compatible with the Windows 1252 codepage: if you have any string not compatible in the database (let's say, a Japanese character or an emoji), you will get an error on insert or on select ☠️. If that's acceptable, in 2022, is up to you. Note that psycopg decodes human messages with a "replace" policy, hence the � in the messages, but it is strict on data and that would result in an error. You might connect in WIN1252 and switch to UTF8 afterwards with a SET client_encoding but meh.

I think the best solution is to create a database with some French+utf8 messages. I don't know how to do it on Windows.

[adikte]

@dvarrazzo Thank you for quick answer, it is much appreciated. That's great, adding client_encoding=WIN1252 to the connection string work like a charm for psycopg3.
I tried the same thing with psycopg 2.9.3 but the error message is still empty.
I think i will start using psycopg3 instead of 2. Their is one thing bothering me, error.diag.sqlstate return None is it because the error is generated client side ?

[dvarrazzo]

@adikte I am aware of the problem in psycopg2: will try to address it.

Yes, the lack of sqlstate is caused by the error being generated by the libpq rather than the server.