Product articles Flyway Automation and workflow
Pipelining Configuration Information…

Pipelining Configuration Information Securely to Flyway

This article demonstrates two techniques for allowing Flyway to read extra configuration information from a secure location, possibly encrypted. The first technique pipes the contents of the config file to flyway via STDIN, and the second uses PowerShell splatting. This makes it much simpler to use Flyway to manage multiple development copies of a database using role-base security.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

You can use Flyway at arms-length, from the Flyway Desktop GUI or from within a build automation tool like Maven or Gradle. You will probably also wish to use it within scripts that automate the various development processes. As well as this, you’re likely to spend time using Flyway interactively at the command-line, from Bash or from the PowerShell console just by typing in commands.

For a simple interactive session, it makes sense to place your configuration settings in the current working directory, but it is never right to have your credentials and connection information there as well. You get some better security if you put them in the user profile area or directory, but Flyway only reads one, called Flyway.conf, and you’ll soon outgrow needing just one database connection. You can, of course, put your credentials into environment variables whose lifespan ends when you shut down the command-line or PowerShell session.

In PowerShell it is possible, as an alternative, to read a Flyway config file from a secure location, possibly encrypted, and ‘splat’ the contents to Flyway. Some Flyway versions allow you to set its configuration via STDIN, which will work for DOS or BASH scripting too.

By reading some or all of your settings from a secure file, you are no longer reliant on using settings in a Flyway.conf file, command-line parameters or environment variables to ensure that Flyway does exactly what you want it to do. You can, instead, pass the configuration settings through a pipeline from a secure source without the details appearing in a text file or script. This makes it a lot more secure, and it becomes easier to provide role-based credentials for database work.

Some or all of Flyway’s configuration details can be set this way, directly, or by the result of the previous command or process in the pipeline. It is even possible to add settings dynamically within the pipeline; a software device that I use to inform callbacks where to look for parameters.

How Flyway handles configuration details

When Flyway is invoked, it reads up to three configuration files in order. It checks the installation directory first, then the user-home directory and finally the current working directory. Logically, these are for installation details, user information, and project information, respectively. I’ve described Flyway’s multi-level configuration system extensively in A Programmer’s Guide to Flyway Configuration.

No credentials should be in the standard files that Flyway reads in from either <install-dir>/conf/flyway.conf or <current-dir>/flyway.conf. I’d also wouldn’t feel happy about anything else that pertains to the connection being in any of any of these two locations. This would include the JDBC URL, the oracle wallet location and the Oracle service.

For the normal storage of credentials on your own workstation, in a secure location, a firewall and a good up-to-date virus checker, the obvious place to put them is in the <user-home>/flyway.conf file (%userprofile%/flyway.conf in Windows), unencrypted taking advantage of the protection provided by the operating system.

My general approach so far has been to store only the credentials in the user area file, but I don’t use the standard <user-home>/flyway.conf to store them. I have a $FetchAnyRequiredPasswords task in my Flyway Teamwork framework that encrypts passwords using Windows standard using the DPAPI and stores them in a file in the user area, though does expose the UserID in the name of the file.

I have to do this rather than use the approach of using the <user-home>/flyway.conf file for the connection details. Why? Firstly, having Flyway read only a single <user-home>/flyway.conf file doesn’t scale. UserIDs and passwords will generally match the individual server or even the database. I even use different UserIDs for the different categories of tasks (user, dev and admin) to ensure that role-based security is working. It gets quite difficult to keep swapping around the <user-home>/flyway.conf to the correct connection, and it can prove embarrassing to run an action on the wrong database.

Secondly, you can’t encrypt the standard <user-home>/flyway.conf file because Flyway needs to read it. This means that you have to be careful what you put in this text file, because any process that can operate as a user application, such as a computer game, or either a suspect PowerShell or DOS process can read it. Worse, if the disk drive is read directly, rather than via Windows, and you’re not using BitLocker, the user home directories are all open to view. No, you need a way to encrypt your credentials and doing that means that you need a process that will read the encrypted file, decrypt it, and then somehow pass it on to Flyway.

Let’s tackle these problems one at a time. Firstly, we need to get around the issue that Flyway, by default, will read only config file in the user home directory; we need a way to specify further config files, one per database connection. After all, we can deal with the knotty problem of storing these extra configuration details securely, in encrypted form, and then decrypting them on the fly and passing them directly into Flyway.

