ARA-server communication with mysql over ssl

[stefan-marc]

What component is this about ?

ara-server

What is your ARA installation like ?

ara: 1.4.2 Running in a docker container hosted in Azure
ansible: 2.9.6
django.db.backends.mysql
Database: MariaDB in Azure

What is happening ?

I have ara-server setup to communicate with MariaDB. The settings used by ara to communicate with the db are declared using env variables.

      ARA_DATABASE_ENGINE: django.db.backends.mysql
      ARA_DATABASE_HOST: "{{ ara_db_fqdn }}"
      ARA_DATABASE_NAME: ara
      ...

Everything works fine, until I enable SSL on MariaDB, at which point ARA crashes. Telling me that I need to configure SSL.

Looking over the ARA documentation, I don't see a clear way of doing the SSL setup for ARA.

Looking over the Azure documentation on how to configure SSL connectivity between applications and MariaDB
https://docs.microsoft.com/en-us/azure/mariadb/howto-configure-ssl
I need to:

download the BaltimoreCyberTrust pem
add the following to the config files of ARA
Using Python (PyMySQL) (ssl={'ca': '/var/www/html/BaltimoreCyberTrustRoot.crt.pem'})

The problem is that there's no environment variable or field in the settings.yaml to load the certificate.

Also the Options filed needed for setting-up the SSL connection is missing from the settings.py in Ara.
/usr/local/lib/python3.7/site-packages/ara/server/settings.py
Original

DATABASES = {
     "default": {
         "ENGINE": DATABASE_ENGINE,
         "NAME": DATABASE_NAME,
         "USER": DATABASE_USER,
         "PASSWORD": DATABASE_PASSWORD,
         "HOST": DATABASE_HOST,
         "PORT": DATABASE_PORT,
         "CONN_MAX_AGE": DATABASE_CONN_MAX_AGE,
     }
 }

To get this working I modified the file to include the Options field and everything works as expected.

        "PORT": DATABASE_PORT,                                                                                   
        "OPTIONS": {                                                                                             
            'ssl': {                                                                                             
                'ca': '/path/to/cert/BaltimoreCyberTrustRoot.crt.pem',                                         
            },                                                                                                   
        },                                                                                                       
        "CONN_MAX_AGE": DATABASE_CONN_MAX_AGE, 

I'm not ok with this solution... am I missing something? Can this be done using existing settings or variables? If not it would be great if the Options field would be included in the settings.yaml and retrievable via env variables.
Thanks!

[dmsimard]

Hey @smarcu92 and thanks for the issue.

You're right, we should add a DATABASE_OPTIONS field and then pass that in.
It wasn't there because I guess no one has had that use case yet but it's totally valid.

Would you like to try a patch that implements this change ? See: https://review.opendev.org/#/c/746735/
To install from source (master+patch):

git clone https://github.com/ansible-community/ara
pushd ara
git fetch https://review.opendev.org/recordsansible/ara refs/changes/35/746735/4 && git checkout FETCH_HEAD
popd
pip install ./ara[server]

Edit: updated patchset, there was an issue with the original patch.

[stefan-marc]

Hey @dmsimard,
Thanks for your quick answer + solution! I'll have a look this evening and let you know what the results are.

Thanks!

[stefan-marc]

Hey @dmsimard,

Sorry for the wait.
I tested the solution provided, however I'm encountering the following error:
I wasn't too sure how to provide the OPTIONS value as a env var since I know ARA is picky when it comes to ''.

