Enable KeycloakDS Datasource validations

[rmarting]

SUMMARY

Keycloak uses a database to persist all the information of the system, this database is connected by a datasource defined in the underlying EAP platform without any kind of sanity or check process. It is a good practice to enable some validations in the datasource for a healhty life cycle and avoid issues in runtime when the connections are not running successfuly or they are suffering some issues.

References:

• https://access.redhat.com/documentation/en-us/red_hat_jboss_enterprise_application_platform/7.4/html/configuration_guide/datasource_management#configure_database_connection_validation_settings
• https://access.redhat.com/solutions/156103

This feature request wants to provide a way to setup this kind of validations in the KeycloakDS datasource as part of the collection, and it is open for discussion with the community.

Implementation Approach

For example a way of implementation could be defining a set of new properties to enable these properties, for example with something similar to:

- name: Playbook for Red Hat SSO Hosts
  hosts: sso
  vars_files:
    - ../vars/variables.yml
  collections:
    - redhat.sso
  tasks:
    - name: Include SSO role
      ansible.builtin.include_role:
        name: redhat.sso.sso
      vars:
        sso_offline_install: True
        sso_apply_patches: "{{ rh_sso_apply_patches }}"
        eap_properties:
          - name: property1-name
            value: property1-value
          - name: property2-name
            value: property2-value
        datasource:
          validation:
            enabled: true
            valid_connection_sql: Select 1

A new set of variables under a new datasource group are proposed, such as:

• validation.enabled - boolean variable to enable this feature
• validation.valid_connection_sql- String variable to identify a SQL sentence to check the connectio

The implementation could be done updating the standalone.xml.j2, standalone-ha.xml.j2 templates, or include some extra tasks using the cli enabling this feature.

The following tasks using the CLI could be an example of implementation:

  #
  tasks:
    - name: 'Setup Database Connection Validator - Validate on match enabled'
      ansible.builtin.command: >
        {{ keycloak.cli_path }} --connect --command='/subsystem=datasources/data-source=KeycloakDS:write-attribute(name=validate-on-match,value=false)'
      become: true

    - name: 'Setup Database Connection Validator - Valid Connection SQL Check '
      ansible.builtin.command: >
        {{ keycloak.cli_path }} --connect --command='/subsystem=datasources/data-source=KeycloakDS:write-attribute(name=check-valid-connection-sql,value={{ valid_connection_sql }})'
      changed_when: false
      register: cli_result

    - name: 'Setup Database Connection Validator - Background validation enabled'
      ansible.builtin.command: >
        {{ keycloak.cli_path }} --connect --command='/subsystem=datasources/data-source=KeycloakDS:write-attribute(name=background-validation,value=true)'
      changed_when: false
      register: cli_result

    - name: 'Setup Database Connection Validator - Shared prepared statements disabled'
      ansible.builtin.command: >
        {{ keycloak.cli_path }} --connect --command='/subsystem=datasources/data-source=KeycloakDS:write-attribute(name=share-prepared-statements,value=false)'
      changed_when: false
      register: cli_result

NOTE: Sorry, I am not an Ansible expert, sorry for any typo in the Ansible syntax.

ISSUE TYPE

• Feature Idea

[rmarting]

Good job @guidograzioli !!! Thank you so much for this contribution!!! It will help many of our customers.

Is it an ETA for the next release? Thanks

[guidograzioli]

on galaxy pretty soon, on hub next week :)