Aws-sdk-go: iam db authentication for postgresql

[KarthickEmis]

Is this related to a problem?

A clear and concise description of the issue, e.g. I'm always frustrated when...

Feature description

Describe what you want to happen.

Describe alternatives you've considered

Any alternative solutions or features you've considered.

Additional context

Add any other context or screenshots about the feature request here.

Currently this project is having iam db authentication for mysql.

How to connect to Aurora postgresql using iam db authentication?

[diehlaws]

Hi @KarthickEmis, thanks for reaching out to us and I do apologize for the delay in response. Using IAM DB Authentication to connect to Aurora Posgresql is very similar to IAM DB authentication for MySQL. You'll want to ensure your engine version is high enough to support IAM DB Authentication (I used 10.7 in my test) and follow these steps to ensure you have a database account that has IAM DB authentication granted to it, from there the code example for rdsutils.BuildAuthToken should work with minor changes:

1. You'll want to import a postgres driver such as pq in order to open a connection to your database
2. In the sql.Open() command you'll want to replace "mysql" with "postgres" to ensure you are using the correct driver when connecting to your database.

Please don't hesitate to reach back out to us if you need further assistance with this.

[KarthickEmis]

Hi @KarthickEmis, thanks for reaching out to us and I do apologize for the delay in response. Using IAM DB Authentication to connect to Aurora Posgresql is very similar to IAM DB authentication for MySQL. You'll want to ensure your engine version is high enough to support IAM DB Authentication (I used 10.7 in my test) and follow these steps to ensure you have a database account that has IAM DB authentication granted to it, from there the code example for rdsutils.BuildAuthToken should work with minor changes:

1. You'll want to import a postgres driver such as pq in order to open a connection to your database
2. In the sql.Open() command you'll want to replace "mysql" with "postgres" to ensure you are using the correct driver when connecting to your database.

Please don't hesitate to reach back out to us if you need further assistance with this.

Hi, Thanks for your reply...
I tried in two ways ,
First I tried with the same you specified(rdsutils.BuildAuthToken ) , but throwing the below error.
panic: dial tcp 127.0.0.1:5432: connect: connection refused

awsCreds := stscreds.NewCredentials(session.New(), "**lambdarolearn**")
authToken, err := rdsutils.BuildAuthToken(fmt.Sprintf("%s:%d", config["dbhost"], 5432),
        awsRegion, "test", awsCreds)
connectStr := fmt.Sprintf("%s:%s@tcp(%s)/%s?allowCleartextPasswords=true&tls=rds",
        "test", authToken, config["dbhost"], config["dbname"])
db, err = sql.Open("postgres", connectStr)
fmt.Printf(" ping db dnsStrr: %s\n", db.Ping())

When i tried in the another way , it throws panic: pq: password authentication failed for user "test"

PFB my code for using iam db authentication

awsCreds := stscreds.NewCredentials(session.New(), "**lambdarolearn**")
authToken, err := rdsutils.BuildAuthToken(fmt.Sprintf("%s:%d", config["dbhost"], 5432),
        awsRegion, "test", awsCreds)
dnsStrr := fmt.Sprintf("host=%s port=%d user=%s dbname=%s sslmode=require password=%s",
        config["dbhost"], 5432, "test", config["dbname"], authToken)
db, err = sql.Open("postgres", dnsStrr)
fmt.Printf(" ping db dnsStrr: %s\n", db.Ping())

Kindly let me know how to connect to aurora postgresql using iam db authentication.If you have any working example kindly let me know how it is working.
Note: I created 10.7 engine version of aurora postgresql

[bjmc]

Hello, I'm running into the same issues, and would greatly appreciate some clarification.

@KarthickEmis I've seen the same error you were getting with panic: dial tcp 127.0.0.1. I think the issue is DSN string parsing in the pq library. Without postgresql:// at the start of the string, it doesn't know to interpret it as a URN, and likely falls back to a default of localhost. I've also had parsing problems because the security token contains the hostname, so if you pass it unescaped in the password section, it confuses the URN parser.

Like you, I changed from the URN format to the key=value style DSN and I'm getting better results that way.

@diehlaws I've noticed that if I generate an IAM RDS token using the command line tools, I get a much shorter token, and that token _does_ work to connect to RDS:

