Give a way to connect to external postgres database with client SSL credentials

[mqufflc]

Please confirm the following

• [X] I agree to follow this project's code of conduct.
• [X] I have checked the current issues for duplicates.
• [X] I understand that AWX Operator is open source software provided for free and that I might not receive a timely response.

Feature Summary

I haven't found a way to specify a ssl private key and certificate in order to connect to an external database.

My use case is to use a cloud sql instance hosted on GCP and to enforce the use of SSL to connect to the database. Doing so disables the possibility to connect with a username and password.

I have enabled the option on a running awx instance deployed with awx-operator. AWX was then unable to connect to the database.

[mqufflc]

Here is my attempt to implement it : 57b007902f70b460409e49ee4f5f6390b45f6392. But I didn't succeed. I cannot connect to the database because the key needs to have less permissions on the pod.

The error during installer playbook :

TASK [installer : Check if there are any super users defined.] *****************
task path: /opt/ansible/roles/installer/tasks/initialize_django.yml:2
fatal: [localhost]: FAILED! => {"changed": true, "rc": 1, "return_code": 1, "stderr": "Traceback (most recent call last):\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 219, in ensure_connection\n    self.connect()\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner\n    return func(*args, **kwargs)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 200, in connect\n    self.connection = self.get_new_connection(conn_params)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner\n    return func(*args, **kwargs)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/postgresql/base.py\", line 187, in get_new_connection\n    connection = Database.connect(**conn_params)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/psycopg2/__init__.py\", line 126, in connect\n    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)\npsycopg2.OperationalError: private key file \"/etc/postgresql/tls/client/client.key\" has group or world access; permissions should be u=rw (0600) or less\n\n\nThe above exception was the direct cause of the following exception:\n\nTraceback (most recent call last):\n  File \"/usr/bin/awx-manage\", line 8, in <module>\n    sys.exit(manage())\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/awx/__init__.py\", line 185, in manage\n    if (connection.pg_version // 10000) < 12:\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/connection.py\", line 15, in __getattr__\n    return getattr(self._connections[self._alias], item)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/functional.py\", line 48, in __get__\n    res = instance.__dict__[self.name] = self.func(instance)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/postgresql/base.py\", line 329, in pg_version\n    with self.temporary_connection():\n  File \"/usr/lib64/python3.9/contextlib.py\", line 119, in __enter__\n    return next(self.gen)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 603, in temporary_connection\n    with self.cursor() as cursor:\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner\n    return func(*args, **kwargs)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 259, in cursor\n    return self._cursor()\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 235, in _cursor\n    self.ensure_connection()\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner\n    return func(*args, **kwargs)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 219, in ensure_connection\n    self.connect()\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/utils.py\", line 90, in __exit__\n    raise dj_exc_value.with_traceback(traceback) from exc_value\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 219, in ensure_connection\n    self.connect()\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner\n    return func(*args, **kwargs)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 200, in connect\n    self.connection = self.get_new_connection(conn_params)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner\n    return func(*args, **kwargs)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/postgresql/base.py\", line 187, in get_new_connection\n    connection = Database.connect(**conn_params)\n  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/psycopg2/__init__.py\", line 126, in connect\n    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)\ndjango.db.utils.OperationalError: private key file \"/etc/postgresql/tls/client/client.key\" has group or world access; permissions should be u=rw (0600) or less\n\n", "stderr_lines": ["Traceback (most recent call last):", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 219, in ensure_connection", "    self.connect()", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner", "    return func(*args, **kwargs)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 200, in connect", "    self.connection = self.get_new_connection(conn_params)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner", "    return func(*args, **kwargs)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/postgresql/base.py\", line 187, in get_new_connection", "    connection = Database.connect(**conn_params)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/psycopg2/__init__.py\", line 126, in connect", "    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)", "psycopg2.OperationalError: private key file \"/etc/postgresql/tls/client/client.key\" has group or world access; permissions should be u=rw (0600) or less", "", "", "The above exception was the direct cause of the following exception:", "", "Traceback (most recent call last):", "  File \"/usr/bin/awx-manage\", line 8, in <module>", "    sys.exit(manage())", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/awx/__init__.py\", line 185, in manage", "    if (connection.pg_version // 10000) < 12:", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/connection.py\", line 15, in __getattr__", "    return getattr(self._connections[self._alias], item)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/functional.py\", line 48, in __get__", "    res = instance.__dict__[self.name] = self.func(instance)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/postgresql/base.py\", line 329, in pg_version", "    with self.temporary_connection():", "  File \"/usr/lib64/python3.9/contextlib.py\", line 119, in __enter__", "    return next(self.gen)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 603, in temporary_connection", "    with self.cursor() as cursor:", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner", "    return func(*args, **kwargs)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 259, in cursor", "    return self._cursor()", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 235, in _cursor", "    self.ensure_connection()", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner", "    return func(*args, **kwargs)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 219, in ensure_connection", "    self.connect()", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/utils.py\", line 90, in __exit__", "    raise dj_exc_value.with_traceback(traceback) from exc_value", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 219, in ensure_connection", "    self.connect()", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner", "    return func(*args, **kwargs)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/base/base.py\", line 200, in connect", "    self.connection = self.get_new_connection(conn_params)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/utils/asyncio.py\", line 33, in inner", "    return func(*args, **kwargs)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/postgresql/base.py\", line 187, in get_new_connection", "    connection = Database.connect(**conn_params)", "  File \"/var/lib/awx/venv/awx/lib64/python3.9/site-packages/psycopg2/__init__.py\", line 126, in connect", "    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)", "django.db.utils.OperationalError: private key file \"/etc/postgresql/tls/client/client.key\" has group or world access; permissions should be u=rw (0600) or less", ""], "stdout": "", "stdout_lines": []}
...ignoring

