Read environment variables in sqitch.conf

[chamini2]

We have a DB of many schemas and different targets. Each target has a uri option like (reduced version of sqitch.conf):

[target "central"]
    uri = db:pg://central@localhost:5432/db
    plan_file = central/central.plan
[target "co"]
    uri = db:pg://co@localhost:5432/db
    plan_file = country/co.plan
[target "ve"]
    uri = db:pg://ve@localhost:5432/db
    plan_file = country/ve.plan

We were hoping to commit the sqitch.conf file in git, but removing the uri options of each and thought that maybe setting these options like:

[target "central"]
    uri = $CENTRAL_TARGET_URI           ##### here
    plan_file = central/central.plan
[target "co"]
    uri = $CO_TARGET_URI           ##### here
    plan_file = country/co.plan
[target "ve"]
    uri = $VE_TARGET_URI           ##### here
    plan_file = country/ve.plan

And if the user has the environment variables CENTRAL_TARGET_URI, CO_TARGET_URI and VE_TARGET_URI set, it fills said option with the value, it would make an easy to use solution. What do you think?

[theory]

Not keen on environment variables for this. Better would be to omit the uri config from each target and have some way to specify it on the command line, e.g.,

sqitch deploy co --uri db:pg://co@localhost:5432/db

[chamini2]

We thought of that option, but would mean to include aliases to ease development. May I ask why you are not keen on environment variables in the sqitch.conf?

[theory]

They're two different things. I can see options or environment variables overriding the contents of the config file, but I wouldn't stick them directly into the config file.

[chamini2]

...environment variables overriding the contents of the config file...

That would work too, but it would mean setting some kind of rule for the names of environment variables to override them.

[g-borgulya]

Do you guys have a best practice to work with local development, local testing, cloud development and cloud production db configurations, where it's critical from the security point of view not to include the credentials of databases used in the cloud in the config files?

Is the best way to have only one target in the config file, and override the uri with sqitch deploy co --uri URI where URI is specific to the current deployment? Or maybe we should have no targets at all? I'm quite newbie with sqitch... My first idea was also to use env variables, as that's a common way to configure things in CI.

[theory]

Have you read sqitch help passwords, @g-borgulya?

[g-borgulya]

Thanks @theory , it's helpful.

[jperkelens]

If not via environment variables, some way to template the uri would be quite helpful. My usecase is to hydrate the priv_key_file_pwd from an environment variable, either directly or by passing it via a flag so as not to check in a secret. Ideally would like to move from:

sqitch deploy "db:snowflake://account.snowflakecomputing.com/sqitch?Driver=Snowflake;warehouse=Deployment;authenticator=SNOWFLAKE_JWT;uid=SVC_DEPLOY;priv_key_file=/ssh/snowflake_svc_deploy.p8;role=ACCOUNTADMIN;priv_key_file_pwd=${PASSPHRASE};database=SQITCH"

to

sqitch deploy -t nonprod --key_file_pwd=${PASSPHRASE}

with a sqitch.conf containing the large connection string above.

[theory]

You can use the $SNOWSQL_PRIVATE_KEY_PASSPHRASE environment variable for this, no?

[jperkelens]

This works for the SNOWSQL client, however the string needs to be in the Engine string or it returns the error: "cannot marshal private file". Additionally, sqitch is printing the passphrase in plaintext in its logs. Example:

$ sqitch deploy --registry ${REGISTRY} --verify -s ENV=$ENV "db:snowflake://${SNOWSQL_ACCOUNT}.snowflakecomputing.com/sqitch?Driver=Snowflake;warehouse=Deployment;authenticator=SNOWFLAKE_JWT;uid=SVC_DEPLOY;priv_key_file=/ssh/snowflake_svc_deploy.p8;role=ACCOUNTADMIN;priv_key_file_pwd=${PASSPHRASE};database=SQITCH"
Deploying changes to db:snowflake://zea07112.us-east-1.snowflakecomputing.com/sqitch?Driver=Snowflake;warehouse=Deployment;authenticator=SNOWFLAKE_JWT;uid=SVC_DEPLOY;priv_key_file=/ssh/snowflake_svc_deploy.p8;role=ACCOUNTADMIN;priv_key_file_pwd=my%20secret%20passphrase;database=SQITCH
  + AddDataEngETLRole ...... ok
  + AddDataEngETLWarehose .. ok

[theory]

Yeah, Sqitch is not aware of any secrets other than passwords; we should teach it about this one. But this is one of many reasons it recommends against using passwords in target URIs at all. Hard to get away from I you need to put a password in the query string, though.