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
diff -r c387989f868f kallithea/lib/celerylib/__init__.py
--- a/kallithea/lib/celerylib/__init__.py Wed Oct 28 14:58:18 2020 +0100
+++ b/kallithea/lib/celerylib/__init__.py Wed Nov 04 11:34:04 2020 +0100
@@ -124,6 +124,7 @@
def dbsession(func):
def __wrapper(func, *fargs, **fkwargs):
try:
+ meta.Base.metadata.bind.dispose()
ret = func(*fargs, **fkwargs)
return ret
finally:
_______________________________________________
kallithea-general mailing list
[email protected]
https://lists.sfconservancy.org/mailman/listinfo/kallithea-general
