boltblog

Copying data from a restored postgres database to the live database

Unknownbolt | 2022-01-14

The correct way to copy data is not to extract to a CSV and import with some tool, to manually create INSERT queries, or to try filtering the pg_dump output. You should use the COPY function included in postgres, as such:

psql -c "COPY (SELECT * FROM my_table WHERE created_at > '2012-05-01') TO STDOUT;" source_db | psql -c "COPY my_table FROM STDIN;" target_db

Add psql parameters as necessary.

This does not replace the target table, it simply inserts what you’ve selected. If you want to replace the target, you need to TRUNCATE it first.

AWS RDS IAM Login pitfalls and checklist

Unknownbolt | 2021-12-07

So, you’re struggling with your IAM role login?

psql: error: FATAL: password authentication failed for user "mydbuser"
psql: error: FATAL: PAM authentication failed for user "mydbuser"

This list is specifically for PostgreSQL, accessed by ECS containers and developers, but most of it would apply to any IAM compatible setup.

First, check that you have allowed IAM logins to your database instance. Click “Modify” on your instance, and set it to “Password and IAM database authentication”:
Make sure you have created a database user to log in as, using CREATE USER in your database. This is not your IAM user, and does not need to have the same name.
Make sure the user is assigned the rds_iam role (GRANT rds_iam TO mydbuser). This should be visible when you do \du in psql, as “Member of” rds_iam
- The user also needs access to the tables/databases you want them to access
- A database user (again, not your IAM user) with the rds_iam role can not log in using their assigned password. …so if you’re migrating from database passwords to IAM and have applications still running, create a new user for this.
- If the rds_iam role is not assigned, you will get “password authentication failed” instead of “PAM authentication failed” if you fail at logging in as the user
For ECS containers, create a new role for the tasks to run as. This role needs to allow the rds-db:connect action on your database and user, all of which could be specified as “any” (*) if you want.
The AWS Console will help you with this:
- You could testing it out with arn:aws:rds-db:*:*:dbuser:*/* to allow everything, but that’s hardly recommended for production use.
- The rds-db:connect action is not included in AmazonRDSFullAccess default policy
- Your role should be added to the ECS task definition as the “Task role”, not the “Task execution role”
- A fully qualified ARN looks like this:
```
"arn:aws:rds-db:eu-central-1:827164351102:dbuser:db-6QRASSC7DASSW7P551LXR666CU/mydbuser"
```
- A completely open ARN looks like this:
```
"arn:aws:rds-db:*:*:dbuser:*/*"
```
For developers, you want the same policy as in the previous point, just applied to your user, or a group you’re in
Make sure you are using SSL. For psql you can do this by setting PGSSLMODE=require (or verify-ca if you’ve downloaded the AWS certificate bundle)
If your AWS account is part of an AWS Organizations setup, you need to add rds-db:* to the service control policy (SCP) of the organization unit that the account belongs to
If you’re a developer using AWS MFA, you need to have an active, functional session to obtain valid database tokens. As of 2021-12-07, the aws rds generate-db-auth-token command is perfectly happy with returning a token anyway, it just won’t work, and you’ll get “PAM authentication failed” once more
When creating db-auth-tokens from the CLI, it’s also important to use the correct, and full, database endpoint
I.e. you can’t use production-db.mydomain.com, 88.51.256.88 (the DB IP address), or any other reference to it, you need the hostname to be what’s listed as the “Endpoint” in the AWS Console, something like production-db.cdracwecaau0.eu-central-1.rds.amazonaws.com
Point 8 goes for ECS containers as well. You need to have it generating auth tokens for the “Endpoint” of the database, not anything else, so check your connection string. Also check it for SSL.
Access tokens are only valid for 15 minutes by default
When your container is correctly set up and running, you can run an interactive bash shell in it and check that a valid AccessKey is created by running
curl 169.254.170.2$AWS_CONTAINER_CREDENTIALS_RELATIVE_URI
This is what the AWS libraries will use to obtain the credentials, so it’s good to know they’re working. The result will also return the name of the role you assigned to the container.

A one-liner for testing your DB connection, inside a container (you’d have to install the relevant tools) or at a console:

DBENDPOINT="production-db.cdracwecaau0.eu-central-1.rds.amazonaws.com" DBPORT=5432 DBREGION="eu-central-1" DBUSER="mydbuser" DBNAME="business_app"; PGPASSWORD=$(aws rds generate-db-auth-token --region "$DBREGION" --hostname "$DBENDPOINT" --port "$DBPORT" --username "$DBUSER") PGSSLMODE=require psql -h "$DBENDPOINT" -U "$DBUSER" -d "$DBNAME" -p "$DBPORT" <<<"SELECT 'SUCCESS' AS OK"

For C#
- You can generate credentials based on the environment variables provided by AWS by doing Amazon.Runtime.FallbackCredentialsFactory.GetCredentials(false)
- You can generate a DB AuthToken directly using Amazon.RDS.Util.RDSAuthTokenGenerator.GenerateAuthToken(host, port, username)
- You use the DB AuthToken as the password in your connection string
- The System.Data.Common.DbConnectionStringBuilder class is very useful here, allowing you to easily parse a connection string, replace the passwords and generate an updated string without having to care about formatting, escapting and the like
- Remember to add SSL mode:
  connectionStringBuilder.Add(“SSL Mode”, “Require”);
  connectionStringBuilder.Add(“Trust Server Certificate”, “true”);
- Also, see this excellent blog post by Steve Gordon (archive) on how the AWS libraries go about obtaining your AccessKey

Allowing RDP remote passwords in Windows 10 in 2021

Unknownbolt | 2021-12-04

If you use saved passwords in your .rdp files on Windows, or from the command line on Linux, you may find yourself being prompted for a password anyway, after the latest updates. The option to allow this still exists, however, and can be found by running gpedit.msc (from the Run box, Win+R) and heading over to Local Computer Policy => Computer Configuration => Administrative Templates => Windows Components => Remote Desktop Services => Remote Desktop Session Host => Security.

What you’ll want to do is set “Always prompt for a password upon connection” to “Disabled“, as such:

Happy remoting.

DNS names for VirtualBox client machines

Unknownbolt | 2021-08-25

Following up on my previous post with the same topic, for Hyper-V, this is how to get DNS names for your VirtualBox guest VM’s into your hosts file on a Windows host.

First things first, you need the VirtualBox Guest Additions installed for the host to tell OS level information about the guest, just as with Hyper-V. Here’s a guide on how to install those on Debian (archive here). PowerShell on the host machine will have access to the IP addresses of the client machine. Well, not technically PowerShell in this case, but it can be used to run VBoxManage.exe and process its output.

Without the Guest Additions, VBoxManage.exe will not show any IP addresses for the client machine.

This is the script from the Hyper-V post, modified to do the same for VirtualBox, with the additional support for autostarting virtual machines in headless mode, as I found this lacking in VirtualBox by default. One of my VM’s, Sarge is used for example purposes. Replace this with your own VM names.

$ErrorActionPreference = "Stop"
$hosts = "$($env:windir)\System32\drivers\etc\hosts"

Start-Transcript -Path "C:\custom\vm_hosts.log" -Append

$autostart = @(
    'Sarge'
)

Write-Host -ForegroundColor Yellow "Autostarting machines"
$autostart | ForEach-Object `
{
    $name = $_
    try { & "C:\Program Files\Oracle\VirtualBox\VBoxManage.exe" startvm "$name" --type headless }
    catch {}
}

