python-oracledb: cannot mTLS connect to Oracle DB on Amazon RDS service in Thin mode

[FrancoisNoyez]

Context
I want to implement python-based Cloud Functions on Google Cloud Plateform. Those Cloud Functions will need to connect to our Amazon RDS Oracle DB.
Until recently this was mostly very difficult / impossible, since using cx_Oracle would require the client software to be installed in the environnement set up for the Cloud Function, and GCP would not let us easily do that (I did try at length...).
But oracledb allows to implement that, with it's Thin mode.

I can't develop and test directly on my local computer, since the way I access the DB is different there:

• when I use my computer, I need to connect to the DB through a ssh tunnel opened through a server whose IP is whitelisted for the RDS OracleDB
• for the Cloud Functions it will be different: they will be attached to a VPN such that the network's elements in it are whitelisted for the RDS OracleDB

I'm already responsible for the maintenance of a project which uses jobs done through pods created in that VPN, so what I do to dev is that I manually create such a pod, and tests things whitin it. The pods for this projet are created through the use of a Docker image which has been prepared to contain the Oracle instantclient software, and the program uses cx_Oracle to connect. To achieve the connection we need to use mTLS, through the use of a .sso wallet file.

I read the doc in order to know how to replicate that when using Thin mode of python-oracledb, but I was unable to do so.
The initial difficulty was about getting the .pem file (I don't have knowledge / experience regarding encryption, I just try to follow what the docs tell me). I finally got one from here, I took the rds-ca-2019-root.pem file.
I renamed it to ewallet.pem, and put it in a directory whose path I give when I try to connect.
But when I test this, the connection fails, with an error message which does not say anything useful regarding what went wrong.

Code

# encoding: utf-8

import oracledb
import os

username = os.environ["ORACLE_USER"]
password = os.environ["ORACLE_PASSWORD"]
dsn = """(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=DB_NAME.ap-southeast-1.rds.amazonaws.com)(PORT=2484))(CONNECT_DATA=(SID=XXXXX)(SERVICE_NAME=XXXXX)))"""
lib_dir = "/opt/instantclient_19_3"
config_dir = "/opt/oracle/network/admin"
wall_loc = "/code/wallet2"
wall_pw = None
# wall_pw = ""

use_thick_mode = False

kwargs = {
    "config_dir": config_dir,
    "wallet_location": wall_loc,
    "wallet_password": wall_pw,
}
if use_thick_mode:
    oracledb.init_oracle_client(lib_dir=lib_dir)
    kwargs = {}

with oracledb.connect(
    user=username,
    password=password,
    dsn=dsn,
    **kwargs,
) as ocon:
    with ocon.cursor() as cursor:
        sql = """select '['||systimestamp||'] Connectivity OK' from dual"""
        for r in cursor.execute(sql):
            print(r[0])

Error

root@POD_NAME:/code# python test_oracledb_connect2.py 
Traceback (most recent call last):
  File "src/oracledb/impl/thin/connection.pyx", line 97, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "src/oracledb/impl/thin/protocol.pyx", line 145, in oracledb.thin_impl.Protocol._connect_phase_one
  File "src/oracledb/impl/thin/protocol.pyx", line 279, in oracledb.thin_impl.Protocol._connect_tcp
  File "src/oracledb/impl/thin/crypto.pyx", line 118, in oracledb.thin_impl.get_ssl_socket
ssl.SSLError: [SSL] PEM lib (_ssl.c:3932)

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "test_oracledb_connect2.py", line 31, in <module>
    **kwargs,
  File "/usr/local/lib/python3.7/site-packages/oracledb/connection.py", line 1000, in connect
    return conn_class(dsn=dsn, pool=pool, params=params, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/oracledb/connection.py", line 128, in __init__
    impl.connect(params_impl)
  File "src/oracledb/impl/thin/connection.pyx", line 294, in oracledb.thin_impl.ThinConnImpl.connect
  File "src/oracledb/impl/thin/connection.pyx", line 184, in oracledb.thin_impl.ThinConnImpl._connect_with_params
  File "src/oracledb/impl/thin/connection.pyx", line 157, in oracledb.thin_impl.ThinConnImpl._connect_with_description
  File "src/oracledb/impl/thin/connection.pyx", line 109, in oracledb.thin_impl.ThinConnImpl._connect_with_address
  File "/usr/local/lib/python3.7/site-packages/oracledb/errors.py", line 103, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database. Connection failed with "[SSL] PEM lib (_ssl.c:3932)"

What I've tried
I've used the .pem file that I downloaded in order to create a fill a new .sso wallet file, and I used it either with cx_Oracle or with the Thick mode of oracledb (cf the code snippet above), and I was able to connect to the Oracle DB without issue.

When I remove the ewallet file from the expected location, there is an error message that says that the file is missing:

oracledb.exceptions.ProgrammingError: DPY-2018: cannot connect to database. Wallet file /code/wallet2/ewallet.pem was not found

When I create an empty cwallet.pem file, and put it where oracledb is expecting it when using Thin mode, there is an error message that says that the file does not contain a certificate:

oracledb.exceptions.OperationalError: DPY-6005: cannot connect to database. Connection failed with "[X509] no certificate or crl found (_ssl.c:4140)"

So I'm confident that oracledb is able to find the file, load its content, and search for a certificate.

I'm not too sure what to do regarding the wallet_password value, since I have no info about that from the source of the .pem file: the Amazon site does not say anything about that, so I'm assuming that I don't need to specify anything?
Just to be sure, I've browsed the web, and saw tutos which explain how to convert from the .sso wallet file back to the .pem file, and at some point we can specify passwords. I've done that, but even with rebuilt .pem file, and me specifying the correct password, the error stays the same.
For information, the script described in the doc to convert from .p12 to .pem did not work for me, so there is probably something to fix there:

francois@francois-laptop#:~/ python create_pem.py --wallet-password "XXXXX" ./
Traceback (most recent call last):
  File "create_pem.py", line 45, in <module>
    f.write(private_key.private_bytes(Encoding.PEM, PrivateFormat.PKCS8,
AttributeError: 'NoneType' object has no attribute 'private_bytes'

Finally, I've also tried to test this using the 1.0.0, 1.0.1, and 1.0.2 version of oracledb (I use 1.0.3 when I ask to install the library without specifying the version), but I always got the same underlying error: "[SSL] PEM lib (_ssl.c:3932)".

Question

• What does this error mean? Would it be possible to update the library so that the error can be explicit, like it is when the file is missing or when it's empty?
• What is the root cause of the error? Is there something specific to apply to the .pem file beforehand? Seems like the use of the certificate of this file is enough to make things work when using Thick mode, though... In that case, is there an issue with the current version of the library for some cases of .pem file? Why would it work for other people but not me, though : / ?

[anthony-tuininga]

Hi, @FrancoisNoyez, thanks for the detailed descriptions of what you did. If you are using mTLS the ewallet.pem file is needed, and, unless you have stored it unecrypted, you also need to supply the wallet_password value. That will be the value that is required to decrypt the encrypted private key and certificates that are stored in the ewallet.pem file. If you supply the wrong value (or no value) then you will get the "[SSL] PEM lib" error. Yes, the error is terrible, but its not one that we control, unfortunately!

Regarding the conversion from .p12 to .pem -- it looks like you don't have a private key in the .p12 file. Where did you get that file from? For Oracle Cloud databases, the wallet is downloaded from console and is a zip file containing these files:

cwallet.sso  ewallet.pem   ojdbc.properties  sqlnet.ora    truststore.jks
ewallet.p12  keystore.jks  README            tnsnames.ora

Thick mode doesn't look at ewallet.pem but looks at cwallet.sso instead. Where did you get that file from? Does it have an accompanying ewallet.p12 or ewallet.pem?

[anthony-tuininga]

Your database is actually using a self-signed certificate? In production? Interesting! That suggests that one-way TLS is actually being used -- which makes life possibly less complicated. :-) That might also explain why just the certificate is needed for the thick driver and not a private key as well.

You need to get the OS to trust the certificate (in the same way that you would get the OS to trust a self-signed certificate from a web server). This differs from platform to platform so I can't give you exact instructions -- but hopefully Google can help there! If you can complete this step the connection may succeed (if one-way TLS is indeed in place).

[FrancoisNoyez]

I see.
Now I make more sense of this blog article that I had found during earlier investigations, and that I had tried to use. Seems like getting the OS to trust the certificate is the point of this article. I tried to replicate it within the Docker instance of my current jobs, but with little success. That said, even if I had succeeded, since the end goal is to make things work within a CloudFunction, and since I probably can't apply the needed commands in that context (I did try to use subprocesses, when I tried to install the Oracle software, but it did not work), it's probably moot.

[anthony-tuininga]

Yes, that article looks exactly like what you need to do -- so the fact that it doesn't work for you makes life complicated! I'm not sure what else to tell you at this point. If you are able to build python-oracledb yourself, you can modify the following lines:

https://github.com/oracle/python-oracledb/blob/main/src/oracledb/impl/thin/crypto.pyx#L127-L136

Specifically, you can pass a value to wallet_location but comment out the line that calls load_cert_chain() since that expects a private key. If that works for you, great! Then we can discuss how to make this possible without hacking the code. :-)

[FrancoisNoyez]

Hi Anthony,

Great idea, it worked! I commented this line out, rebuilt the package, and used it, and the code was able to connect to the DB!

Seems like indeed the issue is that, the way the library is implemented, if a non-null 'wallet_location' value is provided, then the library expects that it will contain a password / a private key to be compared to the value provided by 'wallet_password'. Here things break down because there is no such password / private key to be extracted from the .pem file.

One way to improve the library that I would suggest would be to test for the existence of this in the .pem file, and compare that to the value provided by 'wallet_password' (with null value being interpreted as wanting to use the certificate for one-way TLS), and then proceed accordingly with the rest of the code, or at least raising explicit exceptions if there is a mismatch.
Or to have an additional boolean parameter such as 'use_one_way_tls', and decide whether or not to "carry out the search for password / private key and compare it to the 'wallet_password' value" accordingly, and still with the raising of explicit exception if there is an issue.

Anyway, thank you for your time, with that I have a way to move forward with my project! I'll still want to fetch the .pem file generated through the Oracle Cloud console, for reference, and just in case. For now I plan to not resolve the question / mark this post as answer until I have gotten it, and verified that all things work properly, if that's ok with you. Don't worry, I won't forget.

[FrancoisNoyez]

Ok, so apparently the people in my organisation in charge of managing our Oracle DB do not have access to the Oracle console, as apparently this requires to be the admin of the server where the DB is hosted, and we are not.
And I can't install the custom version of the library inside the CloudFunction.
So the easier solution for us would be that "python-oracledb" be updated to allow to use .pem file which do not contain a private key / which are unencrypted.
Since I have made it work with my custom modification, I will want to share it here, by creating a PR. According to the contribution guidelines, I first need to create a github issue, so that's what i'll do.

[cjbj]

For the record, this was enhanced in python-oracledb 1.2. See #65

[FrancoisNoyez]

Got it, indeed things work now; thank you : ) !