How to properly use client certificates with sqlalchemy 2 and psycopg 3?

[WannaBeGeekster]

Hello,

I have fully configured my postgres environment for SSL certificates and I can connect on my local machine to my remote postgres server using client certificates. I know I have created the certificates properly as they work successfully to connect with psql.

I don't know if the client key needs to be in a specific format for it to work with psycopg or not? I don't know if any one has any good links or suggestions on getting this setup. I am deploying a flask app in a docker container. I am getting an error, but some of my investigations point to potentially needing the key to be in a DER format compared to PEM. Of course this is typically using a JDBC connector, but at this point I am just not quite sure what could be going on here.

Since there is a complete lack of documentation anywhere for connecting with client certificates then I feel like I am probably doing something wrong here. My key file does have 400 permissions (not sure if this is the issue, but it is owned by the user who runs the webserver).

This is the error I am getting:

sqlalchemy.exc.OperationalError: (psycopg.OperationalError) connection failed: FATAL: connection requires a valid client certificate

Thanks,
WBG

[CaselIT]

Hi,

Have you tried asking to the psycopg3 borad?
Sqlalchemy just passes things along to its connect.

Once you have a working connect call with psycopg we can help ensure that sqlalchemy is calling connect in the proper way