while ($true)
{
    Write-Host -ForegroundColor Yellow "Scanning for Virtual Machines"
    $content = Get-Content $hosts

    [array] $vms = & 'C:\Program Files\Oracle\VirtualBox\VBoxManage.exe' list runningvms
    $vmListRegex = '^\s*"([^"]+)"'
    $vms = $vms | Where-Object { $_ -match $vmListRegex }
    if ($vms)
    {
        $vms | ForEach-Object `
        {
            $vm = $_
            $name = [regex]::Match($vm, '^\s*"([^"]+)"').Captures.Groups[1].Value
            Write-Host -ForegroundColor Magenta "VM: $($name)"
            [array] $ips = & 'C:\Program Files\Oracle\VirtualBox\VBoxManage.exe' guestproperty get "$name" "/VirtualBox/GuestInfo/Net/0/V4/IP"
            $ip = [regex]::Match(($ips | Select -First 1), ': (\S+)').Captures.Groups[1].Value
            if ($ip)
            {
                Write-Host -ForegroundColor Green "IP: $($ip)"
                $hostsRegex = '^\s*\S+\s+' + $name.ToLower() + '$'
                $matches = $content | Select-String -Pattern $hostsRegex | ForEach-Object { $_.Line } | Out-String
                $wanted = $ip | ForEach-Object { $_ + ' ' + $name.ToLower() } | Out-String
                if ($matches -ne $wanted)
                {
                    $content = ($content | Select-String -Pattern $hostsRegex -NotMatch | ForEach-Object { $_.Line } | Out-String).Trim()
                    $content += "`n" + $wanted
                }
            }

            $content | Set-Content "${hosts}.tmp"
            if ((Get-FileHash "$hosts").Hash -ne (Get-FileHash "${hosts}.tmp").Hash)
            {
                Move-Item -Path "${hosts}.tmp" -Destination "$hosts" -Force
                Write-Host -ForegroundColor Yellow "Hosts file updated!"
            }
        }
    }

    Write-Host -ForegroundColor DarkGray "Sleeping..."
    Start-Sleep -Seconds 60
    Write-Host
}

