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. 👍