Bug: ssl error with postgres and celery

[Valentin Kleibel]

Hi Thomas,

> I don't have answers but several questions.
> 
> Can you say more about how you set up the database connection? Do you do
> anything special here? Why is it different with SSL than without?
About the setup:
the pg_hba.conf for the database contains the line:
hostssl all         all         1.2.3.0/24          md5
which enforces the automatic and transparent use of ssl connections to 
our database server (that is in the subnet 1.2.3.0/16). To reproduce 
this on a local database you could set:
hostssl all         all         127.0.0.1/32          md5
to use ssl with it.
I'm sure of one difference: with SSL a connection reused by a forked 
process will for sure lead to an error because the connection is 
stateful and will, for security reasons, fail if reused.

> As far as I can see you would just need to give some extra parameters in
> the sqlalchemy.url setting in your ini file.

We were looking through all documented sqlalchemy.url settings and 
couldn't find anything of use here.

> But your mail seems to suggest that you have a connection created upfront
> and would like this one to be reused?

We do not want to reuse a previously created connection but this is how 
the celery workers seem to behave.

> Which are the uwsgi-related settings in your ini file? The 'lazy=true'
> setting is not a choice for Kallithea and should be enabled in any case.
> Our uwsgi template for ini files gives this comment as clarification for
> 'lazy=true': "App *must* be loaded in workers - db connections can't be
> shared"

The only related setting is lazy=true. As you are saying, the database 
connections can't be shared. this can be achieved in multiple ways and 
one of them (although maybe not the most efficient one) is to use the 
lazy setting in uwsgi.
access through uwsgi does work without issues in our setup.

> Did you use Kallithea in this mode successfully before, perhaps on an older
> version? Your mail seems to suggest that but I'm not sure.

Yes, we did use Kallithea before successfully with this setup, with 
version 0.4.1. We think the problem arises with the upgrade due to the 
way celery workers seem to behave with the new prefork worker model. 
This change came with the upgrade of  celery to version 4.

> Note: I found this related question:
> https://stackoverflow.com/questions/51466007/how-to-use-psycopg2-properly-in-a-prefork-celery-environment
> but it does not involve sqlalchemy and does not really give immediate
> solutions.

While looking around for solutions we found this blogpost:
https://virtualandy.wordpress.com/2019/09/04/a-fix-for-operationalerror-psycopg2-operationalerror-ssl-error-decryption-failed-or-bad-record-mac/
Which is about uwsgi but also describes an alternative solution to 
setting lazy=true which is to call engine.dispose in order to make sure 
a new connection is used.
This is the same solution we also found in the sqalchemy documentation 
in the section about connection pools use with mutiprocessing or forked 
processes:
https://docs.sqlalchemy.org/en/13/core/pooling.html#using-connection-pools-with-multiprocessing-or-os-fork

We implemented this in kallithea/lib/celerylib/__init__.py for the 
celery workers.
Attached is a diff for the current stable release which works well in 
our setup, although we are not sure if it has any unwanted side effects 
we just didn't encounter by now.

Cheers,
Valentin
-------------- next part --------------
A non-text attachment was scrubbed...
Name: celery_db_connections.diff
Type: text/x-patch
Size: 436 bytes
Desc: not available
URL: <http://lists.sfconservancy.org/pipermail/kallithea-general/attachments/20201104/67a3d2fe/attachment-0001.bin>