export ARA_DATABASE_OPTIONS={\"ssl\":{\"ca\":\"/path/to/cert/BaltimoreCyberTrustRoot.crt.pem\"}}

ara-manage diffsettings

DATABASES = {'default': {'ENGINE': 'django.db.backends.mysql', 'NAME': 'ara', 'USER': 'xxxxxx', 'PASSWORD': 'xxxxxx', 'HOST': 'xxxxxx, 'PORT': None, 'CONN_MAX_AGE': 0, 'OPTIONS': '{"ssl":{"ca":"/path/to/cert/BaltimoreCyberTrustRoot.crt.pem"}}', 'ATOMIC_REQUESTS': False, 'AUTOCOMMIT': True, 'TIME_ZONE': None, 'TEST': {'CHARSET': None, 'COLLATION': None, 'NAME': None, 'MIRROR': None}}}

I get the following error:

root@e3b658c5c720:/# ara-manage migrate
/usr/local/lib/python3.8/dist-packages/tzlocal/unix.py:158: UserWarning: Can not find any timezone configuration, defaulting to UTC.
  warnings.warn('Can not find any timezone configuration, defaulting to UTC.')
[ara] Using settings file: /root/.ara/server/settings.yaml
Traceback (most recent call last):
  File "/usr/local/bin/ara-manage", line 8, in <module>
    sys.exit(main())
  File "/usr/local/lib/python3.8/dist-packages/ara/server/__main__.py", line 58, in main
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/__init__.py", line 381, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/__init__.py", line 375, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/base.py", line 323, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/base.py", line 361, in execute
    self.check()
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/base.py", line 387, in check
    all_issues = self._run_checks(
  File "/usr/local/lib/python3.8/dist-packages/django/core/management/commands/migrate.py", line 64, in _run_checks
    issues = run_checks(tags=[Tags.database])
  File "/usr/local/lib/python3.8/dist-packages/django/core/checks/registry.py", line 72, in run_checks
    new_errors = check(app_configs=app_configs)
  File "/usr/local/lib/python3.8/dist-packages/django/core/checks/database.py", line 10, in check_database_backends
    issues.extend(conn.validation.check(**kwargs))
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/mysql/validation.py", line 9, in check
    issues.extend(self._check_sql_mode(**kwargs))
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/mysql/validation.py", line 13, in _check_sql_mode
    with self.connection.cursor() as cursor:
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/base/base.py", line 256, in cursor
    return self._cursor()
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/base/base.py", line 233, in _cursor
    self.ensure_connection()
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/base/base.py", line 217, in ensure_connection
    self.connect()
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/base/base.py", line 194, in connect
    conn_params = self.get_connection_params()
  File "/usr/local/lib/python3.8/dist-packages/django/db/backends/mysql/base.py", line 211, in get_connection_params
    options = settings_dict['OPTIONS'].copy()
AttributeError: 'str' object has no attribute 'copy'

Let me know what I should try next.
Thanks!

[dmsimard]

Hey @smarcu92 and thanks for testing it out.

It looks like the variable is being parsed as a string instead of a dictionary.
Could you see if it works in the settings.yaml file instead ?

Something like:

default:
  # [...]
  DATABASE_CONN_MAX_AGE: 60
  DATABASE_ENGINE: django.db.backends.mysql
  DATABASE_HOST: localhost
  DATABASE_NAME: ara
  DATABASE_OPTIONS:
    ssl:
      ca: /path/to/cert/BaltimoreCyberTrustRoot.crt.pem
  DATABASE_PASSWORD: password
  DATABASE_PORT: 3306
  DATABASE_USER: ara
  # [...]

[stefan-marc]

Hey @dmsimard,

Using the settings.yaml worked.

root@f4cbd5bdff16:~/.ara/server# ara-manage migrate
/usr/local/lib/python3.8/dist-packages/tzlocal/unix.py:158: UserWarning: Can not find any timezone configuration, defaulting to UTC.
  warnings.warn('Can not find any timezone configuration, defaulting to UTC.')
[ara] Using settings file: /root/.ara/server/settings.yaml
Operations to perform:
  Apply all migrations: admin, api, auth, contenttypes, db, sessions
Running migrations:
  Applying contenttypes.0001_initial... OK
  Applying auth.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying api.0001_initial... OK

Is there a way we could pass it as an env variable?
Thanks!

[dmsimard]

Hey @smarcu92, I've looked at the dynaconf documentation and they mention that the following should work:

DYNACONF_DICT={key="abc",val=123}

I couldn't get that to work locally with a similar string issue though.

I did get the following to work, however:

export ARA_DATABASE_OPTIONS='@json {"ssl": {"ca": "/etc/ssl/foo.pem"}}'

It'd be a good idea to add that to our docs as an example.

[stefan-marc]

Hey @dmsimard,

Thanks for the solution! It works perfectly!
Adding that to the docs would bring great value.

Any idea when this will be released?
Thanks again!

[dmsimard]

It'll be released in 1.5 but will merge in master soon so you can deploy from master until it's released.

When docs are updated, they'll include:

I don't know when 1.5 will be released yet but I'd like to tag a beta after fixing these issues: https://github.com/ansible-community/ara/milestone/1. Need to iterate a bit on the new CLI too.