Use an AWS IAM token to connect to a Postgres database using .pgpass

Jeroen Joris 0
Viewed 23

Within our company we've the wish to connect to an AWS RDS postgres database based on a generated IAM token. Mostly, we use DBeaver to connect to databases. However, with DBeaver a .pgpass is necessary to make the connection.

My question how can we achieve the connection with DBeaver towards our AWS RDS database using the IAM token?

Thanks in advance!

Gr,

Jeroen

amazon-rds amazon-iam amazon-web-services postgresql
1 Answers

You may be having issues due to not escaping the ':' char in the IAM token (see https://www.postgresql.org/docs/current/libpq-pgpass.html for requirements).

I use the below powershell script to udpate .pgpass files for AWS Aurora and it works fine with DBeaver 22.2. Note the $token = $token.Replace(':', '\:'); line. Script is basic but could to adapted to other use cases.

param (
    [Parameter(Mandatory = $true)][string]$hostname,
    [Parameter(Mandatory = $true)][string]$database,
    [Parameter(Mandatory = $true)][string]$username,
    [Parameter(Mandatory = $false)][int]$port=5432
)

$ErrorActionPreference = "Stop"

$region = $env:AWS_DEFAULT_REGION;
if ($null -eq $region) {
    $region = "us-east-1";
}

$token = aws rds generate-db-auth-token --hostname $hostname --port $port --region $region --username $username
if (!$?) { throw "failed to generate iam rds token"}
$token = $token.Replace(':', '\:');
$loc = $env:APPDATA + "\postgresql\pgpass.conf"
if ($null -eq $env:APPDATA) {
    $loc = $env:HOME + "/.pgpass";
} else {
    [IO.Directory]::CreateDirectory($env:APPDATA + "\postgresql");
}
$updated = "$($hostname):$($port):$($database):$($username):$token`n"
if (![IO.File]::Exists($loc)) {
    [IO.File]::WriteAllText($loc, $updated);
    return;
}
$replaced = "";
foreach ($line in [System.IO.File]::ReadLines($loc))
{
    if ($line.ToLower().StartsWith($hostname.ToLower())) {
        $replaced += $updated
    } else {
        $replaced += $line + "`n";
    }
}
[IO.File]::WriteAllText($loc, $replaced);
playsted0
Related