Issues Connecting Prefect to Postgresql

[MrCoffey]

Summary

I'm trying to connect with Prefect, I passing a URL with the following format to the secret documented here:

postgresql+asyncpg://user:pass@mydb-host.us-west-2.rds.amazonaws.com/dbname?sslmode=require&sslrootcert=~/.postgresql/server-ca.pem 

I'm mounting the certificates in a volumeMount in the path ~/.postgresql as documented here.

This configuration works fine without the SSL configuration but it seems like there is a lack of documentation when it comes to using SSL to connect to postgres.

Current behavior

When I use the URL above, The prefect-server pod fails with the following error:

  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line 615, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 916, in connect
    await_only(creator_fn(*arg, **kw)),
TypeError: connect() got an unexpected keyword argument 'sslmode'

Application startup failed. Exiting.

According to this issue, the key ssl should be used instead of sslmode but still failing this time with the error:

    return self.loaded_dbapi.connect(*cargs, **cparams)
  File "/usr/local/lib/python3.8/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py", line 916, in connect
    await_only(creator_fn(*arg, **kw)),
TypeError: connect() got an unexpected keyword argument 'sslrootcert'

Desired behavior

• Prefect should be able to parse the arguments from the URL and attempt an SSL connection with Postgresql using certificates.

• The documentation should illustrate how to configure correctly connections to psql using SSL.

I'm using the version: 2023.03.30 of the helm chart.

How can I configure Prefect to use an SSL connection with Postgresql using certificates?

Thanks!

This issue can be related to sqlalchemy/sqlalchemy#6275 and tortoise/tortoise-orm#1376

[MrCoffey]

I managed to resolve this issue.

First I needed to configure prefect to mount the certificate to the root certificate in /home/prefect/.postgresql that way it can be found by asyncpg. This is the default location according to the postgres documentation.

prefect-server:
  server:
    image:
      prefectTag: 2.10-python3.9
      debug: true
    extraVolumes:
      - name: db-ssl-secret
        secret:
          secretName: db-ssl-secret
          defaultMode: 384
    extraVolumeMounts:
      - name: db-ssl-secret
        mountPath: "/home/prefect/.postgresql"
        readOnly: true
  postgresql:
    useSubChart: false
    auth:
      existingSecret: prefect-postgresql

The connection string should have the following format:

postgresql+asyncpg://user:pass@mydb-host.us-west-2.rds.amazonaws.com/dbname?ssl=verify-ca notice the argument ssl=verify-ca, that argument will require a file with the name root.crt holding the ca certificate for the database.

apiVersion: v1
kind: Secret
metadata:
  name: db-ssl-secret
data:
  root.crt: BASE64ENCODECERTIFICATE=
type: Opaque

I hope this can help someone else. I'll leave the PR open since this configuration should be added to the documentation.