• Flyway
• Configuration, Authentication

Making Full Use of Environment Variables for Flyway Settings

Although Flyway is most often used within scripts, it is also an excellent tool for interactive use, if you can minimize the typing required. If you organize your projects on disk, and use configuration files sensibly, you can generally avoid long command lines and just stick to just typing the Flyway commands for development work. Scripting is fine for testing and deployment, and these scripts can be as complex as you like, but when you are typing in commands, every keystroke slows you down and is a potential source of error.

Why not put all your project details in a Flyway config file?

Why not just put every detail of your project, including database connection details, in a Flyway configuration file in your working directory? Well, yes, you can and should make good use of config files, but there are four snags with stuffing everything into your project-level config.

Security

You’d be ill-advised to store credentials, connection information, or other sensitive information in an unprotected config file. It is better to reference individual environments, stored in files in the user area where they are given protection by the operating system. That requires at least an extra parameter for your development sessions, probably more. If you store these sensitive details as Session Environment variables, they are volatile and cannot leak into the version control system.

Keeping details up to date

Another snag to using configuration files for storing Flyway credentials is that you must update the config files whenever credentials change, which will happen frequently if you are using containers for development and testing work, or if you have a policy of changing passwords often. Standard CONF files are so simple that they are easily updated but there is no standard way of updating a TOML file, so you’ll need to store the updated details in an extra configuration file, which would need a parameter whenever you execute Flyway.

Managing migrations on several database environments

A developer will need to access several database environments for a single project. Flyway was designed on the assumptions of “one environment per project”, so you couldn’t easily switch between servers and databases within one project. Flyway fixed this problem by introducing TOML environments, allowing developers to use separate credentials and connection details for each environment. Again, this means that to change the credentials easily, you will need to hold each environment in a separate TOML file and configure Flyway to read each one separately (e.g. by having a list of config files as a parameter). This would allow each environment to be over-written when credentials changed.

Checking Callback scripts

Callback scripts depend on using environment variables for their parameters and cannot be checked and tested without having them in place. See How to Write and Debug a PowerShell Callback for Flyway Migrations.

Environment variables: a three-tiered system

Environment variables are typically represented as key-value pairs and are often used to pass values to software and manage the execution environment. They can be set globally (system-wide), for a particular user, or just for the current session (at a terminal window).

Together, these three levels provide the necessary context and resources for processes to run. The session environment variables are dynamic values that can affect the way that processes running within that session will work, and they provide a way to pass configuration information to applications and system processes. Session variables last only as long as the session lasts. User variables last while the user is logged in, and System variables are there while the system is running.

If you want a CLI application to, for example, connect to a database to run various SQL Statements as part of a process, you would usually, unless you just love typing in commands, set up the credentials and URL as session-level environment variables, and only use the command-line parameters for the ephemeral information, which in our example would be the path to the files containing SQL statements.

Controlling Flyway via Environment Variables

Environment variables provide a useful way of setting up Flyway. Within the runtime context in which Flyway operates, it can read almost all its configuration items from environment variables. Some settings, such as configFileEncoding can only be made as an environment variable or at the command line, but not from a file; settings like .color only in the command line, but generally, there is a correspondence between environment variable and the configuration file item.

If you provide up-front all the necessary configuration items required for Flyway to do its work, there is almost no typing; you just need to type the command in the command tail, at which point you can add any configuration item that is needed to override the corresponding environment variable value. Here is the full order of precedence for declaring the values of configuration settings, listed from lowest to highest, meaning that a command-line argument tops everything:

1. Install Directory configuration files
2. User-home Directory configuration files
3. Execution Directory conf files (the directory from which you’re executing flyway)
4. Working Directory (usually the Flyway project directory)
5. Standard input (STDIN)
6. Environment variables (system, user, or session-level)
7. Command-line arguments

Converting conf files to environment variables

I store all connection settings (like database URL, username, password, etc.) in .conf files in the user area. Rather than manually setting each environment variable, I use a bit of PowerShell to convert the values in these .conf files into environment variables. It extracts the settings and assigns them to Flyway-specific environment variables (like FLYWAY_URL, FLYWAY_USER etc.).

All I need to do then is either switch to the working directory (the project folder containing the migrations and other configuration settings), or else set the working directory as a command line argument:

or

Before we go any further, we need to be able to delete Flyway environment variables.

Here’s the script to read in the config file and convert every configuration parameter to the equivalent environment variable:

We can turn this into a function that also determines the name/port of the server as a placeholder. This function is now in the Flyway Teamwork framework

Saving environment variables in a file

Conversely, if you have laboriously constructed a set of Flyway environment variables that work well, you can save them as a file. In this example, I save them as a JSON file, which is easy to read when you need to read some of the configuration, but it is just as easy to save them in other formats such as TOML or CONF files. In this example, I redacted the password.