$ aws rds generate-db-auth-token --hostname "assetsvc-dev.cfuosmcvqhmr.eu-west-1.rds.amazonaws.com" --port 5432 --region eu-west-1 --username listener > token
$ cat token
assetsvc-dev.cfuosmcvqhmr.eu-west-1.rds.amazonaws.com:5432/?Action=connect&DBUser=listener&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=AKIAWHS7GR7QWGRME3HK%2F20191121%2Feu-west-1%2Frds-db%2Faws4_request&X-Amz-Date=20191121T180221Z&X-Amz-Expires=900&X-Amz-SignedHeaders=host&X-Amz-Signature=ea6c92bb18e20bd17e826adcb8be29a643f5241df47aaa8657fd9e03e4ba54a4

The working token from the CLI tools is only 373 chars long and contains these fields:

• Action
• DBUser
• X-Amz-Algorithm
• X-Amz-Credential
• X-Amz-Date
• X-Amz-Expires
• X-Amz-SignedHeaders
• X-Amz-Signature

In contrast, the token returned by rdsutils.BuildAuthToken is 1230 characters long, with a different format:

assetsvc-dev.cfuosmcvqhmr.eu-west-1.rds.amazonaws.com:5432?Action=connect&DBUser=listener&X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=ASIAWHS7GR7QQBLEFKCO%2F20191121%2Feu-west-1%2Frds-db%2Faws4_request&X-Amz-Date=20191121T164430Z&X-Amz-Expires=900&X-Amz-Security-Token=IQoJb3JpZ2luX2VjEMj%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FwEaCWV1LXdlc3QtMSJHMEUCIQCP4gOih5kj4WceKMbxzxkSHcII7IRCkLKBEDUM1961TAIgHa5VF4g00y6S0GOjnqVZGGrcI1dJJhlng%2FsxfKws9c4q7wEI8f%2F%2F%2F%2F%2F%2F%2F%2F%2F%2FARAAGgw0Mjg2MjI2NDcyNjUiDL2uWiIFqGnsnPwxiSrDAejeP1IGHeO0Z1NAxcElHHhmeFwpUriJv9vqfrElEkzrqOyVfINzgzI7GPoaU63m8K70ALJA87sHMGl7%2BbYZebnaummG4JnuVPVnn7tR0HlYRwr9ZZUJhlxmAdVdcMpEuPfN50WaG7mQtq%2FXsMKSCzYhA%2ByjXB3bfOhMi68gBVPVQ7zyh%2BKZgkzB3%2BqQyUWqcO3ZX3MoD8KnuXP4IWLG7FKEDP92iynDgnJR7Cl%2FbkdbaEmfmBYu1N73ybGHqfK0vuKX2DC85truBTrgAVPFFA2BhYkQK6HooVyuye6NbYcxWtw3aWLEhy8mUJsvsWkGfG6fH24Bt8m4hqMOufKSPFiWmBKhlCAhH0JCM20GvL6xXAObzhuBIUTVRsSW7tz561iVwrZTrIUVCsMTphTdttHAR%2BpEJuMhTSys9SYn5FqZDRxLMCjHYWfp3d8Z1mIvJYOhNucXaYk6srDhKRYnCMeVe4yT8gUTDZbgsG8hNlzK2%2FUInQbYElpyJEseU6y6CZVeFACWhvtD8FCzEBN%2BXKCFqkqZ5JYjiRUBcDp2osayWmF6bYuzAMphPr5W&X-Amz-SignedHeaders=host&X-Amz-Signature=02d9aa1fbfe574b000695f8e988253f878d010b13f705a847fca587e3c56c7d9

And it contains these fields:

• Action
• DBUser
• X-Amz-Algorithm
• X-Amz-Credential
• X-Amz-Date
• X-Amz-Expires
• X-Amz-Security-Token
• X-Amz-SignedHeaders
• X-Amz-Signature

The token from rdsutils does _not_ work. I get an error like this:

panic: DB Ping() failed: pq: PAM authentication failed for user "listener"

Can anyone clarify the difference, and explain how to use rdsutils with Postgres RDS/Aurora? A complete working example using rdsutils.BuildAuthToken with the pq driver would be extremely helpful.

[bjmc]

@diehlaws Would it be helpful if I opened a separate issue with a minimal example showing my own code? Or is it easier to keep discussion concentrated here?

Thanks for any advice or examples you can offer.

[sunnylbk]

I am encountering a similar issues as here. Please provide a small example on how to use it.

Thanks

[sunnylbk]

I was able to get this working. I was passing extra port value to the host earlier while generating the token and it was leading to failed: pq: PAM authentication failed for user error

