Can't pass ssl_context for pg8000

Question

Can't pass ssl_context for pg8000

Closed this issue 6 months ago · 9 comments

https://github.com/tlocke/pg8000?tab=readme-ov-file#connect-to-postgresql-over-ssl

When you are using pg8000 and want to connect using custom SSL, you need to be able to add SSL context as one of the engine options.

As far as I can see, the sqlalchemy-celery-beat does not support this currently

Answer 1 · 2024-03-22T02:03:44.000Z

You can pass keyword arguments to the session factory function

def session_factory(self, dburi, schema=None, **kwargs):

so the correct way to pass SSL context would be

ssl_context = ...
session_manager.session_factory(uri, connect_args={'ssl_context': ssl_context})

this is really how SQLAlchemy does it here

let me know if you tried this or there is more to it than this.

Answer 2 · 2024-03-22T08:36:10.000Z

Thanks @farahats9!

I didn't use that approach since I'm using the Flask config setup approach -

    CELERY = {  # noqa: RUF012
        "broker_url": os.environ.get("CELERY_BROKER_URL"),
        "result_backend": f"db+{DATABASE_URL}?{get_celery_ssl_context()}",
        "redis_backend_use_ssl": True,
        "task_ignore_result": True,
        "broker_connection_retry_on_startup": True,
        "beat_schedule": {
            "check-for-unscheduled-retention-emails-every-morning": {
                "task": "app.jobs.retention_email.check_for_unscheduled_retention_emails",
                "schedule": crontab(hour=8, minute=0),
            },
        },
        "beat_scheduler": "sqlalchemy_celery_beat.schedulers:DatabaseScheduler",
        "beat_dburi": f"{DATABASE_URL}?{get_celery_ssl_context()}"
    }
...
   
def celery_init_app(app: Flask) -> Celery:
    class FlaskTask(Task):
        def __call__(self, *args: object, **kwargs: object) -> object:
            with app.app_context():
                return self.run(*args, **kwargs)

    celery_app = Celery(app.name, task_cls=FlaskTask)
    celery_app.config_from_object(app.config["CELERY"])
    celery_app.set_default()
    app.extensions["celery"] = celery_app
    return celery_app

Should we move the periodic tasks out into a separate script called only by the celery-beat process in sqlalchemy-celery-beat? So, something akin to this?

# make_celery_beat.py
from app.app import create_app
from config import get_ssl_context
from sqlalchemy_celery_beat.models import PeriodicTask, CrontabSchedule
from sqlalchemy_celery_beat.session import SessionManager

flask_app = create_app()
celery_app = flask_app.extensions["celery"]

session_manager.session_factory(app.config["CELERY"]["beat_dburi"], connect_args={'ssl_context': get_ssl_context()})
schedule = session.query(CrontabSchedule).filter_by(hour=8, minute=0, timezone='UTC').first()
if not schedule:
  schedule = CrontabSchedule(hour=8, minute=0, timezone='UTC')
  session.add(schedule)
  session.commit()
  task = PeriodicTask(
    schedule_model=schedule
    name='Importing contacts',
    task='app.jobs.retention_email.check_for_unscheduled_retention_emails',
  )
  session.add(task)
  session.commit()

Answer 3 · 2024-03-22T09:07:49.000Z

Thank you for providing an example, I see in your code you are using this config for the result backend:

"result_backend": f"db+{DATABASE_URL}?{get_celery_ssl_context()}",

and you are using the same in beat_dburi

"beat_dburi": f"{DATABASE_URL}?{get_celery_ssl_context()}"

but without db+ at the beginning which is correct. the code for the database connection is the same in celery database backend as it is in sqlalchemy-celery-beat. so if the result database backend is running correctly for you, there is no reason for beat schedule backend not to run (since you are using the same db connection string).

I could add something like database_engine_options from the celery database backend but I don't think it is necessary for your code to run.

If the problem is in the task not being created, please note the task actually gets created when the celery beat process is started, not when you run flask or celery worker. i.e when you do celery -A app.app:celery_app beat -S tasks:DatabaseScheduler -l info.
Could you please send some log of what you saw that made you think the problem is in the SSL context part based on what I explained above.

Answer 4 · 2024-03-22T09:18:27.000Z

Thank you for the feedback!

Here's the error log when I run purely from configuration:

