Pass environment variable or runtime variable to sql scripts

[satyasheelpandey]

To create or update roles the passwords needs to be passed as a parameter for security reasons .

Example CREATE ROLE read_only WITH LOGIN PASSWORD '${password}' VALID UNTIL '${validUntil}';

[jasondcamp]

Good idea, what if we supported the ability to pass in something like an -e NAME=VAL similar to the way docker does it, and you could do like -e PASS=$LOGIN_PASSWORD so it would support command line options and env variables too? Does that sound like it would accomplish what you'd need?

[satyasheelpandey]

Hi Jason, Great to hear from you so quickly.

Yes, this what I want to achieve .

Also if the pyway.config can also be passed as variables it will be far more useful .

Regards, Satya

On Tue, 16 Apr 2024 at 17:56, Jason Camp @.***> wrote:

Good idea, what if we supported the ability to pass in something like an -e NAME=VAL similar to the way docker does it, and you could do like -e PASS=$LOGIN_PASSWORD so it would support command line options and env variables too? Does that sound like it would accomplish what you'd need?

— Reply to this email directly, view it on GitHub https://github.com/jasondcamp/pyway/issues/60#issuecomment-2059427557, or unsubscribe https://github.com/notifications/unsubscribe-auth/AM4QWRCX5BB7HBMUD7TQSZLY5VC4NAVCNFSM6AAAAABGJS7GRGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDANJZGQZDONJVG4 . You are receiving this because you authored the thread.Message ID: @.***>

[jasondcamp]

So the main issue i see here after starting to work on this is that it breaks the validation component of pyway unless you pass in the environment var every time you run pyway from that point on. For example, if you do a migration where you use {password} then the checksums will fail if password is not passed in or stored in the migrations database. Either way it's not very secure.

Unless I'm missing something, I can't think of a clean way to do this, happy to discuss if you have any suggestions.

BTW you can use variables for the pyway.conf - all values can be specified on the command line with flags, as env variables, or in the .pyway.conf file

[jasondcamp]

I've been reading about this here - https://documentation.red-gate.com/fd/placeholders-configuration-224003082.html

I'm still not sure how Flyway calculates the checksum (maybe based on the file not having the substitutions), but its also unclear to me what happens when you want to apply the migration to a new environment with these variables.

• Should they be required, by scanning through the files and making sure there are references to each variable referenced?
• Do we rely on the user to know that they need to add the variables, otherwise the migration just clears the variables?
• Should we support the ability to store variables in the config? Does this defeat the purpose of the variables?

Would love your thoughts. Thanks!