Specifying extra user-level configuration files

If you only ever access one database/server combination, and only one UserID on that database, and your workstation is in a secure place, then you could happily just use the flyway.conf file in the user profile folder. However, larger projects will often use several servers, or may have different databases on a few shared servers. In larger teams, developers may be involved in several projects, or in more than one active branch on the same project.

As always, there are ways around this in Flyway. You can, for example, specify one or more further configuration files that can override these settings by using Flyway’s configFiles parameter, set either as a parameter or as an environment variable. Using this method, you’d have extra .conf files in <user-home>, one for each different ‘connection’; they can’t be encrypted if we use this approach because Flyway cannot decrypt it.

You would, logically, want just enough of these credential files. This would suggest that the filename of each ‘connection’ configuration file should reflect the RDBMS that you are using, the project name and the branch/database. You may also need to specify more if you use different UserIDs in a single server. Instead of relying on filename convention, you could choose to store them in subdirectories and then use their path to tell you which is the correct file. I’ve given a demo of how this works in a previous article, describing how to support GitHub branching in Flyway development.

All you need to do is to specify the path to Flyway. The only thing stopping it becoming a very neat solution is that you can’t use the configFiles parameter to specify the path to ‘connection’ configuration file in any of the three standard flyway.conf files. Flyway just ‘pins its ears back’ and won’t allow it.

Instead, we have the following options for specifying the extra configuration details:

  • Parameter or environment variable –Specify the path to the new ‘connection’ configuration file, either in the command tail as a parameter, or as an environment variable
  • Using STDIN – read the extra configuration information from a secure location, possibly encrypted, and pipe the contents of the config file to flyway via STDIN
  • PowerShell splatting – read the extra Flyway config file from a secure location, possibly encrypted, and ‘splat’ the contents to Flyway

I’ll demonstrate each one. The advantage of the last two techniques is that we can pass in the config details securely, from an encrypted file.

Specifying extra config files using a parameter or environment variable

We’ll start simply by demonstrating how to tell Flyway to read an unencrypted config file from your user area. It can do this because it is you that is executing Flyway. Let’s say, as a Windows user, you were accessing the development branch of a Pubs database project. You might create a PgSQL_Pubs_Develop.conf file in your user profile folder and specify it to Flyway by using a command-line parameter:

That is fine but it means that you have to type that every time you execute Flyway. If it is likely to be a problem, you can use the environment variable to specify the location of the extra config file. You’d set it at the start of the session, like this:

Either way, nobody can see your credentials or server name, and there is no risk of it leaking into the source control system by mistake!

This configuration setting actually allows you to pass a whole list of files.

Using the environment variable to pass the file-location of the ‘connection’ configuration file makes it easier to use Flyway interactively without a lot of typing of parameters. One problem though is that it overrides all other sources of settings other than the command-line parameters, and keeps those settings for the whole console session, when you really just want it to last only while you are running of the flyway instance with that particular database connection. You may, for example, legitimately want to check on all the running databases in your project to see what changes have happened and what version of the project they are at. You’d need to change your connection information for every run of Flyway to do this. This would require you to change the value of FLYWAY_CONFIG_FILES.

For these reasons, passing the location of the ‘connection’ configuration file via the command tail is the simplest way to go. Or it was, because there are other solutions that allow you to use encrypted credentials.

Piping the config file to Flyway via STDIN

There are some recent versions of Flyway that allow you to provide lists of configuration settings via STDIN so that you can encrypt your Flyway Configuration. I’ll go on to discuss alternatives for doing this in PowerShell that works for all versions.

If you are so hardcore that you always type in your Flyway commands through the command line, you might not like this solution, but for those of us who are habitual cut n’ pasters, it’s fine.

We’ll start off simply, without the decryption, just to show how an STDIN approach can simplify using Flyway. Imagine that we are in the command line. We want to use the Oracle version of the old Pubs database. We want to see what version it is at, and who did what. We make the right location our current working directory and tap in, or paste in, this.

We pipe the contents of the required configuration file to STDIN, where Flyway reads it. The result is as follows:

Schema version: 1.1.1
+-----------+---------+------------------------------+--------+---------+----------+
| Category  | Version | Description                  | Type   | State   | Undoable |
+-----------+---------+------------------------------+--------+---------+----------+
|           |         | << Flyway Schema Creation >> | SCHEMA | Success |          |
| Versioned | 1.1.1   | Initial Build                | SQL    | Success | No       |
| Versioned | 1.1.2   | Pubs Original Data           | SQL    | Pending | No       |
+-----------+---------+------------------------------+--------+---------+----------+

In PowerShell, we do this:

…and we get this…

Schema version: 1.1.1
+-----------+---------+------------------------------+--------+---------+----------+
| Category  | Version | Description                  | Type   | State   | Undoable |
+-----------+---------+------------------------------+--------+---------+----------+
|           |         | << Flyway Schema Creation >> | SCHEMA | Success |          |
| Versioned | 1.1.1   | Initial Build                | SQL    | Success | No       |
| Versioned | 1.1.2   | Pubs Original Data           | SQL    | Pending | No       |
+-----------+---------+------------------------------+--------+---------+----------+

You’ll understand that, instead of just typing an unencrypted file, we can very simply decrypt an encrypted one.

In the following example, we use the STDIN feature to build the latest versions of two branches of the PubsOracle Flyway project, in one pipeline operation.

We read the sensitive details of the connection, usually the User, password and flyway URL, from “per branch” config files stored in the user area and rely on the config files in the project area for all the details that can, and should, be in plain text. In the following example, our connection information was complicated by the need to also specify the wallet and the connection together with the URL.

You’ll notice that the contents of the configuration file is read into a string called $conf. A very simple change will allow you to decrypt an encrypted file to do the same thing.

As you can see, this does more than just allow us to use encrypted credentials. It also removes a lot of the difficulties associated with ‘switching parameters’ when needing to supply different credentials for different servers or databases, or having to reset an environment variable every time we need to switch credentials. Now, we just provide the configuration information via the piped input. The flyway.conf file in the current working directory holds the project-level information whereas all the connection-level information is in the user area. Smiles all around! However, it would be good to encrypt all that information as well as using the operating-system level of protection.

The biggest bonus with the use of the STDIN technique is that we can now use an encrypted source for the configuration information rather than plain text in a file. The STDIN technique is designed to allow you to encrypt all your secrets, so that your scripted process will just read the configuration file, decrypt it within the pipeline and pass the configuration straight into Flyway. By changing the line

…to…

We can decrypt an encrypted file (Gnu Privacy Guard in this case). We’ll go on to explain how this is done later.

Using PowerShell and ‘splatting’

Without the recent STDIN feature of Flyway, it can still be treated as a pipeline component. We can read the information from file, do whatever decrypting or other transformations we need, and then pass them as a ‘splatted’ array of parameters to Flyway. The only downside is that it is no use to the hardcore users of Bash or Dos Command prompt.

I explain how to use this technique in detail in Using Windows Security and Encryption with Flyway, but here is a function that will demonstrate it:

You could then use it like this, or in a number of ways.

In this case, the config file would be read, and translated into an array of parameters that are then ‘splatted’ to Flyway, which is none the wiser of the tortuous route by which its parameters have arrived.

Handling sensitive configuration details securely

To get a connection to a cloud service, we sometimes need several configuration items. In our last examples, we’ve shown how easy it can be if we can get Flyway to read in extra configuration files, either via STDIN or PowerShell splatting, and both of the techniques give us the opportunity to store the configuration details encrypted in the suer area, decrypt them on the Fly and pass them directly into Flyway. I’ll demonstrate how to do it.

First, though, let’s start with a simpler task, where the only problem we have is keeping passwords secure. We merely get the correct password and UserID for the login and pass it to Flyway, either formatted in Flyway.conf format, or as either a parameter or Environment variable.

Encrypting and decrypting simple Windows login credentials

Let’s say we want to keep our login credentials out of a flyway.conf file altogether, so that we can store the remaining non-sensitive details in the config files, in plain text.

I’ve written a PowerShell function that reads out the credential you specify and translates that to a couple of configuration settings. It is compatible with the way that Flyway Teamwork does it (using $FetchAnyRequiredPasswords), in that it will access the same encrypted passwords, but it doesn’t require use of the framework.

