Phil Factor

17 May 2023

• Flyway
• Configuration, Authentication

Using Windows Security and Encryption with Flyway

Storing Data securely in Windows

The user’s profile folder on a Windows machine, also known as the user area or home directory, can store any information that should be kept secret, because only the corresponding logged-in user can access it. If you store these files within ‘OneDrive’ (the cloud app from Microsoft that appears as a subdirectory of the user profile folder), you can access securely them from different devices.

However, any sensitive information must still be encrypted. The contents of files in the user area saved on a local drive aren’t encrypted on disk unless you are using BitLocker. This means that they can be read, ‘at rest’, by any device that can read the hard disk. BitLocker, by itself, isn’t a complete solution because if someone gains access to your files while the drive is in use, they will be able to read the unencrypted data. This is why encryption is a good idea. By itself it may be insufficient, but it provides defense in depth.

When you need a file to be encrypted, but the information must be shared, you can opt to use an encryption key rather than let Windows do this for you, but this key has to be stored just like any password. For this sort of use, public-key cryptography is better.

However, for our use, we don’t want to share credentials. The Data Protection API (DPAPI) is ideal for this sort of user-level encryption. It is designed to provide protection for sensitive data on a single machine, or within a single user profile, and is not intended to be used as a general-purpose encryption mechanism.

Here is a simple example of saving text to a file in an encrypted form and then decrypting it:

On disk, this is, in this case, stored as such in the file called ‘MySecretMessage‘. If we examine the contents, we see this:

But we can read this file and decrypt it, because we are the logged-in user that saved the file in the first place:

Fee-fi-fo-fum,
I smell the blood of an Englishman,
Be he alive, or be he dead
I'll grind his bones to make my bread

I’ve created two cmdlets to do this work for you. They are Read-DPEncryptedContent and Write-DPEncryptedContent. We can now save a secret, easily:

… and it can be read back, but only by the user who saved it:

…which gives…

Passing encrypted configuration information to Flyway using PowerShell

There are some parameters that Flyway needs to work that cannot be put safely in any of the default configuration files. We need at least the credentials and probably also the connection information to be encrypted.

This DPAPI method is fine for our purposes because we can store all our Flyway connection information, however complicated it is, in encrypted .conf files in the user area. We can then read in the appropriate file, decrypt it, and pass it to Flyway without having to save its contents in either a file or global variable.

If you have a version of Flyway that accepts configuration items as standard input (STDIN), you can use this instead. All we then need to do is to create a PowerShell cmdlet or command-line tool to take, as its input, the name of the encrypted config file and to send to its STDOUT the unencrypted contents. We can then create a pipeline that has Flyway as the recipient of the configuration information.

If you are using a Flyway version that can’t do this, I’ll demonstrate how we can use PowerShell to achieve the same thing, by taking advantage of the ‘splatting’ feature with which PowerShell can pass parameters to another cmdlet, function or application. The only drawback is that we can’t ‘splat’ in DOS batch scripts.

We’ll use a cmdlet called Get-DPEConfigItems, which is just a modified version of Read-DPEncryptedContent. It converts the encrypted Flyway configuration file into an array of Flyway parameters, which can then be passed to Flyway as if you were typing them in:

Now we can use this to work with Flyway. First, we encrypt the required configuration details. In this case I’m putting the entire Flyway configuration into an encrypted PubsMain file, just to test it all out:

The values we read from this encrypted config file will take precedence over the ones read from Flyway configuration files in the current working directory. As I’m not a typist by inclination, I’ve used a shortened alias for this cmdlet and a short variable name for the encrypted config file:

Normally, you’d probably only want the connection information and credentials to be secure, in which case you’d want to read the others from the current working directory. This means that you will remain with your current working area as the Flyway project directory containing your current flyway.conf. If, however, you wish to wander to another directory, and use an encrypted file for all your configuration, then any relative links in the encrypted file will no longer work. If, for example, you’ve specified a relative path for the flyway.locations parameter in your encrypted flyway.conf file (e.g., flyway.locations=filesystem:sql), then you’ll either need to stay in your project directory, or else change any relative path reference in the encrypted parameter file to make it absolute.

And now we can run any Flyway commands we wish at the console:

You might be wondering why I store the UserID securely together with the password when it is possible to assign the UserID in the Flyway.conf file in the user profile area. This is because it is common to use different credentials for different roles on a database, and so one can end up with several credentials for the same database. If you are working on several projects, then it soon gets impossible to have just one UserID.

The advantage of using just a single file to specify your configuration will come if you need to provision or update a number of Flyway projects in a batch.

Running Scripts with Flyway

If you execute a callback script in Flyway, you get a few details for your script environment, passed from Flyway as environment variables. You get the default schema, the current database user, the name of the database, the time, the filename of the script, the user working directory and the name of the Flyway schema history table.

These are useful but often insufficient for more complex tasks. To get more than this, you can access the standard configuration files from which Flyway takes its settings, but you could miss extra information, such as those configuration settings passed from an encrypted file, as we’ve just demonstrated. This is not the only problem. It is also possible to specify extra config files in a parameter from both the command line and as an environment setting, but these can’t all be picked up by these scripts.

A way around any problem we’ve introduced by providing for encrypted credentials is to pass to Flyway a placeholder that provides as its value the relative path to the file you’ve loaded in this way:

The .dpeci stands for Data Protection API Encrypted Config Input. It can, in PowerShell, do the same for a GPG file as well with the -placeholders.gpgci=<filename>.

These are then passed on, like every other placeholder parameter, and a PowerShell callback script can easily read this path value from an environment variable and use it to get the configuration values it needs from the encrypted file, using the method I’ve already described.

If you use the Flyway Teamwork Framework, the values are read for you automatically within preliminary.ps1 so as long as you call this within the callback, you’ll have all the correct values in place in the shared hashtable.

Conclusion

There are painless ways of meeting the security requirements of your organization when using Flyway. This goes beyond providing simple credentials and allows more complex cloud connections that demand several configuration settings. We can even, when necessary, encrypt all the details of your Flyway project. Once you have this system running, it can make it much easier to change settings and to store the different sets of credentials required for different database operations separately and securely on the same server.

This means that the development process no longer needs God-like powers for even the meanest scripted operations. It frees the user to use a system that allows several credentials to be used securely without the risk of exposing them, and to safely extend Flyway’s system of providing configuration settings.