Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

psycopg2.OperationalError: FATAL: unsupported frontend protocol 1234.5679: server supports 2.0 to 3.0

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

like image 868
The-Han-Emperor Avatar asked Dec 05 '19 07:12

The-Han-Emperor


3 Answers

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.

like image 113
Laurenz Albe Avatar answered Oct 16 '22 10:10

Laurenz Albe


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')
like image 5
Julio Batista Silva Avatar answered Oct 16 '22 09:10

Julio Batista Silva


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

like image 4
atmik Avatar answered Oct 16 '22 09:10

atmik