python-oracledb thin client returns DPY-6005

Question

I'm trying to connect to a 21c ATP and 19c ADP (free tier, ACL enabled/configured with "My Address", TLS enabled (mTLS set to "Not required"), connection string contains "ssl_server_dn_match=yes") using Python's thin client but at the point of making a connection or setting up a connection pool, I get:

OperationalError: DPY-6005: cannot connect to database. Connection
failed with  "[SSL: CERTIFICATE_VERIFY_FAILED] certificate verify
failed: self signed certificate  in certificate chain (_ssl.c:1131)"

Envioronment:

DB: ATP 21c and ADP 19c

Python client library: oracledb-1.2.1 (I've tried 1.2.0 and 1.1.1, as well, but to no avail)

Environment: Python 3.10.4 and 3.8.10 (running on Mac OS)

Code sample:

import oracledb

# copied from the ATP's "Database Connection"
cs='''(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.uk-london-1.oraclecloud.com))(connect_data=(service_name=xxxx.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))'''

connection = oracledb.connect(user="admin", password="<password>", dsn=cs)

with connection.cursor() as cursor:
    try:
        sql = """select systimestamp from dual"""
        for r in cursor.execute(sql):
            print(r)

    except oracledb.Error as e:
        error, = e.args
        print(error.message)
        print(sql)
        if (error.offset):
            print('^'.rjust(error.offset+1, ' '))

References:

I've used the following documents as a reference:

• https://blogs.oracle.com/opal/post/easy-way-to-connect-python-applications-to-oracle-autonomous-databases
• https://blogs.oracle.com/developers/post/writing-a-flask-application-using-python-oracledb
• https://python-oracledb.readthedocs.io/en/latest/user_guide/installation.html
• https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/connecting-python-tls.html#GUID-CA446B91-BC48-4A66-BF69-B8D54B9CBAD4

Answer 1

That error tells you that the certificate supplied by the server is not one that any local certificate authority recognizes (which is necessarily the case with self-signed certificates). Two options are available to resolve this:

1. Tell the OS the certificate is acceptable by adding it to the OS certificate "store"

2. Use an Oracle wallet (ewallet.pem) that contains the relevant certificates and set the wallet_location parameter appropriately. This was discussed in this issue.