injecting secrets into values.yaml

[Swaps76]

When configuring the target in the values.yaml, I need to hardcode the postgres credentials as part of the azure DSN;

config:
target:
name: dev-database
data_source_name: 'postgres://user:password@database_url/table'

I have set the credentials as external secrets and configured them as ENV VARS in the pod

env:
PGPASSWORD:
from:
kind: Secret
name: sql-secrets
key: sql-password
PGUSER:
from:
kind: Secret
name: sql-secrets
key: sql-user

But how can I pass these secrets to the configmap so that they are iterpolated in the sql_exporter.yml?

[burningalchemist]

Hey @Swaps76, you can also populate the env variable SQL_EXPORTER_DSN to be used instead. Have a look at the example here and let me know if it answered your question.

Alternatively, you can use env variable in your configuration file like:

...
data_source_name: 'postgres://${PGUSER}:${PGPASSWORD}@database_url/table'
...

During the init, the env variable names will be expanded to the actual values. Give it a try. 👍

[Swaps76]

Thank you @burningalchemist

I had tried using the env var in the data_source_name as per your suggestion, and although the env vars were set correctly, the expansion did not seem to work, instead I got this error;
msg="Error gathering metrics: [from Gatherer #1] [target=dev-database] pq: password authentication failed for user "$PGUSER""

And using curly brackets;
msg="Error gathering metrics: [from Gatherer #1] [target=dev-database] net/url: invalid userinfo"

I have since removed the credentials entirely and I am now not getting errors, and the metrics are being exported ok, however I am not sure where it is getting the credentials from. Is there any more info on what the sql-exporter is running to connect to the database, assuming a psql command?

Thanks!

[burningalchemist]

Hey @Swaps76, with curly braces have you tried it this way - ${PGUSER}? I think this is the right format for expansion (I've updated it in the answer above). The error you're getting with invalid userinfo is most likely because your password or username are using special characters, that should be URL-encoded as per the documentation:

ref: https://stackoverflow.com/questions/48671938/go-url-parsestring-fails-with-certain-user-names-or-passwords

---

In general, it depends on the driver. PostgreSQL golang driver automatically checks PGUSER, PGPASSWORD and other env variables out of the box. So this option also works but specifically for PostgreSQL. Other database drivers might not have such a functionality.

[Swaps76]

Ah ok! I was confused as I couldn't see PGUSER or PGPASSWORD referenced in the code, but when I removed them from the values.yaml, I got this error (I am using Azure flexible postgres server);

msg="Error gathering metrics: [from Gatherer #1] [target=dev-database] pq: password authentication failed for user \"nobody\""

So the env vars are definitely being used even though the data_source_name does not now contain those credentials. This is what is set in the pods /etc/sql_exporter/sql_exporter.yml;

data_source_name: postgres://db_name.postgres.database.azure.com:5432/table_name

To answer your question, yes I used curly brackets like;
data_source_name: postgres://${PGUSER}:$PGPASSWORD@db_name.postgres.database.azure.com:5432/table_name

I had read about that limitation in the net/url package so I changed my creds to have no special characters but was still getting the expansion issue.

Should the sql_exporter.yml file contain the variables or the expanded variables?

[burningalchemist]

Well, if so, then maybe it's a bug. I'd need to investigate and reproduce it separately. 👍 The expansion was contributed by the other user last year, and they were happy about it in the end. So it could be something else. 😄

When I tested it myself, the data_source_name contained placeholders that are expanded to values during startup. This worked fine. I'll check it again when I find a bit of extra time soon. 👍 I will update this discussion with details, so stay tuned. 😃

It could be a tiny configuration bit which is better to be added to the docs.