[stable-only] Split CA of ProxySQL internal TLS and backend TLS

ProxySQL frontend TLS (database internal TLS) and backend TLS (database
backend TLS) expects different CA if users use chain of certificates
(e.g. use of intermediate certificates).

Frontend TLS expects the intermediate certificate that signed the leaf
certificate as a CA file, whereas backend TLS expects root CA.

To fix, users are now required to create a file named
``proxysql-ca.pem`` at ``kolla_certificates_dir``, which is a copy of
the intermediate certificate.

If your system is not affected by this bug, you're not required to
create the new file ``proxysql-ca.pem``.

Closes-bug: #2150449
Change-Id: I88911badb0d0801f79317df6681c571cc1c8cfee
Signed-off-by: Seunghun Lee <seunghun@stackhpc.com>

5 files changed, 49 insertions, 1 deletions

diff --git a/ansible/roles/certificates/tasks/generate.yml b/ansible/roles/certificates/tasks/generate.yml
index 7715487f3..3b130b473 100644
--- a/ansible/roles/certificates/tasks/generate.yml
+++ b/ansible/roles/certificates/tasks/generate.yml
@@ -59,6 +59,14 @@
     - letsencrypt_managed_certs == 'internal' or letsencrypt_managed_certs == ''
     - kolla_enable_tls_external | bool
 
+# ProxySQL frontend TLS (database internal TLS) needs separate CA file
+- name: Copy CA certificate for ProxySQL
+  ansible.builtin.copy:
+    src: "{{ certificates_root_dir }}/root.crt"
+    dest: "{{ kolla_certificates_dir }}/proxysql-ca.pem"
+    mode: "0660"
+  when: database_enable_tls_internal | bool
+
 - name: Copy external certificates for ProxySQL
   when:
     - database_enable_tls_internal | bool
diff --git a/ansible/roles/loadbalancer/tasks/config.yml b/ansible/roles/loadbalancer/tasks/config.yml
index cf1840478..543bea860 100644
--- a/ansible/roles/loadbalancer/tasks/config.yml
+++ b/ansible/roles/loadbalancer/tasks/config.yml
@@ -48,6 +48,18 @@
   become: true
   when: service | service_enabled_and_mapped_to_host
 
+- name: Check if separate database internal TLS CA certificate (proxysql-ca.pem) exists
+  vars:
+    service: "{{ loadbalancer_services['proxysql'] }}"
+  ansible.builtin.stat:
+    path: "{{ kolla_certificates_dir }}/proxysql-ca.pem"
+  delegate_to: localhost
+  register: proxysql_ca_file
+  changed_when: false
+  run_once: true
+  when:
+    - service | service_enabled_and_mapped_to_host
+
 - name: Remove mariadb.cfg if proxysql enabled
   vars:
     service: "{{ loadbalancer_services['keepalived'] }}"
diff --git a/ansible/roles/loadbalancer/tasks/copy-certs.yml b/ansible/roles/loadbalancer/tasks/copy-certs.yml
index 0bf3307f7..501e4ec05 100644
--- a/ansible/roles/loadbalancer/tasks/copy-certs.yml
+++ b/ansible/roles/loadbalancer/tasks/copy-certs.yml
@@ -31,6 +31,20 @@
   with_items:
     - "haproxy-internal.pem"
 
+- name: Copying over proxysql-ca.pem
+  vars:
+    service: "{{ loadbalancer_services['proxysql'] }}"
+  ansible.builtin.copy:
+    src: "{{ kolla_certificates_dir }}/proxysql-ca.pem"
+    dest: "{{ node_config_directory }}/proxysql/proxysql-ca.pem"
+    mode: "0660"
+  become: true
+  when:
+    - database_enable_tls_internal | bool
+    - service | service_enabled_and_mapped_to_host
+    - proxysql_ca_file.stat.exists
+    - not kolla_externally_managed_cert | bool
+
 - name: Copying over proxysql-cert.pem
   vars:
     service: "{{ loadbalancer_services['proxysql'] }}"
diff --git a/ansible/roles/loadbalancer/templates/proxysql/proxysql.json.j2 b/ansible/roles/loadbalancer/templates/proxysql/proxysql.json.j2
index f720f18e0..2dfcccc8d 100644
--- a/ansible/roles/loadbalancer/templates/proxysql/proxysql.json.j2
+++ b/ansible/roles/loadbalancer/templates/proxysql/proxysql.json.j2
@@ -52,7 +52,7 @@
         }{% endif %}
         {% if database_enable_tls_internal | bool %},
         {
-            "source": "{{ container_config_directory }}/ca-certificates/root.crt",
+            "source": "{{ container_config_directory }}/{{ 'proxysql-ca.pem' if proxysql_ca_file.stat.exists else 'ca-certificates/root.crt' }}",
             "dest": "/var/lib/proxysql/proxysql-ca.pem",
             "owner": "proxysql",
             "perm": "0600"
diff --git a/releasenotes/notes/fix-proxysql-database-internal-tls-b401e564ec39a633.yaml b/releasenotes/notes/fix-proxysql-database-internal-tls-b401e564ec39a633.yaml
new file mode 100644
index 000000000..fb3fb9db9
--- /dev/null
+++ b/releasenotes/notes/fix-proxysql-database-internal-tls-b401e564ec39a633.yaml
@@ -0,0 +1,14 @@
+---
+fixes:
+  - |
+    Fixes bug `LP#2150449 <https://bugs.launchpad.net/kolla-ansible/+bug/2150449>`__
+    where ProxySQL's database internal TLS fails when users use chain of
+    certificates (e.g. use of intermediate certificates).
+
+    Users who are affected by this bug needs to create a file
+    ``proxysql-ca.pem`` at ``certificates_internal_dir``.
+    This file needs to be a copy of the intermediate certificate (i.e.
+    certificate that signed database internal TLS)
+
+    Users who are not affected by this bug does not need to do
+    anything.