This routine will save the UserID and Password in an encrypted form the first time you run it for that particular server and UserID. On any subsequent calls with the same parameter, it will provide the decrypted values as parameters that we can pipe directly to Flyway via STDIN. If your password changes, you just delete the existing file and ask again.

This function gets the password for that particular UserID from the user area, decrypts it and puts it in a string that is formatted as a line from a flyway.conf document. It then passes it to Flyway. Because the piped configuration item is read after the standard config files, it overwrites any current value with the new ones so you can place an entirely bogus one in the standard config files to confuse attackers, if the mood takes you.

The same technique allows the parameters or environment variables to be used to provide the password.

We can tweak this function to allow us to encrypt whole files, rather than just a password, but that is another story.

Encrypting and Decrypting Flyway config files

So much for a single pair of items such as a password/UserID credential. What if there is more? There could be more complicated connection information or maybe information that changes according to the branch of the database on which you’re working. As well as the UserID and password you’ll also want to specify a number of other configuration items that are unique to the connection.

The STDIN or PowerShell Splatting methods of specifying configuration information makes Flyway much more readily usable in cases such as these, when you need to run a single automated operation for a number of different projects, branches or instances. It makes it much simpler to switch configuration as part of a pipeline, while maintaining security for connection information and credentials, or other ‘secrets’, rather than having to rely on switching config files, parameters or environment variables.

In turn this makes it easier for you when engaged in ad-hoc development tasks, database debugging, long or repetitive database development sessions with Flyway, or you want to automate boring tasks such as provisioning a whole group of identical databases for parallel testing.

These files would, obviously, be stored in the user home directory. If you need to store these files in an encrypted form, you can then decrypt from an encrypted config file. It means that you can store all your connection information in the user area as encrypted files, so if someone tries to run a rogue script, under your login, to hoover up everything in your user area, it will find nothing useful.

Simple decryption with Gnu Privacy Guard using STDIN or splatting

In this first example, I’ve used the GNU Privacy Guard (GPG) to encrypt and sign my credentials and connection information. I’m using a YubiKey to hold my various keys to decrypt the file, so it is no hassle to decrypt the file on the fly and pipe it into Flyway:

That’s only a credential and a single database. However, the system scales up easily.

We can do the same action for a version of Flyway that doesn’t have the STDIN facility by using ‘splatting’.

And then we can use Flyway with the contents of the file converted to Flyway parameters.

Handling a process with multiple configurations using Gnu Privacy Guard

In this example, we’ll have a more difficult connection to Oracle Cloud, using a wallet. We have two different databases. Again, the obvious place for storing several different credentials for Flyway is within an encrypted file stored in the user area, or the Credential Store in Windows. We’ll access them both in one script to update the two different databases. The pipelined approach suits us fine. We can store the configuration data in its ‘.conf’ format and pass it all in through STDIN in one gulp.

So, let’s add a few complications to our previous example, in the form of an encrypted ‘branch’ config file, holding the connection details, so we can see how this makes it easier to pipe in anything that needs to be encrypted, and therefore unencrypted ‘on the fly’ for input into Flyway:

Here, Flyway is only executed in one place, so we can extend this to react to errors, display verbose output when wanted, and display warnings in their own stream. We would want to do a few other tweaks such as deleting the text of the decrypted config items after the last command is executed.

Conclusion

By taking any configuration items from the STDIN pipeline, a Command line database tool such as Flyway becomes a full contributor to any scripting pipeline. Flyway is able to take its configuration settings via STDIN and passing the output via STDOUT and STDERR (see Piping, Filtering and Using Flyway Output in PowerShell for more about that). If that technique isn’t available in your version of Flyway, I’ve provided an alternative, using PowerShell splatting.

This is going to have great appeal to the software engineers and security people in many enterprises. Even the venerable DOS Batch can now feed in configuration files to Flyway, for surprisingly common development operations such as updating every installed copy of the development database in a ‘binge-migration’. All you have to do is to feed in the configuration settings down the pipeline for every installation. If all these files are encrypted to PGP standards and installed in a user directory, this looks like sufficient security to me.

Tools in this post

Flyway

DevOps for the Database

Find out more