Use DB connections provided via environment variables or use envs in config

[MustDie95]

Is your feature request related to a problem? Please describe.
Have tried to use multiple jobs with different databases, but unable to provide DSN in secure-way (from secrets)

Describe the solution you'd like
Use DB connections provided via environment variables or use envs in config

Describe alternatives you've considered
Use multiple instances of sql_exporter with different DSNs

Additional context

My test config:

env:
  PG_HOST:
    value: db-cluster
  PG_USER:
    from:
      kind: Secret
      name: db-cluster
      key: username
  PG_PASSWORD:
    from:
      kind: Secret
      name: db-cluster
      key: password
#
#  This works only for first job and target:
#  SQLEXPORTER_TARGET_DSN:
#    value: postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:5432/test_db_1
#
  PG_DSN:
    value: "postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:5432"

config:
  jobs:
    - job_name: test_job_1
      collectors: [test_col_1]
      enable_ping: true
      static_configs:
        - targets:
            test_db_1: "${PG_DSN}/test_db_1"
    - job_name: test_job_2
      collectors: [test_col_2]
      enable_ping: true
      static_configs:
        - targets:
            test_db_2: "${PG_DSN}/test_db_2"
  collectors:
    - collector_name: test_col_1
      metrics:
        - metric_name: m_1
...
    - collector_name: test_col_2
      metrics:
        - metric_name: m_2
...

Result is error like "invalid character '{' in DSN"

[burningalchemist]

Hi @MustDie95, could you try it without curly braces? Like $VAR?

The expansion should work, but if it doesn't, I'll have a look.

[MustDie95]

could you try it without curly braces? Like $VAR?

Have tried like this:

  PG_DSN:
    value: "postgresql://$PG_USER:$PG_PASSWORD@$PG_HOST:5432"

config:
  jobs:
    - job_name: test_job_1
      collectors: [test_col_1]
      enable_ping: true
      static_configs:
        - targets:
            test_db_1: "$PG_DSN/test_db_1"
    - job_name: test_job_2
      collectors: [test_col_2]
      enable_ping: true
      static_configs:
        - targets:
            test_db_2: "$PG_DSN/test_db_2"

Error log:

caller=klog.go:134 level=error func=Errorf msg="Error gathering metrics: [from Gatherer #1] [job=test_job_1,target=test_db_1] dial tcp: lookup $PG_HOST: no such host"

Only this method works well:

  SQLEXPORTER_TARGET_DSN:
       value: postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:5432/test_db_1

[burningalchemist]

@MustDie95 Yeah cool, thanks for checking! Let me investigate the issue, and I'll get back to you. 🙂

I believe I made it work somehow - don't remember now, but I'll revisit it, and make it work if it doesn't. 👍

[burningalchemist]

Ok, so far it works on my end (even with the curly braces and composing env variables from the other environment variables). So, the expansion works correctly for multiple connections.

I guess the issue here is somewhat related to where/how you define the env variables. Could you elaborate on that? 👍

P.S. I'll transfer the issue into a discussion, as it works for the test cases I have. 👍

[MustDie95]

My case is to get business metrics for some service in K8s.
This service is saving some indicators in PG (it is also in K8s) in addition to the technical metrics that it gives out in the usual way.
sql_exporter was deployed in K8s using ArgoCD with Helm-chart provided with this project.

values.yaml looks like:

serviceMonitor:
  enabled: false
serviceAccount:
  create: false

env:
  LOG_LEVEL:
    value: "debug"
  PG_HOST:
    value: db-cluster
  PG_USER:
    from:
      kind: Secret
      name: blackbox.db-cluster.credentials.postgresql.acid.zalan.do
      key: username
  PG_PASSWORD:
    from:
      kind: Secret
      name: blackbox.db-cluster.credentials.postgresql.acid.zalan.do
      key: password
  PG_DSN:
    value: "postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:5432"

config:

  global:
    max_connections: 3
    max_idle_connections: 3
    min_interval: 0s
    scrape_error_drop_interval: 0s
    scrape_timeout: 10s
    scrape_timeout_offset: 500ms

  jobs:
  
    - job_name: cardsdb
      collectors: [cards]
      enable_ping: true
      static_configs:
        - targets:
            db_1: "${PG_DSN}/db_1"
          labels:
            db: db_1
			
    - job_name: db_2
      collectors: [col_2]
      enable_ping: true
      static_configs:
        - targets:
            db_2: "${PG_DSN}/db_2"
          labels:
            db: db_2

  collectors:
  
    - collector_name: col_1
      metrics:
        - metric_name: some_status_count
          help: Some count by status
          query_ref: some_status_count
          type: gauge
          key_labels:
            - id
            - status
          values:
            - count
          timestamp_value: timestamp
      queries:
        - query_name: some_status_count
          query: |-
            SELECT id AS id, status, count(*) AS count FROM meta
            WHERE date >= (SELECT (date_trunc('month', NOW()) - INTERVAL '1 month')) AND date <= NOW()
            GROUP BY id, status;
			
    - collector_name: col_2
      metrics:
        - metric_name: some_counter
          help: Some counter
          query_ref: some_counter
          type: counter
          values:
            - some_count
          timestamp_value: timestamp
      queries:
        - query_name: some_counter
          query: |-
            SELECT NOW() AS timestamp, count(id) AS some_count FROM providers;

I would be glad to receive assistance in correcting possible mistakes and inaccuracies.

[burningalchemist]

We can hop on a call if you like. This might be faster for us to iterate, I'm happy to assist. 👍 Does it work for you? :)

My current concern is that Helm might not be able to compose the envs from the other envs (I don't work with Helm on daily basis these days, so I might be wrong). Also, it might not like the format with curly braces and so on. But it would be a different scope - outside of sql_exporter software (which of course should be mentioned in the docs).

So, I'd adjust the DSN in the config - we put postgresql://${PG_USER}:${PG_PASSWORD}@${PG_HOST}:5432/db_1 or /db_2 respectively. If you could try it out, then it'd be easier to follow up. 🙂

[MustDie95]

Looks like I finally solved this puzzle

Thanks to K8s docs (https://kubernetes.io/docs/tasks/inject-data-application/define-interdependent-environment-variables/)

Have renamed PG_DSN variable to PG_XDSN and use correct brackets and voula.. it works as desired..

  PG_XDSN:
    value: postgresql://$(PG_USER):$(PG_PASSWORD)@$(PG_HOST):5432

Now envs in POD's yaml are rendered in correct order:

     env:
        - name: PG_HOST
          value: db-cluster
        - name: PG_PASSWORD
          valueFrom:
            secretKeyRef:
              name: blackbox.db-cluster.credentials.postgresql.acid.zalan.do
              key: password
        - name: PG_USER
          valueFrom:
            secretKeyRef:
              name: blackbox.db-cluster.credentials.postgresql.acid.zalan.do
              key: username
        - name: PG_XDSN
          value: postgresql://$(PG_USER):$(PG_PASSWORD)@$(PG_HOST):5432

without unresolved dependency as earlier:

      env:
        - name: PG_DSN
          value: postgresql://$(PG_USER):$(PG_PASSWORD)@$(PG_HOST):5432
        - name: PG_HOST
          value: db-cluster
        - name: PG_PASSWORD
          valueFrom:
            secretKeyRef:
              name: blackbox.db-cluster.credentials.postgresql.acid.zalan.do
              key: password
        - name: PG_USER
          valueFrom:
            secretKeyRef:
              name: blackbox.db-cluster.credentials.postgresql.acid.zalan.do
              key: username

[burningalchemist]

Perfect, glad you sorted it out! 👏👍