Postgres auth with certificates

Help
1

Hello, due to corporate policies I have to run Prefect 2.0 with certificate authentication. It seems that Orion rely on sqlalchemy with psycopg2 driver.
I verified that with psycopg2 I can connect easily to my already present postgresql instance so, will prefect works simply using a connection string with certificates like this?:

postgresql+asyncpg://prefect@prefect-orion-postgresql.prefect-orion:5432/orion?sslkey=srvprivkey.pem&sslcert=srvcert.pem&sslrootcert=ca.pem

$ cat test-db.py
import psycopg2
conn=psycopg2.connect(
host=“localhost”,
database=“postgres”,
user=“postgres”,
sslcert=“srvcert.pm”,
sslkey=“srvprivkey.pem”,
sslrootcert=“ca.pem”
)
cur=conn.cursor()
cur.execute(‘select version()’)
print(cur.fetchone())

2

Hello, it seems that there is something wrong in prefect code (prefecthq/prefect:sha-42b80f1-python3.10) when passing ssl parameters in pg connection string to asyncpg (v0.27.0 in my case), as a result it doesn’t work.
It could be related to this bug asyncpg does not work with “sslmode” query param when called from SQLAlchemy · Issue #737 · MagicStack/asyncpg (github.com)

$ k logs prefect-orion-846fb4dd4-8tptl

