PostgreSQL TLS: Specify Client Cert and Key in Connection URL

Jo P. · 2025-05-02T09:01:25.206Z

When using PostgreSQL over TLS, what's the recommended way to specify the paths to client cert and key in the postgres connection url? I used ENV_PHOENIX_SQL_DATABASE_URL with a formatted connection url: postgresql:// :***@ / ?sslcert= &sslkey= &sslmode=require&sslrootcert= but got the following error 🧵

PostgreSQL TLS: Specify Client Cert and Key in Connection URL | Arize AI Community

Jo P.
·
RunLLM
APP
·
It could be possible that specifying `sslcert`, `sslkey`, and `sslrootcert` as query parameters in the `PHOENIX_SQL_DATABASE_URL` is not fully supported or may be stripped/ignored by Phoenix, especially since there have been issues with SSL parameters being lost due to internal connection string manipulation (<https://github.com/Arize-ai/phoenix/issues/5647|see this issue>). The official documentation and code examples only mention using `sslmode=require` or `ssl=require` as a query parameter, but do not explicitly document support for `sslcert`, `sslkey`, or `sslrootcert` in the connection URL (<https://docs.arize.com/phoenix/self-hosting/configuration|docs>, <https://github.com/Arize-ai/phoenix/blob/main/tests/unit/db/test_engines.py|test code>). Though I'm not entirely certain, it might be that Phoenix currently does not support passing client certificate and key paths via the connection URL, or that these parameters are not passed through to the underlying driver. If you need client certificate authentication, you may need to open a feature request or check for recent updates. Would you like more detail on possible workarounds or the current state of TLS support in Phoenix? Sources: - <https://github.com/Arize-ai/phoenix/issues/5647|see this issue> - <https://docs.arize.com/phoenix/self-hosting/configuration|docs> - <https://github.com/Arize-ai/phoenix/blob/main/tests/unit/db/test_engines.py|test code>
💡 Hint: Mention RunLLM in the thread for followups.

Jo P.

It actually looks like sqlalchemy's asyncpg dialect does support this:

import asyncio
import asyncpg

async def async_main():
    conn = await asyncpg.connect(
        "postgresql://<username>:***@<hostname>/<dbname>?sslcert=%2Fcerts%2Fdb%2Fclient-cert.pem&sslkey=%2Fcerts%2Fdb%2Fclient-key.pem&sslmode=require&sslrootcert=%2Fcerts%2Fdb%2Fserver-ca.pem"
    )
    row = await conn.fetchrow("SELECT 1")
    print(f">>> {row}")

asyncio.run(async_main())

^ above script runs fine and return my query result

Rusty
·
Maybe try ssl=require (instead of sslmode). I don't use client certs, but that's the parameter that gets it to use TLS.
Jo P.
·
Hi Rusty, I saw your thread about postgres TLS too. Wonder how were you able to look up the client cert, client key without providing a path?
Rusty
·
We're not using client certs, we still/only use password auth
Rusty
·
phoenix is using sqlalchemy make_url which does some translation of parameters for different drivers. https://github.com/Arize-ai/phoenix/blob/main/src/phoenix/db/engines.py#L40-L46
Rusty
·
You could try passing your input to make_url and see if the output is accepted by the driver
Jo P.
·
based on my python code above, where I'm using asyncpg.connect directly without sqlalchemy in the middle, the connection was successful
Rusty
·
Yeah, so sqlalchemy (used in phoenix) might be changing your URL
Rusty
·
Sorry, I don't have much to add. I'll let the phoenix folks comment.
Jo P.
·
Appreciate your help Rusty! 🙏
🙂1
Roger Y.
·
we actually have two pg clients, so this may require some investigation on our end. could you please file us a ticket to track? thanks
Jo P.
·
thank you Roger Y.: https://github.com/Arize-ai/phoenix/issues/7397
👍1
Jo P.
·
Specifically, we found this code block: https://github.com/Arize-ai/phoenix/blob/main/src/phoenix/db/engines.py#L40-L62 I printed out all the url params and they look all good. I see phoenix is overriding driver name here: url.set(drivername="postgresql+asyncpg") , which seems to ask sqlalchemy to use asyncpg dialect -- and it really should work based on this plain vanilla python code below:
import asyncio import asyncpg async def async_main(): conn = await asyncpg.connect( "postgresql://<username>:***@<hostname>/<dbname>?sslcert=%2Fcerts%2Fdb%2Fclient-cert.pem&sslkey=%2Fcerts%2Fdb%2Fclient-key.pem&sslmode=require&sslrootcert=%2Fcerts%2Fdb%2Fserver-ca.pem" ) row = await conn.fetchrow("SELECT 1") print(f">>> {row}") asyncio.run(async_main())
Maybe there's a mismatch in the sqlalchemy version phoenix is using?
👀1

23 comments