Use environment variables in SQL queries

[ScareCrowZV]

Hi!

Is it possible to use a secret from Vault in an SQL query?

env:
DEPLOY_USER: 
    value: vault:services/db/rel/ts#username
--------------
          query: |
            SELECT 'days_until_expiration_moon' as measurement,
                   ISNULL(LOGINPROPERTY('${DEPLOY_USER}', 'DaysUntilExpiration'), 1000) as value
            WHERE SUSER_ID('${DEPLOY_USER}') IS NOT NULL

In this usage, it is as if the value is not resolved, but substituted as it is.

At the same time, it works in the jobs section as a login and password for DSN

[burningalchemist]

Hey @ScareCrowZV, it's not possible to expand env variables in the query field for the time being. It's only applied to the connection strings (have a look: link).

Currently, queries must be defined because there’s no dynamic preprocessing involved; they’re simply passed through.

What's currently possible and might potentially work for you is the following composition:

• sql_exporter relies upon sql_exporter.yml and provided collector files (they might be external files too);

• a user prepares a simple configuration generator in their favourite language that populates a collector file (with queries, etc) fetching data from user-known locations (e.g. vault, s3, local filesystem, etc);

• upon writing to a file, user hits the /reload endpoint (programmatically or scheduled), that would reload the config and repopulate queries to be run without restarting the instance.

This approach allows for sufficient flexibility, but also makes users responsible for necessary data retrieval (security, integrity and relevance). Give it a try. 👍

Overall, I try to avoid adding additional pre-processing and dealing with external systems to the utility, as it immediately affects the performance on scale and requires extra handling to be done.

---

Having said that, I have some feature in mind that should enable histogram metrics, and to avoid writing unpleasant queries I want to introduce a limited set of macro functions. Looking up the env variable might be a thing too. I don't want unexpected automatic env expansion in the queries, but explicitly requested ones feel better to me. I don't have ETA for that yet.

I'd appreciate if you could also describe your use case if you may. 👍

[ScareCrowZV]

I just wanted to hide sensitive information, because this is the user's login for deploying the database.

[burningalchemist]

@ScareCrowZV, Yeah I see. I'm afraid there is nothing available out of the box to support that. But what I described about the macros above might actually work out. As soon as I find some time, I'll begin working on it, and will update this thread upon some results. 👍