Environment variable format, and providing other information for scripts and callbacks

Just as the working environment supplies information to Flyway, callbacks, and scripts can access Flyway system details through environment variables. Any environment variables set via Bash or the DOS command line are automatically passed to all callbacks and scripts, during Flyway execution. Even if you don’t provide credentials explicitly, as environment variables, they will appear in the session that executes a callback or Flyway Script as an environment variable.

We can’t pass parameters directly to scripts or callbacks. We must define environment variables, in various formats and then when Flyway runs, it reads these environment variables and passes their values into the scripts and callbacks.

For more details, see Passing Parameters and Settings to Flyway Scripts for example, and more extensively in Running Flyway Pre-Migration Checks on the Database.

Config variables

The most required configuration parameters are automatically made available to all non-SQL scripts and callbacks through environment variables with FLYWAY_ prefix (e.g. FLYWAY_PASSWORD, FLYWAY_URL, FLYWAY_USER). These will refer to the connection details and credentials for the current working directory.

The format of environment variables is ‘Classic’ Flyway, not TOML key/value format, so does not include information in the key to indicate the table in which the variable belongs.

Placeholders

Additional default placeholders are provided to versioned migrations, repeatable migrations, SQL callbacks and scripted callbacks. In the file, you can use a range of default placeholders, such as FP__flyway_table, and Flyway will automatically substitute it with the value from the corresponding environment variable ($env:FP__flyway_table__):

• FP__flyway_defaultSchema = The default schema for Flyway
• FP__flyway_user = The user Flyway uses to connect to the database
• FP__flyway_database = The name of the database from the connection URL
• FP__flyway_timestamp = The time that Flyway parsed the script, formatted as ‘yyyy-MM-dd HH:mm:ss’
• FP__flyway_filename = The filename of the current script
• FP__workingDirectory = The user working directory as defined by the System Property
• FP__flyway_table = The name of the Flyway schema history table
• FP__flyway_environment = The name of the database environment configured for this script execution

We can also define custom placeholders using like ${myplaceholder}. These can be read directly by a scripting language, or you can use them in your SQL files or scripts and Flyway will replace them with the actual values during execution (like how variables are used in Ant-style build scripts). We can also configure flyway to substitute the values in SQLCMD placeholders.

Files within the working directory

When Flyway runs migrations, it generates a file called report.json in the working directory. This file contains useful information about the migration process, including:

• Current schema version: The version of the database schema after the latest migration.
• Database type: The RDBMS type (e.g., SQL Server, PostgreSQL).

This information can also be read and made available within a callback, in addition to the environment variables.

Using environment variables with Flyway securely

Finally, what are the security implications of using environment variables for sensitive information like database credentials? It is certainly bad practice to post the environment variable at the System level. If you place them at the User level, they could still be read by a rogue process running with your user authority. It’s generally best to limit the scope of these variables to the session-level, but this on its own is not enough. For example, if a process creates a child process, the child inherits the environment variables of the parent. A rogue process will quickly have these values ‘on the dark side’, but it can also lead to unintended exposure if the child process logs or otherwise exposes these variables.

However carefully you encrypt your credentials and connection details they will be in plain text as environment variables, and they must be so to accommodate the requirements of the sort of scripts that assist a Flyway migration.

Therefore, it is relatively easy for a malicious actor to place a script into a set of migrations, or a callback, that copies the values of all the environment variables undetectably and saves them somewhere (the script is easy to write in PowerShell DOS or Bash, but I’m not going to show you how it is done).

Flyway will obligingly execute scripts within an environment that it has ensured has all the parameters it needs to connect to the current database. These will be visible to any process executing within that session. Even if a rogue actor can gain access to the directories containing scripts and callbacks, this probably isn’t, by itself, enough for a third party to connect, especially if you are using a proxy or work within a DMZ.

If you use volatile containers for development work, such as those provided by rgclone in Redgate Test Data Manager, the risk of a breach would come if you were reckless enough to use unmasked live data for your development data.

There are enormous advantages to having a system like Flyway that can execute scripts. However, the locations of these callback and migration scripts must be protected from access or unauthorized alteration. The best approach is probably to keep all scripts in source control and positively check all scripts for changes before they are used. Quite apart from the risk of leaking credentials or connection information, there is always a risk of allowing the execution of a PowerShell script in your ordinary user account if you’re not completely sure of the contents.

Summary

If you do a lot of interactive work with Flyway at the command line, and you resent the wasted time in all that typing, it may be time to use environment variables to set all the specific configuration items you need for your database environment.

Once you have started a session, you can create or update these variables, and they will last for the whole session. With luck that means that you just tap in the commands. This does not provide any additional security risks to the ones you already have in using Flyway.

 

Tools in this post

Redgate Flyway

DevOps for the Database

Find out more