TASK [installer : Create super user via Django if it doesn't exist.] ***********
task path: /opt/ansible/roles/installer/tasks/initialize_django.yml:16
fatal: [localhost]: FAILED! => {"censored": "the output has been hidden due to the fact that 'no_log: true' was specified for this result", "changed": true}

PLAY RECAP *********************************************************************
localhost                  : ok=61   changed=1    unreachable=0    failed=1    skipped=61   rescued=0    ignored=1

[fosterseth]

@rooftopcellist does operator support connecting to external db with custom CA and cert? i see in docs you can specify sslmode, but don't see where you can provide private key and cert to use in ssl verification

[fosterseth]

@mqufflc were you able to get this working?

[mqufflc]

I was able to start an AWX instance with these modifications : 3b5ae73dc595a94565c70b8cda3db3e56a873978 The molecule test is in success but I haven't tested the backup and restore playbooks.

It should be noted that I tested this with a local postgresql instance, note a GCP Cloud SQL instance. As Cloud SQL enforce the use of both the client certificate and the user password, I'm not sure yet if this would work on GCP.

[mabashian]

@fosterseth @rooftopcellist @mqufflc https://github.com/ansible/awx-operator/commit/3b5ae73dc595a94565c70b8cda3db3e56a873978 is this something we want to look at rolling into the project? Can this issue be closed or do we want to leave it open?

[pablo-suazo]

I have the same issue, I am unable to connect an AWX instance to an PostgreSQL instance runnin on CloudSQL on GCP with a client certificate and SSL enforced, @mabashian could you merge @mqufflc modifications?

[mqufflc]

I have created a PR (#1385) with the modifications I have wrote a few months ago. The molecule tests don't show any error but I have not checked if the authentication to a postgreSQL database with client certificate authentication is still working. I will try to do that in the following days. Backup and restore still needs to be implemented if what I wrote still works.

[jravetch]

@mqufflc I too am trying to use GCP cloud sql with AWX in GKE. Would #1385 fix this? Another option is to use cloud sql proxy which would mean running the cloud sql as a side car for the pods and connecting via 127.0.0.1.