| _ \ _ \ | | / | | / _ | _ _ / _ | | |
| / / || || | ( | | | () | /| | () | .` |
|| ||__|| |_| |_| _/||___/|_|_|

Configure Prefect to communicate with the server with:

prefect config set PREFECT_API_URL=http://0.0.0.0:4200/api

View the API reference documentation at http://0.0.0.0:4200/docs

Check out the dashboard at http://0.0.0.0:4200

Traceback (most recent call last):
File “/usr/local/lib/python3.10/site-packages/starlette/routing.py”, line 671, in lifespan
async with self.lifespan_context(app):
File “/usr/local/lib/python3.10/site-packages/starlette/routing.py”, line 566, in aenter
await self._router.startup()
File “/usr/local/lib/python3.10/site-packages/starlette/routing.py”, line 648, in startup
await handler()
File “/usr/local/lib/python3.10/site-packages/prefect/orion/api/server.py”, line 348, in run_migrations
await db.create_db()
File “/usr/local/lib/python3.10/site-packages/prefect/orion/database/interface.py”, line 55, in create_db
await self.run_migrations_upgrade()
File “/usr/local/lib/python3.10/site-packages/prefect/orion/database/interface.py”, line 63, in run_migrations_upgrade
await run_sync_in_worker_thread(alembic_upgrade)
File “/usr/local/lib/python3.10/site-packages/prefect/utilities/asyncutils.py”, line 91, in run_sync_in_worker_thread
return await anyio.to_thread.run_sync(
File “/usr/local/lib/python3.10/site-packages/anyio/to_thread.py”, line 31, in run_sync
return await get_asynclib().run_sync_in_worker_thread(
File “/usr/local/lib/python3.10/site-packages/anyio/_backends/_asyncio.py”, line 937, in run_sync_in_worker_thread
return await future
File “/usr/local/lib/python3.10/site-packages/anyio/_backends/_asyncio.py”, line 867, in run
result = context.run(func, *args)
File “/usr/local/lib/python3.10/site-packages/prefect/orion/database/alembic_commands.py”, line 24, in wrapper
return fn(*args, **kwargs)
File “/usr/local/lib/python3.10/site-packages/prefect/orion/database/alembic_commands.py”, line 53, in alembic_upgrade
alembic.command.upgrade(alembic_config(), revision, sql=dry_run)
File “/usr/local/lib/python3.10/site-packages/alembic/command.py”, line 378, in upgrade
script.run_env()
File “/usr/local/lib/python3.10/site-packages/alembic/script/base.py”, line 569, in run_env
util.load_python_file(self.dir, “env.py”)
File “/usr/local/lib/python3.10/site-packages/alembic/util/pyfiles.py”, line 94, in load_python_file
module = load_module_py(module_id, path)
File “/usr/local/lib/python3.10/site-packages/alembic/util/pyfiles.py”, line 110, in load_module_py
spec.loader.exec_module(module) # type: ignore
File “”, line 883, in exec_module
File “”, line 241, in _call_with_frames_removed
File “/usr/local/lib/python3.10/site-packages/prefect/orion/database/migrations/env.py”, line 147, in
apply_migrations()
File “/usr/local/lib/python3.10/site-packages/prefect/utilities/asyncutils.py”, line 226, in coroutine_wrapper
return run_async_from_worker_thread(async_fn, *args, **kwargs)
File “/usr/local/lib/python3.10/site-packages/prefect/utilities/asyncutils.py”, line 177, in run_async_from_worker_thread
return anyio.from_thread.run(call)
File “/usr/local/lib/python3.10/site-packages/anyio/from_thread.py”, line 49, in run
return asynclib.run_async_from_thread(func, *args)
File “/usr/local/lib/python3.10/site-packages/anyio/_backends/_asyncio.py”, line 970, in run_async_from_thread
return f.result()
File “/usr/local/lib/python3.10/concurrent/futures/_base.py”, line 458, in result
return self.__get_result()
File “/usr/local/lib/python3.10/concurrent/futures/_base.py”, line 403, in __get_result
raise self._exception
File “/usr/local/lib/python3.10/site-packages/prefect/orion/database/migrations/env.py”, line 140, in apply_migrations
async with engine.connect() as connection:
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/base.py”, line 66, in aenter
return await self.start(is_ctxmanager=True)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/ext/asyncio/engine.py”, line 157, in start
await (greenlet_spawn(self.sync_engine.connect))
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/util/_concurrency_py3k.py”, line 115, in greenlet_spawn
result = context.switch(*args, **kwargs)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/future/engine.py”, line 406, in connect
return super(Engine, self).connect()
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py”, line 3315, in connect
return self._connection_cls(self, close_with_result=close_with_result)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py”, line 96, in init
else engine.raw_connection()
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py”, line 3394, in raw_connection
return self._wrap_pool_connect(self.pool.connect, _connection)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py”, line 3361, in _wrap_pool_connect
return fn()
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py”, line 327, in connect
return _ConnectionFairy._checkout(self)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py”, line 894, in _checkout
fairy = _ConnectionRecord.checkout(pool)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py”, line 493, in checkout
rec = pool.do_get()
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py”, line 145, in do_get
with util.safe_reraise():
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py”, line 70, in exit
compat.raise(
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py”, line 211, in raise
raise exception
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/impl.py”, line 143, in _do_get
return self._create_connection()
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py”, line 273, in _create_connection
return _ConnectionRecord(self)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py”, line 388, in init
self.__connect()
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py”, line 690, in connect
with util.safe_reraise():
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py”, line 70, in exit
compat.raise(
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/util/compat.py”, line 211, in raise
raise exception
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/pool/base.py”, line 686, in __connect
self.dbapi_connection = connection = pool._invoke_creator(self)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/create.py”, line 578, in connect
return dialect.connect(*cargs, **cparams)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/engine/default.py”, line 598, in connect
return self.dbapi.connect(*cargs, **cparams)
File “/usr/local/lib/python3.10/site-packages/sqlalchemy/dialects/postgresql/asyncpg.py”, line 780, in connect
await_only(self.asyncpg.connect(*arg, **kw)),
TypeError: connect() got an unexpected keyword argument ‘sslkey’

Application startup failed. Exiting.
Orion stopped!

3

Hello, I found an easy solution that worked for me just mounting the certificate and key files in Prefect’s pod under the “~/.postgresql/postgresql.{crt,key}” of the running user, as it is a default setting they were picked and problem solved.

4

Hey @davidp1404, I’m facing this issue trying to connect Prefect 2.0 to RDS and CloudSQL, but still having issues.

RDS and CloudSQL only provide me a server-ca.pem file, I’m placing that file under ~/.postgresql/ in a volumeMount inside my pod and I’m passing to prefect a connection string with the following format: postgresql+asyncpg://user:pass@mydb-host.us-west-2.rds.amazonaws.com/dbname?sslmode=require&sslrootcert=~/.postgresql/server-ca.pem which results in:

TypeError: connect() got an unexpected keyword argument 'sslmode'

when I change the parameter from sslmode=require to ssl=require then the error changes to:

TypeError: connect() got an unexpected keyword argument 'sslrootcert'

Should the name of the cert be called postgresql.pem or something?

For reference, I’ve installed Prefect using the helm chart which only receives the configuration to the database using the string.

5

I managed to resolve this issue. thanks to this post, I going to elaborate a bit more on how I resolved this using the Helm chart.

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.