Creating Postgres Users and Granting Permissions from Go

Question

I'm trying to create database users in my Postgres database from Golang, and I can't seem to get queries to run.

I'm able to establish a connection, but I cannot run create user X with password Y and I've tried multiple ways of doing this, and searching the internet has not yielded any results. I'm building a database migrator wrapper utility and I need to manage these users manually as their credentials are being sourced from secrets. I'm using pq as my driver.

I've tried the following:

// fails with syntax error re $
res, err := db.ExecContext(ctx, "create user $1 with password $2", username, password)
// fails with syntax error re ?
res, err := db.ExecContext(ctx, "create user ? with password ?", username, password)

The only examples I've found on the internet use fmt.Sprintf but obviously that's a bad idea due to SQL injection attacks.

Is there a specific function I need to be using when operating on Postgres users/roles/other items? I need to issue grant statements after this so I'm not sure if it will follow the same pattern.

I was unaware that pg is headed toward deprecation, so as recommended in the comments below, I have switched to github.com/jackc/pgx/v5. However, I'm still having issues and I can't see in the documentation how to accomplish what I'm looking to do.

I'm now operating with a pgx.Conn directly instead of through the sql facade so I can take full advantage of all of the features provided by pgx.

I'm running a local Docker container for postgres:15.10 and I'm trying to create a user janedoe with a password of password.

Attempt 1: Use pgx.Identifier to Sanitize Both Username and Password

queryTemplate := fmt.Sprintf(
    "create user %s with password %s",
    pgx.Identifier{username}.Sanitize(),
    pgx.Identifier{password}.Sanitize(),
)

result, err := pgx.Exec(ctx, queryTemplate)

This returns ERROR: syntax error at or near ""password"" (SQLSTATE 42601). I've tried including a final semicolon at the end of the statement and that doesn't fix things.

It seems that Postgres wants this string to not be double-quoted but single-quoted, and I don't see a way in pgx to specify the kind of escaping that I want.

Better to move to pgx since pq is in maintenance mode and will be abandoned. Use pkg.go.dev/github.com/jackc/pgx/v5#Identifier.Sanitize to fix the issue. — Frank Heikens
– Frank Heikens, Commented Sep 15 at 19:03
1) The issue is in create user $1 with password $2 the $1 and $2 are literals not parameters that can be passed in with username, password. I don't see anything in pq that allows you to safely build dynamic queries, so I would take @FrankHeikens advice and move off pq. 2) An option is to create your own Postgres function that accepts arguments and then does the CREATE USER safely using format. — Adrian Klaver
– Adrian Klaver, Commented Sep 15 at 19:49
As @FrankHeikens recommended, I have begun trying to use pgx instead of pg, so I'll update my question, but I'm still having trouble building a query. — Naftuli Kay
– Naftuli Kay, Commented Sep 15 at 20:27
I was wrong in my previous comment the role(user) name is an identifier. FYI, CREATE USER is an alias for CREATE ROLE. The password is a string not an identifier which is why you are getting the error. The pgx documentation is sparse and I don't use Golang so I don't know whether pgx has something like pgx.Literal .Sanitize() to deal with strings. Or maybe try passing the password as a parameter. — Adrian Klaver
– Adrian Klaver, Commented Sep 15 at 21:38

Naftuli Kay · Accepted Answer · 2025-09-16 16:57:30Z

I have come to a solution which isn't ideal because it isn't something natively covered by either pq or pgx, but it does get the job done.

I think underlying these issues is the fact that there are different Postgres data types that are composed together to form queries, and only some of these data types can be parameterized.

In my particular case, which is create user "x" with password 'abc', the two places I want variance to occur (the username and the password) are not locations in the query which can be parameterized, and further complicating this, the user name is of the type identifier and the password is of the type literal.

For the username, we can use the built-in pgx.Identifier type's Sanitize() function to safely escape it:

safeUsername := pgx.Identifier{username}.Sanitize()

However, the password seems to be an edge-case that neither of the Postgres drivers cover. Another answer here on StackOverflow covers how to escape literals in SQL queries, so I had to implement this myself. There are several different ways of doing this, but I opted for the $DELIMITER$actual_password$DELIMITER$ approach, where we can make DELIMITER any value we want.

I'm generating a 32-digit alphanumeric random string and using this as my delimiter, and I'm also checking that the password does not contain this value to prevent SQL injections attempting to break out of the escaping somehow:

delimiter := RandomString(32)

for strings.Contains(password, delimiter) {
    // ensure escape is not possible
    delimiter = RandomString(32)
}

safePassword := fmt.Sprintf("$%s$%s$%s$", delimiter, password, delimiter)

Finally, now that we have escaped our username as an identifier and our password as a string literal, we can run the query:

result, err := db.Exec(
    fmt.Sprintf("create user %s with password %s", safeUsername, safePassword
)

Obviously it would be far more ideal if escaping of literals were something that was provided by drivers themselves, but this does work. I think the main lesson learned for me was that variable substitution in queries depends on the underlying Postgres data type for that section of the query. I assume that this is why create table ? (...) won't work either.

I'm going to do further work to not send passwords over the network at all, rather to use SCRAM-SHA-256 instead, and this should prevent needing to worry about escaping from a string literal.

Collectives™ on Stack Overflow

Creating Postgres Users and Granting Permissions from Go

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