16

I'm using Macbook

Psycopg2 works well when connecting the localhost db (PostgreSQL on Mac). The error was raised when I tried to connect PostgreSQL db on a Windows10.

the following code is what I have for connection, the host is just the IP of the windows10 

db= psycopg2.connect(database='dbname',user='username',password="secret",host="192.168.3.9",port="5432")

Errors: 

 File "path/to/psycopg2/__init__.py", line 126, in connect conn = _connect(dsn, connection_factory=connection_factory, **kwasync) psycopg2.OperationalError: FATAL: unsupported frontend protocol 1234.5679: server supports 2.0 to 3.0

Is this because of system compatibility or something else? I've tried other Windows machine and I got no luck to make it work. However, I was able to connect PostgreSQL on windows while I using Node.JS module pg

Improve this question

6 Answers 6

Reset to default
24

1234.5679 is the special code sent by the client to request SSL-encrypted database connections, and support for that has been in PostgreSQL since commit e0e7daef6da in 1999. But your PostgreSQL cannot be that old, because support for protocol version 3.0 was not added before 2003.

Actually, from studying src/backend/postmaster/postmaster.c and reading the mailing list, this is a bug on the PostgreSQL server:

The client must be configured to try GSS authentication, and when the server rejects, it wants to negotiate an SSL connections, but the server doesn't expect that at this point; hence the error.

See the discussion here. The bug has been fixed with release 12.3.

As a workaround, disable either GSS authentication or SSL negotiation on the client.

In psycopg2, disabling SSL is done by using sslmode="disable" in the connection string, and disabling GSS is done with gssencmode="disable". See the documentation for details.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Sorry for pointing my finger too rashly, it is a PostgreSQL server bug.
Not sure if it helps, but I had the same bug using docker's 12.1-alpine image, the bug doesn't show up when using 12.1 image.
@cglacet That must be because you are doing something different. This is at the moment a live bug, and the proposed fix has not been applied to the code yet.
@LaurenzAlbe no, that's the exact same code for both tests, the only thing I changed is the FROM directive in my docker-compose file. In both cases I mount a volume /var/lib/postgresql/data to my local host. When I connect to the database from the host (also running version 12.1) the error message shows when the database was created by the 12.1-alpine docker container, but not when it has been created by the 12.1 container.
@Long_NgV I have added a description how to disable SSL and GSS in client connections. The database is called "Postgres", not "Postgre".
|
8

Adding ?gssencmode=disable to the connection string worked for me:

import pyodbc from sqlalchemy import create_engine engine = create_engine(f'postgresql://{user}:{password}@localhost:5432/database_name?gssencmode=disable')
Improve this answer

1 Comment

This sounds like you are disabling encryption. Does this solution create a security hazard?
5

Getting a similar error working with Laravel and Postgres. Solved it by putting this in my .env file: PGGSSENCMODE=disable

Improve this answer

1 Comment

Whenever you answer questions, do not hesitate to clarify, what you proposed solution does, where you got the idea and what possible drawbacks might be. Furthermore, to disable encryption, when the server clearly provides some support is not a good idea, even for local databases. Someday one might move the database to a different host, and not realize, that he just majorly messed up.
2

If you installed this Psycopg2 module through conda command, then uninstall that module and install using pip command.

Command: pip install Psycopg2

The above command may resolve your issue. I resolved through this step

Improve this answer

Comments

1

In order to have a SSL connection working and using certificates, you can disable GSS connection mode to avoid the client to attempt to connect with GSS and directly try a SSL connection (Postgres version 12 < 12.3, I had no such issues with a test on version 11).

Below an example with the verify-ca option, providing filenames for certificates and key (example names from GCP Cloud SQL, running Postgres 12.1 when posted).

 from sqlalchemy import create_engine db_user = 'user' db_pwd = 'secret' db_host = 'hostname' db_port = '5432' db_name = 'test' cnn = f'postgresql://{db_user}:{db_pwd}@{db_host}:{db_port}/{db_name}' ssl_args = { 'gssencmode': 'disable', 'sslmode': 'verify-ca', 'sslrootcert': 'server-ca.pem', 'sslcert': 'client-cert.pem', 'sslkey': 'client-key.pem', } engine = create_engine(cnn, connect_args=ssl_args)

This engine can then be used with pandas for example:

 df.to_sql('my_table', con=engine)
Improve this answer

Comments

1

Using PostgresSQL 13.0 I had the same problem, displaying the error messages:

 unsupported frontend protocol 255.255: server supports 2.0 unsupported frontend protocol 0.0: server supports 2.0 to

according to this site it postgresql.org deals with the SSL / GSS Protocol Negotiation Problem. Which should already be resolved in Postgres version 12.0

I was able to solve it following the guidelines contained in these sites: highgo.ca

In the postgres terminal, I executed the command below setting the environment variable gssencmode = disable and the problem was solved:

 psql -h localhost -U postgres -d "dbname=belez gssencmode=disable";
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.