Multiprocessing issue with database connections

[ncroese]

When using multiple gunicorn workers (as is the default for the docker image) database connections start to fail:

2023-04-20T09:33:42.807128989Z 2023-04-20 09:33:42,806 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-04-20T09:33:42.807481643Z {"event": "BEGIN (implicit)", "logger": "sqlalchemy.engine.Engine", "level": "info", "timestamp": "2023-04-20T09:33:42.807060Z"}
2023-04-20T09:33:42.812778168Z 2023-04-20 09:33:42,812 INFO sqlalchemy.engine.Engine SELECT accounts.id AS accounts_id, accounts.username AS accounts_username, accounts.password_hash AS accounts_password_hash, accounts.is_admin AS accounts_is_admin, accounts._permissions AS accounts__permissions 
2023-04-20T09:33:42.812951070Z FROM accounts 
2023-04-20T09:33:42.812963191Z WHERE accounts.username = %(username_1)s
2023-04-20T09:33:42.812871689Z {"event": "SELECT accounts.id AS accounts_id, accounts.username AS accounts_username, accounts.password_hash AS accounts_password_hash, accounts.is_admin AS accounts_is_admin, accounts._permissions AS accounts__permissions \nFROM accounts \nWHERE accounts.username = %(username_1)s", "logger": "sqlalchemy.engine.Engine", "level": "info", "timestamp": "2023-04-20T09:33:42.812697Z"}
2023-04-20T09:33:42.813103192Z 2023-04-20 09:33:42,812 INFO sqlalchemy.engine.Engine [generated in 0.00100s] {'username_1': 'test'}
2023-04-20T09:33:42.813249494Z {"event": "[generated in 0.00100s] {'username_1': 'test'}", "logger": "sqlalchemy.engine.Engine", "level": "info", "timestamp": "2023-04-20T09:33:42.813069Z"}
2023-04-20T09:33:42.819252777Z 2023-04-20 09:33:42,819 INFO sqlalchemy.pool.impl.QueuePool Invalidate connection <connection object at 0x7fe6f0f82040; dsn: 'user=taxii password=xxx dbname=taxii_auth host=xxx port=5432', closed: 2> (reason: OperationalError:SSL error: decryption failed or bad record mac
2023-04-20T09:33:42.819309488Z )
2023-04-20T09:33:42.819545121Z {"event": "Invalidate connection <connection object at 0x7fe6f0f82040; dsn: 'user=taxii password=xxx dbname=taxii_auth host=xxx port=5432', closed: 2> (reason: OperationalError:SSL error: decryption failed or bad record mac\n)", "logger": "sqlalchemy.pool.impl.QueuePool", "level": "info", "timestamp": "2023-04-20T09:33:42.819431Z"}
2023-04-20T09:33:42.820971078Z {"event": "Exception on /services/collection-management [POST]", "exc_info": ["<class 'sqlalchemy.exc.OperationalError'>", "OperationalError('(psycopg2.OperationalError) SSL error: decryption failed or bad record mac\\n')", "<traceback object at 0x7fe6f0ebea00>"], "logger": "opentaxii.middleware", "level": "error", "timestamp": "2023-04-20T09:33:42.820238Z"}
2023-04-20T09:33:42.821690947Z {"event": "Error handling request /services/collection-management", "exc_info": ["<class 'AttributeError'>", "AttributeError('taxiiserver')", "<traceback object at 0x7fe6f0eac440>"], "logger": "gunicorn.error", "level": "error", "timestamp": "2023-04-20T09:33:42.821568Z"}

It seems that when using 2 workers the connections fail for one worker and work fine for the other.

My configuration is pretty standard:

domain: "xxx"

support_basic_auth: yes
return_server_error_details: no

auth_api:
  class: opentaxii.auth.sqldb.SQLDatabaseAPI
  parameters:
    db_connection: postgresql://taxii:xxx@xxx:5432/taxii_auth
    create_tables: yes
    secret: xxx
    token_ttl_secs: 3600
    echo: true
    echo_pool: true

taxii1:
  save_raw_inbox_messages: yes
  xml_parser_supports_huge_tree: yes
  count_blocks_in_poll_responses: yes
  unauthorized_status: UNAUTHORIZED
  hooks:
  persistence_api:
    class: opentaxii.persistence.sqldb.SQLDatabaseAPI
    parameters:
      db_connection: postgresql://taxii:xxx@xxx:5432/taxii_data
      create_tables: yes
      echo: true
      echo_pool: true

taxii2:

logging:
  opentaxii: debug
  root: debug

I'm running it using docker run -p 9000:9000 -v "$(pwd)"/config.yml:/input/opentaxii.yml eclecticiq/opentaxii:0.9.3.
My database is a standard postgres AWS RDS database.

It seems this issue is at least present in version 0.2.1 and 0.9.3. I would think that everyone using the docker images would run into this problem, but since there are no other issues about this maybe I'm doing something wrong?

[eric-eclecticiq]

OperationalError:SSL error: decryption failed or bad record mac

It seems legit: https://stackoverflow.com/questions/64995178/decryption-failed-or-bad-record-mac-in-multiprocessing

A DB connection is known to not survive fork. It must be disposed before the fork and recreated after.