Google cloud sql Postgres python connector ssl error

[snef]

Hi all,

i'm using python google cloud sql connector into my Flask application, so i can read and write data from my istance of google cloud sql.

This is my code for connection:

def getconn():
    with Connector() as connector:
        conn = connector.connect(
            instance_connection_name,
            "pg8000",
            user = db_user,
            password = db_pass,
            db = db_name,
            ip_type = IPTypes.PUBLIC
        )
        return conn

 and into my app.py file i set the connection in this way:

app.config["SQLALCHEMY_ENGINE_OPTIONS"] = {
    "creator" : getconn,
    "max_overflow": 2,
    "pool_timeout": 30,
    "pool_size": 5,
    "pool_recycle": 1800
}

db.init_app(app)

 now i try to add some feature with this code:

@blp.route("/v0/service")
class IstanceRegister(MethodView):
    @blp.arguments(JobSchema)
    def post(self, istance_data):
        
        search_list = ast.literal_eval(istance_data['search_list'])
        uuid_istance = istance_data['unit_uid']
        token = get_token()
        job_created = manage_data(search_list,token)

        
        
        for job in job_created:
            if job['status'] == 'ok':
                jj = jobModel(
                    client_uid_user = ['xxxxxxx'],
                    created_at = date_time,
                    status = 'active',
                    unit_uid = job['istance_uuid'],
                    country = ['USA']
                )

                db.session.add(jj)
                db.session.commit()
                print('Insert ok')
            else:
                print('Error insert')


    
        return {"message": "Result creation"}, 201

 Now when i run the app from my laptop and i call the api to insert data, i receive this errore connection

