search

aws / aws-sdk-go

AWS SDK for the Go programming language.
http://aws.amazon.com/sdk-for-go/
Apache License 2.0
8.64k stars 2.07k forks source link

iam db authentication for postgresql #2951

Closed KarthickEmis closed 2 years ago

KarthickEmis commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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:

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:

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 commented 4 years ago

@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 commented 4 years ago

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

Thanks

sunnylbk commented 4 years ago

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 commented 4 years ago

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 commented 4 years ago

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

califlower commented 3 years ago

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

author of that guide here

Unfortunately, AWS has completely changed their function signatures as of one of their newer library updates without documenting the change whatsoever (which they shouldn’t be doing according to the Golang specs, it should be a new version).

Since this is a breaking undocumented change, and based on some of the comments towards questions asking for better password lifecycle management I’d recommend using a different solution for authentication. Its clear that IAM authentication is a bit of an afterthought or at least geared towards lambda use. If I do get the time to look for a solution I’ll update my guide but since we transitioned away from this in my application it might take a moment

califlower commented 3 years ago

Update on this issue here https://github.com/califlower/golang-aws-rds-iam-postgres/issues/5

github-actions[bot] commented 2 years ago

We have noticed this issue has not received attention in 1 year. We will close this issue for now. If you think this is in error, please feel free to comment and reopen the issue.

bjmc commented 2 years ago

AWS folks, what's the verdict? It doesn't look like this issue has been resolved. Could we please have a complete working example in the docs?