Arrow Flight SQL Endpoint TLS Compliance Gate

id: arrow-flight-sql-endpoint-tls-compliance-gate
namespace: company.team

description: |
  Probe an Apache Arrow Flight SQL endpoint with a trivial query, classify it HEALTHY,
  TLS_POLICY_VIOLATION or PROBE_FAILED from the real connection outcome and the real
  encryption setting used to reach it, and alert Slack the moment a consumer connects to a
  shared Flight SQL gateway without the encryption your policy requires.

inputs:
  - id: arrow_flight_host
    type: STRING
    displayName: Arrow Flight SQL host
    description: Hostname or IP of the Arrow Flight SQL endpoint.
    defaults: "localhost"

  - id: arrow_flight_port
    type: INT
    displayName: Arrow Flight SQL port
    description: Port the Arrow Flight SQL endpoint listens on.
    defaults: 31337

  - id: use_encryption
    type: BOOL
    displayName: Use encryption
    description: Whether this probe connects with TLS. Maps to the JDBC URL's
      useEncryption parameter, which defaults to true in the Arrow Flight SQL
      JDBC driver itself.
    defaults: true

  - id: disable_certificate_verification
    type: BOOL
    displayName: Disable certificate verification
    description: Only for a local test endpoint serving a self-signed certificate.
      Maps to the JDBC URL's disableCertificateVerification parameter. Leave
      false against any real endpoint with a certificate from a trusted CA.
    defaults: true

  - id: require_tls
    type: SELECT
    displayName: Require TLS
    description: Your own policy for this endpoint. When true, a probe that
      connected with use_encryption false is classified TLS_POLICY_VIOLATION
      instead of HEALTHY, regardless of whether the query itself succeeded.
    values:
      - "true"
      - "false"
    defaults: "true"

tasks:
  - id: probe_arrow_flight_endpoint
    type: io.kestra.plugin.jdbc.arrowflight.Query
    description: Runs a trivial, backend-agnostic query over the Arrow Flight SQL
      protocol to confirm the endpoint is reachable and authenticating
      correctly, independent of any particular dataset.
    allowFailure: true
    url: "jdbc:arrow-flight-sql://{{ inputs.arrow_flight_host }}:{{
      inputs.arrow_flight_port }}/?useEncryption={{ inputs.use_encryption
      }}&disableCertificateVerification={{
      inputs.disable_certificate_verification }}"
    username: "{{ secret('ARROWFLIGHT_USERNAME') }}"
    password: "{{ secret('ARROWFLIGHT_PASSWORD') }}"
    sql: "SELECT 1 AS probe"
    fetchType: FETCH_ONE

  - id: classify_endpoint_status
    type: io.kestra.plugin.core.debug.Return
    description: PROBE_FAILED when the probe did not return at all,
      TLS_POLICY_VIOLATION when the probe succeeded but used_encryption is false
      while require_tls is true, HEALTHY otherwise.
    format: "{{ outputs.probe_arrow_flight_endpoint is not defined ? 'PROBE_FAILED'
      : ( (not inputs.use_encryption and inputs.require_tls == 'true') ?
      'TLS_POLICY_VIOLATION' : 'HEALTHY' ) }}"

  - id: route_by_endpoint_status
    type: io.kestra.plugin.core.flow.Switch
    description: Branch A logs a reachable, policy-compliant endpoint. Branch B
      alerts Slack with the real host, port, and encryption setting that
      triggered it.
    value: "{{ outputs.classify_endpoint_status.value }}"
    cases:
      "HEALTHY":
        - id: log_healthy
          type: io.kestra.plugin.core.log.Log
          message: "Arrow Flight SQL endpoint {{ inputs.arrow_flight_host }}:{{
            inputs.arrow_flight_port }} reachable, use_encryption={{
            inputs.use_encryption }}."
      "TLS_POLICY_VIOLATION":
        - id: alert_tls_policy_violation
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          url: "{{ secret('SLACK_WEBHOOK') }}"
          payload: |
            {{ {"text": "Arrow Flight SQL endpoint " ~ inputs.arrow_flight_host ~ ":" ~ inputs.arrow_flight_port ~ " TLS_POLICY_VIOLATION. The probe connected with use_encryption=false while require_tls is true. A client reaching this endpoint right now may be doing the same."} | toJson }}
      "PROBE_FAILED":
        - id: alert_probe_failed
          type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
          url: "{{ secret('SLACK_WEBHOOK') }}"
          payload: |
            {{ {"text": "Arrow Flight SQL probe for " ~ inputs.arrow_flight_host ~ ":" ~ inputs.arrow_flight_port ~ " did not return a result. Check the host, port, use_encryption, disable_certificate_verification, and the ARROWFLIGHT_USERNAME and ARROWFLIGHT_PASSWORD secrets."} | toJson }}
    defaults:
      - id: log_unexpected_status
        type: io.kestra.plugin.core.log.Log
        message: "Unexpected classify_endpoint_status value: {{
          outputs.classify_endpoint_status.value }}"

  - id: log_audit_summary
    type: io.kestra.plugin.core.log.Log
    description: Final audit log confirming the gate ran, regardless of the branch
      taken above.
    message: "Arrow Flight SQL endpoint TLS compliance gate completed. endpoint={{
      inputs.arrow_flight_host }}:{{ inputs.arrow_flight_port }}
      use_encryption={{ inputs.use_encryption }} require_tls={{
      inputs.require_tls }} status={{ outputs.classify_endpoint_status.value }}"

errors:
  - id: alert_flow_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    url: "{{ secret('SLACK_WEBHOOK') }}"
    payload: |
      {{ {"text": "Arrow Flight SQL endpoint TLS compliance gate flow failed (execution " ~ execution.id ~ "). Check the execution logs."} | toJson }}

triggers:
  - id: every_30_minutes
    type: io.kestra.plugin.core.trigger.Schedule
    description: Shipped disabled.
    cron: "*/30 * * * *"
    disabled: true
    inputs:
      arrow_flight_host: "localhost"
      arrow_flight_port: 31337
      use_encryption: true
      disable_certificate_verification: true
      require_tls: "true"