Cannot connect to host sqladmin.googleapis.com:443 ssl:True [SSLCertVerificationError: (1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:997)')]

The same code using into other app deployed as cloud run service works fine. How i can fix it when i run from my laptop?

[ms4446]

This error usuallly occurs because the SSL certificate presented by the remote server could not be verified by your local machine. Below are some possible solutions that might resolve this issue:

1. Check your system time and date: An incorrect system time and date can cause SSL certificate verification to fail. Ensure that your system time and date are set accurately.

2. Ensure that you have the necessary CA certificates installed: SSL certificate verification requires a set of trusted root CA certificates on your system. Make sure you have the necessary root certificates installed on your local machine.

3. Update your Python packages: Outdated packages may not have the latest security updates and bug fixes, which could cause SSL verification issues. You can update packages such as certifi and urllib3 to their latest versions using pip: pip install --upgrade certifi urllib3

4. Manually specify the CA certificate bundle: If you have a specific CA certificate bundle you want to use for verification, you can specify it using the REQUESTS_CA_BUNDLE environment variable (for the requests library) or by setting the ssl_context argument (for the Connector class) in your code.

[snef]

Hi,

thanks for your replay.

For 4 point do you mean something like this?

ctx = ssl.create_default_context()
ctx.check_hostname = False
ctx.verify_mode = ssl.CERT_NONE



def getconn():
    with Connector() as connector:
        conn = connector.connect(
            instance_connection_name,
            "pg8000",
            user = db_user,
            password = db_pass,
            db = db_name,
            ip_type = IPTypes.PUBLIC,
            context=ctx
        )
        return conn

because i have try but the result is always the same

[ms4446]

To manually specify the CA certificate bundle when creating the Connector instance in the getconn() function, you can set the ssl_context argument to an ssl.SSLContext object that has been configured with the path to your CA certificate bundle file. The load_verify_locations() method of the ssl.SSLContext object allows you to specify the path to the CA certificate bundle. Here's how you can modify the getconn() function to do this:

import ssl

def getconn():
    # Create an SSL context with the desired protocol version (e.g.,  PROTOCOL_TLS_CLIENT)
    ssl_context = ssl.create_default_context(ssl.PROTOCOL_TLS_CLIENT)
    # Load the CA certificate bundle file
    ssl_context.load_verify_locations(cafile='/path/to/your/ca-bundle.crt')

    with Connector(ssl_context=ssl_context) as connector:
        conn = connector.connect(
            instance_connection_name,
            "pg8000",
            user=db_user,
            password=db_pass,
            db=db_name,
            ip_type=IPTypes.PUBLIC
    )
return conn

[snef]

Thanks for your help. Hi have tried with your example but i have an error with PROTOCOL_TLS_CLIENT. I think that is a issue on my python version 3.10

[ms4446]

If you're using Python 3.10, ssl.PROTOCOL_TLS_CLIENT should be ok. . Here are a few things you can try:

• Check your Python installation's certificate store: Python uses its own certificate store independent of your operating system's certificate store. If the certificate store of your Python installation is out of date or corrupted, it could lead to this error. You can update the certificate store for Python using the certifi package. You can install it with pip:

     pip install --upgrade certifi

• Verify the SSL_CERT_FILE environment variable: If the SSL_CERT_FILE environment variable is set in your system, make sure it points to the correct location of the CA certificates file.

• Disable SSL verification (not recommended for production): As a last resort, and only for local development, you can try disabling SSL verification by setting the PYTHONHTTPSVERIFY environment variable to 0. This is generally not recommended as it makes your connections insecure, but can help you debug if the issue is with SSL verification:

     export PYTHONHTTPSVERIFY=0

If still getting you an error, could you please share the exact error message you're getting?

[snef]

Hi and thanks for your reply and your help. I have try all your indications but the problem is not fix...i have always this error 

File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiohttp/connector.py", line 1175, in _create_direct_connection
    transp, proto = await self._wrap_create_connection(
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/aiohttp/connector.py", line 982, in _wrap_create_connection
    raise ClientConnectorCertificateError(req.connection_key, exc) from exc
aiohttp.client_exceptions.ClientConnectorCertificateError: Cannot connect to host sqladmin.googleapis.com:443 ssl:True [SSLCertVerificationError: (1, '[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify failed: unable to get local issuer certificate (_ssl.c:997)')]

But, is there a way to bypass the verify certification like into requests library (verify= false)

[ms4446]

While it's technically possible to bypass SSL certificate verification, it's generally not recommended for security reasons. With that being said, if you understand the risks and you're in a situation where bypassing SSL certificate verification is necessary (e.g., for testing or dealing with self-signed certificates in a trusted environment), you can typically do so by passing ssl=False when making the request.

A few additional things you can try:

1. Check the CA certificate bundle file. Make sure the file is correct, exists at the path you're providing, and includes the necessary CA certificates. You can typically download the latest CA certificate bundle from a trusted source like Mozilla's CA Bundle.

2. Use the operating system's CA certificates. Instead of loading a specific CA certificate bundle file, you can tell Python's SSL module to use the CA certificates provided by your operating system. You can do this by calling ssl_context.set_default_verify_paths() after creating the SSL context. Here's how you can modify your code to do this:

import ssl

def getconn():
# Create an SSL context with the desired protocol version (e.g., PROTOCOL_TLS_CLIENT)
ssl_context = ssl.create_default_context(ssl.PROTOCOL_TLS_CLIENT)
# Use the operating system's CA certificates
ssl_context.set_default_verify_paths()

with Connector(ssl_context=ssl_context) as connector:
conn = connector.connect(
instance_connection_name,

[snef]

Always error

line 23, in getconn
    ssl_context = ssl.create_default_context(ssl.PROTOCOL_TLS_CLIENT)
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/ssl.py", line 750, in create_default_context
    raise TypeError(purpose)
TypeError: _SSLMethod.PROTOCOL_TLS_CLIENT

it's strange

[ms4446]

Taking another stab here.. Try the following:

import ssl

def getconn():
# Create an SSL context with safe default settings
ssl_context = ssl.create_default_context(ssl.Purpose.CLIENT_AUTH)
# Load the CA certificate bundle file
ssl_context.load_verify_locations(cafile='/path/to/your/ca-bundle.crt')

with Connector(ssl_context=ssl_context) as connector:
conn = connector.connect(
instance_connection_name,
"pg8000",
user=db_user,
password=db_pass,
db=db_name,
ip_type=IPTypes.PUBLIC
)
return conn

[snef]

Hello, i have tried with this code:

def getconn():
    # Create an SSL context with safe default settings
    ssl_context = ssl.create_default_context(ssl.Purpose.CLIENT_AUTH)
    # Load the CA certificate bundle file
    ssl_context.load_verify_locations(cafile='/Users/Downloads/ca-bundle.crt')

    with Connector(ssl_context=ssl_context) as connector:
        conn = connector.connect(
            instance_connection_name,
            "pg8000",
            user = db_user,
            password = db_pass,
            db = db_name,
            ip_type = IPTypes.PUBLIC
            
        )
        return conn

but i have this error:

with Connector(ssl_context=ssl_context) as connector:
TypeError: Connector.__init__() got an unexpected keyword argument 'ssl_context'

[ms4446]

Here is another option:

def getconn():
# Create an SSL context with safe default settings
ssl_context = ssl.create_default_context(ssl.Purpose.CLIENT_AUTH)
# Load the CA certificate bundle file
ssl_context.load_verify_locations(cafile='/Users/Downloads/ca-bundle.crt')

with Connector() as connector:
# Wrap the connector with SSL using the created SSL context
connector.ssl_wrap_socket(
ssl_context=ssl_context,
server_hostname=instance_connection_name
)

conn = connector.connect(
instance_connection_name,
"pg8000",
user=db_user,
password=db_pass,
db=db_name,
ip_type=IPTypes.PUBLIC
)
return conn

[snef]

Thanks ms,

but with this option i have always error, but different:

AttributeError: 'Connector' object has no attribute 'ssl_wrap_socket'

[ms4446]

Based on this error, it looks like the code needs a slight modification.

from sqlalchemy import create_engine
import ssl

def getconn():
# Create an SSL context with safe default settings
ssl_context = ssl.create_default_context(ssl.Purpose.CLIENT_AUTH)
# Load the CA certificate bundle file
ssl_context.load_verify_locations(cafile='/Users/Downloads/ca-bundle.crt')

engine = create_engine('postgresql+pg8000://user:password@host/db', connect_args={'ssl_context': ssl_context})

conn = engine.connect()
return conn

Here we import the create_engine function from SQLAlchemy and the ssl module. We create an SSL context using ssl.create_default_context() and load the CA certificate bundle file. Then, we use create_engine to establish the connection with the pg8000 driver, passing the SSL context through the connect_args parameter. Finally, we connect to the database using the connect() method of the engine.

[snef]

Hi ms,

also with this example there is a error:

  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/site-packages/pg8000/core.py", line 261, in __init__
    self._usock = ssl_context.wrap_socket(self._usock, server_hostname=host)
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/ssl.py", line 513, in wrap_socket
    return self.sslsocket_class._create(
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/ssl.py", line 1062, in _create
    self._sslobj = self._context._wrap_socket(
ssl.SSLError: Cannot create a client socket with a PROTOCOL_TLS_SERVER context (_ssl.c:801)

as host into postgres connection i have set the Public IP of my cloud sql db

[ms4446]

Upon some research the error you are experiencing might be related to a bug in Python 3.10 when creating client sockets with a PROTOCOL_TLS_SERVER context. This issue has been reported in different libraries, including pg8000 and aioimaplib, and it appears to be fixed in some newer versions of those libraries. However, the specific fix may depend on the library you're using and the version you have installed.

• To resolve this issue, you can try the following steps:Update the pg8000 library to the latest version. You can use the following command to upgrade the library:

pip install --upgrade pg8000

• If updating the library doesn't resolve the issue, you can try using a different library or PostgreSQL driver that is compatible with Python 3.10 and supports SSL connections. Some alternative libraries you can consider are psycopg2 or asyncpg. You may need to adjust your code to use the specific syntax and API of the chosen library.
• If using a different library is not an option, you can try downgrading your Python version to a version prior to 3.10 if it's feasible for your project. This would allow you to avoid the specific bug in Python 3.10.

[snef]

Hello me,

I have tried with Python version 3.8 and 3.7, but the error is always the same

thanks

[ms4446]

Let's take this a few steps back.. Can you confirm that you can connect to you CloudSQL-PG instance from your laptop outside of the code ie through psql or pgadmin?

[jackwotherspoon · Accepted solution]

@snef @ms4446 The initial code in the question here has nothing wrong with it and is properly using the  Cloud SQL Python Connector. You should not need to change your application code at all. The cause of this error is most likely to do with the SSL configuration on the local machine itself. 

This is a common issue and is well known on StackOverflow. I'd recommend trying the answers in this SO post to resolve it.