Mar 22 09:15:54 ip-172-31-18-86 celery[1308140]: [2024-03-22 09:15:54,857] INFO in loggers: My Flask app startup
Mar 22 09:15:55 ip-172-31-18-86 celery[1308140]: celery beat v5.3.6 (emerald-rush) is starting.
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]: Traceback (most recent call last):
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/bin/celery", line 8, in <module>
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     sys.exit(main())
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/celery/__main__.py", line 15, in main
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     sys.exit(_main())
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/celery/bin/celery.py", line 236, in main
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return celery(auto_envvar_prefix="CELERY")
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/click/core.py", line 1157, in __call__
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return self.main(*args, **kwargs)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/click/core.py", line 1078, in main
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     rv = self.invoke(ctx)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return _process_result(sub_ctx.command.invoke(sub_ctx))
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return ctx.invoke(self.callback, **ctx.params)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/click/core.py", line 783, in invoke
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return __callback(*args, **kwargs)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/click/decorators.py", line 33, in new_func
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return f(get_current_context(), *args, **kwargs)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/celery/bin/base.py", line 134, in caller
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return f(ctx, *args, **kwargs)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/celery/bin/beat.py", line 72, in beat
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return beat().run()
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/celery/apps/beat.py", line 84, in run
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/celery/apps/beat.py", line 104, in start_scheduler
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     print(self.banner(service))
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/celery/apps/beat.py", line 126, in banner
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     c.reset(self.startup_info(service))),
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/celery/apps/beat.py", line 136, in startup_info
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     scheduler = service.get_scheduler(lazy=True)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/celery/beat.py", line 668, in get_scheduler
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return symbol_by_name(self.scheduler_cls, aliases=aliases)(
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy_celery_beat/schedulers.py", line 293, in __init__
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     session_manager.prepare_models(self.engine, schema=self.schema)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy_celery_beat/session.py", line 106, in prepare_models
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     ModelBase.metadata.create_all(engine)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 5828, in create_all
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     bind._run_ddl_visitor(
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3242, in _run_ddl_visitor
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     with self.begin() as conn:
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/usr/lib/python3.10/contextlib.py", line 135, in __enter__
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return next(self.gen)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3232, in begin
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     with self.connect() as conn:
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3268, in connect
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return self._connection_cls(self)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 145, in __init__
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     self._dbapi_connection = engine.raw_connection()
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 3292, in raw_connection
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 452, in connect
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return _ConnectionFairy._checkout(self)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 1269, in _checkout
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     fairy = _ConnectionRecord.checkout(pool)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 716, in checkout
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     rec = pool._do_get()
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/pool/impl.py", line 284, in _do_get
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return self._create_connection()
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 393, in _create_connection
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return _ConnectionRecord(self)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 678, in __init__
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     self.__connect()
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 902, in __connect
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     with util.safe_reraise():
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     raise exc_value.with_traceback(exc_tb)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/pool/base.py", line 898, in __connect
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     self.dbapi_connection = connection = pool._invoke_creator(self)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/engine/create.py", line 637, in connect
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return dialect.connect(*cargs, **cparams)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:   File "/home/gosolid/apps/green/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 616, in connect
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]:     return self.loaded_dbapi.connect(*cargs, **cparams)
Mar 22 09:15:57 ip-172-31-18-86 celery[1308140]: TypeError: connect() got an unexpected keyword argument 'ssl_cert_reqs'
Mar 22 09:16:02 ip-172-31-18-86 celery[1308201]: [2024-03-22 09:16:02,963] INFO in loggers: My Flask app startup

For context - here's the get_ssl_context()

def get_celery_ssl_context():
    if os.environ.get("FLASK_ENV") == "production":
        return "ssl_cert_reqs=required&ssl_ca_certs=%2Fpath%2Fto%2Fbundle.pem"
    else:
        return ""

I also tried with simply ssl_ca_certs, but had similar error

Answer 5 · 2024-03-22T10:17:29.000Z

please take a look at the PR #10 and I would appreciate if you can verify it fixed your issue, you need to pass the ssl context like this:

ssl_context = ssl.create_default_context()
ssl_context.verify_mode = ssl.CERT_REQUIRED
ssl_context.load_verify_locations('/path/to/bundle.pem')

# and in your celery config:
CELERY = {
...
"beat_engine_options": {"ssl_context": ssl_context}
}

Answer 6 · 2024-03-22T13:30:06.000Z

That was fast! Thanks! I'll give it a try tomorrow when I get back to computer

Answer 7 · 2024-03-23T17:14:07.000Z

This worked, and that's amazing. Thank you!

Once you merge it, I'm happy to support and write readme improvements if you wish

Answer 8 · 2024-03-23T17:50:20.000Z

I am glad it worked for you, I just need to write some tests and docs for this new option then I will make a release and close this issue

Answer 9 · 2024-06-06T17:24:15.000Z

this should be fixed now in #10 closing issue.