Connecting Python to OpenGauss Database Using psycopg2

 After giving some classmates a helping hand, I found that they met same problem when using python to connect openGauss by psycopg2. That is the encryption strategy you need md5.

Error while connecting to PostgreSQL: connection to server at "116.62.59.207", port 5432 failed: none of the server's SASL authentication mechanisms are supported

Here is code snippet for testing connection.

import psycopg2

try:
    connection = psycopg2.connect(
        user="your_username",
        password="your_password",
        host="116.62.59.207",
        port="5432",
        database="your_database",
        sslmode="require"  # Adjust based on server requirements
    )
    print("Connection successful")
except Exception as error:
    print("Error while connecting to PostgreSQL:", error)

Two files in openGauss you need to pay attention

pg_hba.conf

add host all all 0.0.0.0/0 md5 for remote connection.

0.0.0.0/0 allows connections from any IP address using md5 (password required).

192.168.137.0/24 allows connections from the specific IP address.

It's up to you.

 192.168.137.0/24 is my IP address, you should switch it! 

or use 0.0.0.0/0 directly for convenience.

postgresql.conf

set listen_addresses for opening.

change the password_encryption_type into 0 or 1.

Restart is important

gs_ctl reload -D /db/software/openGauss/data/single_node
gs_ctl restart -D /db/software/openGauss/data/single_node

Attention

1. remote connection doesn't support for initial user. (privilege issue)

2. After changing the encryption strategy into the md5 type, you need to alter the password.

If you forget to alter it, you would meet this: 

psycopg2.OperationalError: connection to server at "116.62.59.207", port 5432 failed: fe_sendauth: invalid authentication request from server: AUTH_REQ_SASL_CONT without AUTH_REQ_SASL

3. Or, like me, create an new user

CREATE USER remoter WITH PASSWORD 'Qq123123'；

don't forget to give some privilege like login, and operation to the selected database.

ALTER USER remoter WITH LOGIN;

GRANT ALL PRIVILEGES ON DATABASE postgres TO remoter;

Enjoy your coding!