postgresqlgopgx

Changing user password using postgres go client

Hi I'm trying to change user's password in postgres using pgx/v5 driver in Go

_, err = conn.Exec(context.Background(), "ALTER USER $1 WITH PASSWORD $2", username, password)
if err != nil {
    fmt.Printf("Error altering user: %v\n", err)
    return
}

When I execute it, I'm getting below error:

error: syntax error at or near "$1" (SQLSTATE 42601)

I've tried running raw sql query using psql and it works fine but fails trying to do it in go. What could I possibly be doing wrong and How do I fix it?

Solution

  • Parameter references can be used for values only. However the role_name in ALTER USER role_name ... is a name, or, more precisely, an identifier, it is NOT a value. Just like it's not possible to do CREATE TABLE $1 ..., for the same reason it is not possible to do ALTER USER $1.

    Based on where the username is coming from you could simply use fmt.Sprintf and interpolate the name into the string as is, or you could declare a function in postgres that executes dynamic sql.