Question 71874 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Use an AWS IAM token to connect to a Postgres database using .pgpass – Amazon web services

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

Tags:

2

Answers


  1. 0

    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 + "postgresqlpgpass.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);
    Login or Signup to reply.
  2. 0

    Here’s how I was able to get DBeaver Community Edition working with PostgreSQL PgPass and AWS IAM authentication:

    % export AWS_REGION=us-east-2
% export RDSHOST=example.cluster-ro-abcde12345.us-east-2.rds.amazonaws.com
% export RDSDBNAME=postgres
% export RDSUSER=skohrs
% echo "$RDSHOST:5432:$RDSDBNAME:$RDSUSER:$(aws rds generate-db-auth-token --hostname $RDSHOST --port 5432 --region $AWS_REGION --username $RDSUSER | sed 's/:/\:/')" >> $HOME/.pgpass

    DBeaver Connection Settings:
    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search