Simple Safeguards for PowerShell Scripting with Flyway
This article demonstrates simple techniques to security-check any processes that use Bash or PowerShell scripts to automate database tasks, when using Flyway. These checks help ensure a script is trusted, hasn't been tampered with since creation, and doesn't contain commands commonly used with malicious intent. They add a valuable layer of protection, without sacrificing the power and flexibility that makes Flyway so effective.
Ensuring data security is a fundamental responsibility in IT management. Many organizations have failed to meet this obligation, and the resulting data leaks, identity theft, and fraud have led to public reprimands, legal action and fines. While attention often focuses on external threats, it’s important to remember that malicious activity also comes from within. On three occasions in my long IT career, I’ve seen developers caught in the act and escorted off the premises clutching their desk contents in a bin liner.
Even seemingly innocuous database scripts can be a gateway to exploitation, and a development database containing real data becomes a high-value target. It’s essential to periodically review development practices to ensure they meet current security standards.
Flyway makes database automation easy, allowing developers to write PowerShell, Java or Bash scripts that generate reports, run code reviews, or update documentation during migration runs. However, this flexibility makes it even more important to follow good scripting practices, especially if those scripts might have access to sensitive data or production systems. A compromised script might expose credentials, leak data, or silently alter production systems.
Flyway’s handling of SQL migrations includes built-in safeguards like checksum validation. However, external scripts, like PowerShell or Bash callbacks, aren’t subject to the same protection and so need a bit of extra care. Fortunately, with a few simple checks, such as static code searches for malicious patterns and script signing, we can guard against unauthorized changes to external scripts with the same tenacity that Flyway brings to validating SQL migrations.
Understanding the risks of scripted automation
Scripts like PowerShell or Bash run with the privileges of the user who executes them. This makes them powerful tools for automation but also means they can do damage if misused or compromised, especially if that user has access to sensitive data or Personally Identifiable Information (PID).
When used in database development, such scripts often have access to environment variables (including credentials), can read or modify files, and connect directly to databases. Even when using integrated authentication, they run in the user’s security context and can access anything the user can. A short block of code inserted into a PowerShell script by a malicious actor could lead to unauthorized data access and extraction, modification or deletion of critical database entries, or the insertion of malicious data or code into the database. If the release accidentally uses compromised PowerShell callbacks associated with development to update the production server (rather than merely the ‘dry run’ script) this will happen to the live data.
Flyway, like most automation tools, supports these scripting languages for task automation, and by adding a few lightweight security checks, teams can ensure these scripts remain safe, unchanged, and appropriately scoped.
Mitigation strategies: check, sign and verify scripts
I’ve no desire to explore here the many ways available to Ops teams to detect suspicious activity by ‘threat actors’ within the network. A corporate network is liable to be secure, but it nevertheless falls to development teams to find commonsense practical ways to check that it isn’t happening. Much of the work of source and configuration management aims to assist with this.
For any scripted approach to database development, there are two ways to be confident your scripts haven’t been tampered with or used to compromise security. First, we can use regular expressions (regex) to search for signs that a script contains commands or utilities often used with malicious intent. You’ll never cover every edge case, and a regex can’t reliably distinguish intent, but it can catch the most blatant issues. Once a script passes that check, your second job is to make sure it stays unchanged.
I’ll provide some PowerShell functions that offer a drop-in way to implement these checks and raise flags on potentially unsafe behavior or malicious access:
Find-SecurityConcerns– uses regex to inspect scripts for patterns common to malicious behavior (e.g., use ofInvoke-WebRequest,Invoke-Expression, or base64 obfuscation).Sign-FilesandVerify-Files– sign trusted scripts with GPG and then verify those signatures before execution to ensure nothing in the callbacks has changed since they were initially validated and signed.
The GPG “Verify-Files” check can be done in a callback, such as a beforeMigrate, before a migration run. . The code you use would need to look for callbacks in all your Flyway ‘locations’ (the configuration item that provides a list of all directories to search for migration or callback files).
The integrity check (Find-SecurityConcerns) is designed to be run when you have many callback scripts that need to be checked through before being signed. It isn’t particularly suitable to be run in a callback.
These checks don’t take much time to run and can be built into existing pipelines that use PowerShell scripting for automation. They attempt to provide the necessary oversight, without undermining the flexibility a tool like Flyway provides.
Signing files and then checking if they have changed
It is a good practice to validate the origin of the scripts before use. This is best done with cryptographic signatures. I like to sign files using GPG and then, before using them, check whether the signature is still valid. This verifies that the scripts are from a trusted source, as well as indicating if they have changed since they were signed.
As an alternative, it’s possible to verify the integrity of scripts using checksums, in the same way Flyway does for migration files. You can check the ‘last modified’ date of the file with a record of its value and, if it has changed, then run a checksum on the file to compare the result with your records.
Creating GPG signatures
The Sign-Files routine below can create either a detached GPG signature or a ‘clearsign’ signature. For our purposes, we use a detached signature, which stores the cryptographic signature in a separate file, rather than embedding it within the signed file as clearsign does. This keeps the original file unaltered, preserving its format and structure, while allowing you to verify its integrity and authenticity.
To validate a file with a detached signature, you need both the original file and its corresponding signature file (.gpg), using the correct GPG key to confirm that the file has not been tampered with since it was signed.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
<# .SYNOPSIS A PowerShell script to sign files using GPG. .PARAMETER Files The list of files (ambiguous or unambiguous) to be signed. .PARAMETER ClearSign If specified, performs a 'clearsign' signature instead of a detached signature. .PARAMETER KeyID Specifies the GPG key to use for signing. .EXAMPLE Sign-Files -Files "C:\path\file.txt" .EXAMPLE Sign-Files -Files "C:\path\file.txt" -KeyID='125467' #Signs file.txt using the specified GPG key. #> function Sign-Files { param ( [Parameter(Mandatory = $true)] [array]$paths, [string]$KeyID = $null ) $files = $paths | foreach{ dir $_ } | foreach{ $_.Fullname } foreach ($File in $Files) { if (-Not (Test-Path $File -PathType Leaf)) { Write-error "File not found: $File" continue } if (!([string]::IsNullOrWhiteSpace($DemoString))) { write-verbose "local user '$KeyID'" $LocalUser = "--local-user $KeyID" } else { $LocalUser = "" } if (-Not (Test-Path "$File.gpg" -PathType Leaf)) { gpg $LocalUser --sign $File if ($LASTEXITCODE -ne 0) { write-verbose 'GPG Error' } Write-verbose "file: $File now signed" } else { Write-verbose "file: $File is already signed" } } } |
Verifying the file signatures
The Verify-Files PowerShell function checks whether each script has a valid detached signature using GPG. It can be incorporated into your deployment pipeline to validate script integrity at runtime.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
<# .SYNOPSIS Verifies the GPG signature of files. .DESCRIPTION Checks if a file has a valid GPG signature. .PARAMETER Paths The paths of the files to verify. .EXAMPLE $Output= Verify-files '.\Branches\develop\routines\*.ps1' .EXAMPLE ".\Branches\develop\routines\*.gpg" | Verify-Files .output A report in the form of a hashtable with the list of files and the success or failure along with GPG's output #> function Verify-Files { [CmdletBinding()] param ( [Parameter(Mandatory = $true, ValueFromPipeline = $true)] [array]$Paths ) $Files = $Paths | ForEach-Object { Get-Item $_ } | ForEach-Object { $_.FullName } foreach ($File in $Files) { if (-Not (Test-Path $File -PathType Leaf)) { Write-Error "File not found: $File" continue } Write-Verbose "Verifying signature for: $File" $GoodSignature=$false;# assume the worst # Ensure we don't append ".gpg" twice $GpgFile = if ($File -match '\.gpg$') { $File } else { "$File.gpg" } # Run GPG verification, capturing both stdout & stderr $Output = gpg --verify "$GpgFile" 2>&1 if ($Output -match "Good signature") { $GoodSignature=$true; Write-Verbose "✅ Valid signature detected for $File! $Output" } else { Write-Verbose "❌ Signature verification failed for $File! $Output" } [psCustomObject]@{'File'=$file;'Success'=$GoodSignature;'Report'="$Output"} } } |
Implementing a Script Integrity Check
In this step, we run automated checks on our callback scripts to catch obvious signs of malicious intent. The Find-SecurityConcerns PowerShell function performs a simple regex-based scan across script files, flagging potentially dangerous usage patterns (“Known Malicious Patterns”), like commands that can exfiltrate data, spawn processes, or obscure payloads through encoding.
Currently, it checks for the use of the following PowerShell commands and utilities:
- Invoke-WebRequest: This command can be used to make HTTP requests, which may be used to exfiltrate data or download malicious payloads.
- Invoke-Expression: Evaluates a string as a command. It can be exploited to execute arbitrary code, making it a potential vector for attacks.
- curl and wget: These utilities can be used to download files from the internet, posing a risk for downloading malicious content.
- scp: Secure copy command used for transferring files between hosts, which could be utilized for data exfiltration.
- Start-Process: Starts a new process, which may be used to execute external programs with malicious intent.
- Start-Job: Initiates a background job, potentially hiding malicious activities from immediate detection.
- New-Object System.Net.WebClient: Creates a web client object that can download or upload data, posing a risk for data exfiltration or malicious downloads.
- FromBase64String: Converts a Base64 encoded string to binary data, often used in obfuscation techniques to hide malicious payloads.
- ConvertTo-SecureString: Converts a plain text string to a secure string, which might be used to handle sensitive data improperly.
- [System.Text.Encoding]::UTF8.GetString: Decodes a byte array to a UTF-8 string, possibly used in obfuscation or data manipulation.
- reg add and reg delete: These commands modify the Windows registry, potentially altering system configurations and permissions maliciously.
This isn’t a security audit, but it’s an effective way to spot common red flags across your script directories. With this function you can also whitelist files that you know to be safe, define custom patterns, and optionally log findings, making it well suited to run as part of a pre-deployment validation step.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 |
<# .SYNOPSIS Checks directories of PowerShell code for any potential security concerns .DESCRIPTION This is intended as a check whenever credentials of other secure information are held in environment variables. .PARAMETER MigrationPaths a list of migration files to check. .PARAMETER WhitelistFile A a list of files you don't want checked .PARAMETER $CustomRegexPatterns A description of the WhitelistFile parameter. .PARAMETER $JustReportSuspectFiles Set to false if you want a list of both files that failed and passed .EXAMPLE Find-SecurityConcerns -MigrationPaths @('<myCallbacks>\*.ps1') Find-SecurityConcerns -MigrationPaths @('<myCallbacks>\FalsePositives.ps1') #> function Find-SecurityConcerns { [CmdletBinding()] param ( [Parameter(Mandatory = $false)] [array]$MigrationPaths = @("./sql", "./migrations", "./callbacks"), # Paths to scan [string]$WhitelistFile = $null, # Path to whitelist file [Array]$CustomRegexPatterns = $null, #Regex patterns to use instead [int]$JustReportSuspectFiles = $true, #Rtrue if you just want to report suspect files [Array]$AdditionalRegexPatterns = $null #Regex patterns to use As Well ) $LogMessage = { param ([string]$Message) if ($LogFile -ne $null) { if (Test-Path $LogFile) { $timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss" "$timestamp - $Message" | Out-File -Append -FilePath $LogFile Return (true) } } } $GetWhitelist = { if ($WhitelistFile -ne $null) { if (Test-Path $WhitelistFile) { return Get-Content $WhitelistFile } } return $null } # Define suspicious patterns <# If you don't provide an array of regexes, The script will flag any callback script containing the following Suspicious Commands Invoke-WebRequest, curl, wget, scp, Invoke-Expression (These can send data externally or execute code dynamically.) Process Spawning & PowerShell Abuses Start-Process, Start-Job, New-Object System.Net.WebClient (Common for malware or persistence.) Base64 or Hex Encoding (Obfuscation) FromBase64String, ConvertTo-SecureString, [System.Text.Encoding]::UTF8.GetString (Attackers use these to hide payloads.) Registry Modifications reg add, reg delete, Set-ItemProperty (Might indicate persistence mechanisms.) #> $regexPatterns = @( "Invoke-WebRequest", "Invoke-Expression", "curl", "wget", "scp", "Start-Process", "Start-Job", "New-Object System.Net.WebClient", "FromBase64String", "ConvertTo-SecureString", "\[System.Text.Encoding\]::UTF8.GetString", "reg add", "reg delete" ) $whitelist = $GetWhitelist.Invoke $flaggedFiles = @() $concerns = 0; if ($CustomRegexPatterns -ne $null) {$regexPatterns=$CustomRegexPatterns} if ($AdditionalRegexPatterns -ne $null) {$regexPatterns+=$AdditionalRegexPatterns} $MigrationPaths | foreach{ Write-verbose "checking $_ " $path = $_; if (-Not (Test-Path $path)) #check that the path exists { Write-Host "⚠️ Warning: Migration path '$path' does not exist. Skipping." continue } Get-ChildItem -Path $path -Include "*.ps1", "*.bat", "*.sh" -Recurse | foreach{ $Concerns = 0 }{ $file = $_ Write-verbose "Checking through $file ..." if ($whitelist -contains $file.FullName) { if (!($JustReportSuspectFiles)) { Write-Host "✅ Whitelisted: $($file.FullName)" } continue } $content = Get-Content $file.FullName -Raw $ThisFile = @{ Filename = $file.Fullname; 'concern' = @() } foreach ($pattern in $regexPatterns) { if ($content -match $pattern) { $Concerns += 1; $ThisFile.Concern += "$pattern" } } $ThisFile } | foreach{ # Report findings $array = @() if ($_.Concern.Count -gt 0) { $array = [array]($_.Concern | Sort-Object | Get-Unique) #((0..($array.count-2)|%{"'$($array[$_])'"}) -join ', ')+" and '$($array[($array.count-1)])'" $suspiciousStuff = switch ($array.count) { (1) { "'$($array[0])'" }(2) { "'$($array[0])' and '$($array[1])'" } default { ((0..($array.count - 2) | %{ "'$($array[$_])'" }) -join ', ') + " and '$($array[($array.count - 1)])'" } } $report = "⚠️ Security Warning: $($_.Concern.Count) Suspicious commands $suspiciousStuff found in $($_.Filename)!" $LogMessage.Invoke($report) write-host $report } else { if (!($JustReportSuspectFiles)) { write-host "✅ No potentially malicious patterns detected in $($_.Filename)!" $LogMessage.Invoke("No issues detected in callback $($_.Filename).") } } } } if ($concerns -eq 0) { Write-Host "✅ No potentially malicious patterns detected in callbacks." $LogMessage.Invoke("No issues detected in callbacks.") } else { Write-Host "⚠️ Security Warning: $concerns Suspicious commands found in callbacks!" } } |
Conclusion
However secure a corporate development environment may be, it makes sense to tackle security at every level. While integrating PowerShell scripts into Flyway migrations offers substantial benefits, it is part of the task of supervising development work to ensure that there are no security risks associated with it. Implementing a PowerShell function to check scripts for malicious alterations can significantly enhance the security of the database development environment. By proactively validating scripts, organizations can protect sensitive data, maintain user trust, and ensure the integrity of their database systems.
These simple techniques can help teams to security-check their Flyway automation processes, without losing the power and flexibility that makes a database migration tool like Flyway so effective.