I run it as a Scheduled Task, same as in the Hyper-V post.

Now I can easily connect to my virtual machines whenever I need to, without knowing their IP address.

DNS names for Hyper-V client machines

Unknownbolt | 2021-05-18

So, running a couple of Hyper-V machines, and needing to determine their local IP addresses to connect to them.

As long as the Hyper-V Integration Services are installed (apt-get install hyperv-daemons on Debian-based distributions), PowerShell on the host machine will have access to the IP addresses of the client machine.

Without the Integration Services, PowerShell will not show any IP addresses for the client machine.

Anyway, after making sure the integration services were installed on all the clients, I wrote this little script, which I run in the background, as an Administrator, with Task Scheduler, to keep the Windows hosts file updated: vm_hosts.ps1

You can download it from the above link, or copy/paste it from here:

$ErrorActionPreference = "Stop"
$hosts = "$($env:windir)\System32\drivers\etc\hosts"

while ($true)
{
    Write-Host -ForegroundColor Yellow "Scanning for Virtual Machines"
    $content = Get-Content $hosts
    Get-VM | ForEach-Object `
    {
        $vm = $_
        $regex = '^\s*\S+\s+' + $vm.Name + '$'
        Write-Host -ForegroundColor Magenta "VM: $($vm.Name)"
        $ips = @($vm.NetworkAdapters | Select -ExpandProperty IPAddresses)
        if ($ips.Count)
        {
            $matches = $content | Select-String -Pattern $regex | ForEach-Object { $_.Line } | Out-String
            $wanted = $ips | ForEach-Object { $_ + ' ' + $vm.Name } | Out-String
            if ($matches -ne $wanted)
            {
                $content = ($content | Select-String -Pattern $regex -NotMatch | ForEach-Object { $_.Line } | Out-String).Trim()
                $content += "`n" + $wanted
            }
        }
    }

    $content | Set-Content "${hosts}.tmp"
    if ((Get-FileHash "$hosts").Hash -ne (Get-FileHash "${hosts}.tmp").Hash)
    {
        Move-Item -Path "${hosts}.tmp" -Destination "$hosts" -Force
        Write-Host -ForegroundColor Yellow "Hosts file updated!"
    }

    Write-Host -ForegroundColor DarkGray "Sleeping..."
    Start-Sleep -Seconds 60
    Write-Host
}

In case you’re interested, it’s running as a Scheduled Task, triggering Daily at 00:00, repeated every 5 minutes, for a duration of 1 day, but set to not start another instance if it is already running. This will make sure it is always restarted after 5 minutes if any of the PowerShell commands throw an error, such as when Hyper-V decides to throw up for some reason. The Action is set to start PowerShell.exe with the following arguments:

-WindowStyle Hidden -NoProfile -NoLogo -NonInteractive -ExecutionPolicy Bypass -File "C:\custom\vm_hosts.ps1"

Now I can easily connect to my virtual machines whenever I need to, without knowing their IP address.

Office Volume License Keys (VLK)

Unknownbolt | 2021-05-06

I keep having to look these up, so I stored them here.

Office 2019

