Changing user password using postgres go client

Question

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?

mkopriva · Accepted Answer · 2024-04-16 06:06:28Z

1

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.

answered Apr 16, 2024 at 6:06

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Changing user password using postgres go client

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related