Below is the code

func authToken(dbHost, dbUser, region, roleARN string) (token string, err error) {
    awsCreds := stscreds.NewCredentials(services.NewSession(aws.NewConfig()), roleARN)
    token, err = rdsutils.BuildAuthToken(dbHost, region, dbUser, awsCreds)
    return
}

func main() {
    roleARN := os.Getenv("DB_ACCESS_ROLE_ARN")
    region := os.Getenv("AWS_REGION")
    dbHostPort := os.Getenv("DB_HOST") // <host>:<port>
    dbUser := os.Getenv("DB_USER")
    dbName := os.Getenv("DB_NAME")
    dbHost := strings.Split(dbHostPort, ":")[0]
    dbPort := strings.Split(dbHostPort, ":")[1]

    token, err := authToken(dbHost, dbUser, region, roleARN)
    if err == nil {
        // http://gorm.io/docs/connecting_to_the_database.html
        dbURI := fmt.Sprintf("host=%s port=%s user=%s dbname=%s password=%s", dbHost, dbPort, dbUser, dbName, token)
        _, err := gorm.Open("postgres", dbURI)
        if err != nil {
            log.Panic("Could not get connection")
        }
    }
}

@KarthickEmis @bjmc ^

[KarthickEmis]

I was able to get this working. I was passing extra port value to the host earlier while generating the token and it was leading to failed: pq: PAM authentication failed for user error
Below is the code
func authToken(dbHost, dbUser, region, roleARN string) (token string, err error) {
awsCreds := stscreds.NewCredentials(services.NewSession(aws.NewConfig()), roleARN)
token, err = rdsutils.BuildAuthToken(dbHost, region, dbUser, awsCreds)
return
}

func main() {
roleARN := os.Getenv("DB_ACCESS_ROLE_ARN")
region := os.Getenv("AWS_REGION")
dbHostPort := os.Getenv("DB_HOST") // :
dbUser := os.Getenv("DB_USER")
dbName := os.Getenv("DB_NAME")
dbHost := strings.Split(dbHostPort, ":")[0]
dbPort := strings.Split(dbHostPort, ":")[1]

token, err := authToken(dbHost, dbUser, region, roleARN)
if err == nil {
// http://gorm.io/docs/connecting_to_the_database.html
dbURI := fmt.Sprintf("host=%s port=%s user=%s dbname=%s password=%s", dbHost, dbPort, dbUser, dbName, token)
_, err := gorm.Open("postgres", dbURI)
if err != nil {
log.Panic("Could not get connection")
}
}
}

@KarthickEmis @bjmc ^

Hi , PFB for the working piece of code for the IAM DB Authentication
package main

import (
    "database/sql"
    "fmt"

    "github.com/aws/aws-lambda-go/events"
    "github.com/aws/aws-lambda-go/lambda"
    "github.com/aws/aws-sdk-go-v2/aws/external"
    "github.com/aws/aws-sdk-go-v2/service/rds/rdsutils"
    _ "github.com/lib/pq"
)

const awsRegion = "eu-west-2"

func init() {

    fmt.Println(" test code base")
    cfg, err := external.LoadDefaultAWSConfig()
    if err != nil {
        fmt.Println(err)
    }
    config := gettoken()
    authToken, err := rdsutils.BuildAuthToken(fmt.Sprintf("%s:%d", config["dbhost"], 5432),
        awsRegion, "test", cfg.Credentials)
    dnsStrr := fmt.Sprintf("host=%s port=%d user=%s dbname=%s sslmode=require password=%s",
        config["dbhost"], 5432, "test", config["dbname"], authToken)

    fmt.Println("authtoken: " + authToken)
    fmt.Println("--------------------------------------------------------------: ")
    fmt.Println("dnsStrr: " + dnsStrr)

    db, err = sql.Open("postgres", dnsStrr)

    if err != nil {
        panic(err)
    }
    err = db.Ping()
    if err != nil {
        panic(err)
    }
    fmt.Println("Successfully connected with IAM Authentication Token dnsStrr!")

    defer db.Close()
}

func gettoken() map[string]string {
    conf := make(map[string]string)
    conf["dbhost"] = "ur db host name"
    conf["dbport"] = "5432"
    conf["dbuser"] = "ur user name"
        conf["dbname"] = "db user name"
        return conf
}

[bjmc]

I have not verified it, but for benefit of others on this thread, somebody has written a tutorial with sample code.