Product	KMS Client Setup Key
Office Professional Plus 2019	NMMKJ-6RK4F-KMJVX-8D9MJ-6MWKP
Office Standard 2019	6NWWJ-YQWMR-QKGCB-6TMB3-9D9HK
Project Professional 2019	B4NPR-3FKK7-T2MBV-FRQ4W-PKD2B
Project Standard 2019	C4F7P-NCP8C-6CQPT-MQHV9-JXD2M
Visio Professional 2019	9BGNQ-K37YR-RQHF2-38RQ3-7VCBB
Visio Standard 2019	7TQNQ-K3YQQ-3PFH7-CCPPM-X4VQ2
Access 2019	9N9PT-27V4Y-VJ2PD-YXFMF-YTFQT
Excel 2019	TMJWT-YYNMB-3BKTF-644FC-RVXBD
Outlook 2019	7HD7K-N4PVK-BHBCQ-YWQRW-XW4VK
PowerPoint 2019	RRNCX-C64HY-W2MM7-MCH9G-TJHMQ
Publisher 2019	G2KWX-3NW6P-PY93R-JXK2T-C9Y9V
Skype for Business 2019	NCJ33-JHBBY-HTK98-MYCV8-HMKHJ
Word 2019	PBX3G-NWMT6-Q7XBW-PYJGG-WXD33

Office 2016

Product	KMS Client Setup Key
Office Professional Plus 2016	XQNVK-8JYDB-WJ9W3-YJ8YR-WFG99
Office Standard 2016	JNRGM-WHDWX-FJJG3-K47QV-DRTFM
Project Professional 2016	YG9NW-3K39V-2T3HJ-93F3Q-G83KT
Project Standard 2016	GNFHQ-F6YQM-KQDGJ-327XX-KQBVC
Visio Professional 2016	PD3PC-RHNGV-FXJ29-8JK7D-RJRJK
Visio Standard 2016	7WHWN-4T7MP-G96JF-G33KR-W8GF4
Access 2016	GNH9Y-D2J4T-FJHGG-QRVH7-QPFDW
Excel 2016	9C2PK-NWTVB-JMPW8-BFT28-7FTBF
OneNote 2016	DR92N-9HTF2-97XKM-XW2WJ-XW3J6
Outlook 2016	R69KK-NTPKF-7M3Q4-QYBHW-6MT9B
PowerPoint 2016	J7MQP-HNJ4Y-WJ7YM-PFYGF-BY6C6
Publisher 2016	F47MM-N3XJP-TQXJ9-BP99D-8K837
Skype for Business 2016	869NQ-FJ69K-466HW-QYCP2-DDBV6
Word 2016	WXY84-JN2Q9-RBCCQ-3Q3J3-3PFJ6

Activation

To activate office with the above keys, see this reference on Microsoft’s site (backup here).

Finding and killing stuck or mistyped postgresql queries

Unknownbolt | 2021-05-05

I’ve found these useful during development:

Listing running queries:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid();

Listing queries that have run for more than a full minute:

SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() AND (now() - pg_stat_activity.query_start) > interval '1 minute';

Try to cancel a single query:

SELECT pg_cancel_backend(<pid>);

Force kill a single query:

SELECT pg_terminate_backend(<pid>);

Force kill all queries that have been running for over 10 minutes:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() AND (now() - pg_stat_activity.query_start) > interval '10 minutes';

Force kill all queries using the user_accounts table (or mentioning user_accounts at all, this isn’t limited to table names):

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'active' AND pid <> pg_backend_pid() AND query LIKE '%user_accounts%';

Hope someone finds this useful.

Web Echo Service for developers

Unknownbolt | 2021-03-29

I recently had need of a service that would return your exact query to you for web client (or proxy) development purposes, in a case where using Fiddler, Wireshark, tcpdump et.al. was proving difficult for various reasons. The solution became the C-Net Echo Service, available at https://echo.c-net.org/ (and over plain HTTP if you need it). Feel free to use this for whatever.

Example usage:

$ curl -X PUT -d "Hello World!" -H "User-Agent: Fake Agent" -H "CUSTOM-HEADER: foo" -H "X-Forwarded-For: 1.2.3.4" "https://echo.c-net.org/"
PUT / HTTP/1.1
Host: echo.c-net.org
Accept: */*
User-Agent: Fake Agent
CUSTOM-HEADER: foo
X-Forwarded-For: 1.2.3.4
Content-Type: application/x-www-form-urlencoded
Connection: Keep-Alive
Content-Length: 12

Hello World!

Generating a C# library from swagger.json using Docker for Windows

Unknownbolt | 2021-02-09

Just a short reference for myself (and maybe for you). How to run Docker for Windows, mount a local file system drive inside of the container, grab the swagger.json file off of a URL, and generate a library:

Using swagger-codegen-cli (old):

docker run -it -v //c/temp/swagger:/local --network host --rm swaggerapi/swagger-codegen-cli generate -i "http://docker.for.win.localhost:5000/swagger/v1/swagger.json" -l csharp -o /local

Using openapi-generator-cli (new):

docker run -it -v //c/temp/swagger:/local --network host --rm openapitools/openapi-generator-cli generate -i "http://docker.for.win.localhost:5000/swagger/v1/swagger.json" -g csharp -o /local

In the examples, the generators connect to a webserver running on the local Windows host at port 5000, and outputs “csharp”. Change this as appropriate.

Oh, and another thing: To make NSwag generate the swagger.json correctly in a .Net project, the <GenerateDocumentationFile> property needs to be set.

<PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <AspNetCoreHostingModel>InProcess</AspNetCoreHostingModel>
    <GenerateDocumentationFile>true</GenerateDocumentationFile>
</PropertyGroup>

Remote Desktop (RDP) to a computer that’s enrolled in Azure AD

Unknownbolt | 2021-01-07

If you attempt to connect to a machine that’s enrolled in Azure AD from a workstation that’s not part of the domain, like your desktop or laptop at home, you’ve likely encountered this issue.

Firstly, we should get a few basics out of the way. You need a correct username and password. When your user is in AzureAD, you need to specify your username as such:

You also need to log into the target machine and allow remotely showing the login screen through RDP, which means disabling the requirement to use Network Level Authentication. This will allow you to log in interactively:

Note that the checkbox must be deselected for interactive login sessions to work.

Now we need to go about modifying our .rdp files. You can get a .rdp file by pressing “Save As” in the Remote Desktop Connection window. Open the file with Notepad (or your favorite editor), and you’ll get lines such as these:

At the end of the file, we need to enable support for CredSSP if you want to connect to an AzureAD enrolled machine. Add a line like this:

enablecredsspsupport:i:0

You can also put your username in the file, with the domain, as such:

enablecredsspsupport:i:0
username:s:\AzureAD\myazureuser@domain.com

You can also put your password in the file, though I don’t recommend the latter. This is likely to be a company computer, after all. Check your IT policies and proceed at your own risk.

Here be monsters.

Passwords in the .rdp file can be encrypted as SecureStrings. You can generate them with PowerShell. Keep in mind that SecureStrings are encrypted using your current account, so you have to do this while logged in on the machine on which you want to use the .rdp file. The client, not the remote machine.

> "Hunter2" | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString
01000000d08c9ddf0115d1118c7a00c04fc297eb010000000f16d2079232d84babe49f4bac6617ef0000000002000000000003660000c00000001000000072b47796dcf3b4df632972f45d83ff8a0000000004800000a000000010000000230f0d13e136c1543b80d8f6c3a206e110000000ff1532a09e3140c31fabac391962d02614000000686ce522def23fb7921c0029305060e0fd28042b

All of that garble is your encrypted password (Hunter2). Your password is now also stored in your PowerShell. Not good. If you do this without specifying the password, it’ll ask you. Better. Slightly.

ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString

You can put the password (if you really want to) into your .rdp file as such:

enablecredsspsupport:i:0
username:s:\AzureAD\myazureuser@domain.com
password 51:b:01000000d08c9ddf0115d1118c7a00c04fc297eb010000000f16d2079232d84babe49f4bac6617ef0000000002000000000003660000c000000010000000e686a1ac13248c81a2c9abb56082452f0000000004800000a0000000100000002a071ce0035f888093d04bdc4d8716aa100000005590842a9ba86481072a0e444971e7e014000000871ea7bed2d1abdc67222644e9a4c98addb76b7b

Note: The syntax is “password 51:b:encryptedpasswordhere“. There’s a space, not a newline. Don’t trust your browser.

Happy remote working!