Postgres psycopg2 create user

Question

To create a user through psycopg2, I am using the following code :

        cur=conn.cursor()
    cur.execute("create user %s with password %s",('abcdefgh','0h/9warrAttrgd8EF0gkvQ==',))

This gives the following error :

syntax error at or near "'abcdefgh'" LINE 1: create user 'abcdefgh' with password '0h/9warrAttrgd8EF0gkvQ.

It seems that %s is placing quotes around the username, which postgres doesn't like while creating a user. The following code works fine :

        cur.execute("create user abcdefgh with password %s",('0h/9warrAttrgd8EF0gkvQ==',))

Any workaround for this ?

Answer 1

None of the existing answers actually use a safe method of doing this. However, since version 2.7 of Psycopg, there is a much better method.

What the docs say about using %s for identifiers (table names, user names etc):

Only query values should be bound via this method: it shouldn’t be used to merge table or field names to the query (Psycopg will try quoting the table name as a string value, generating invalid SQL). If you need to generate dynamically SQL queries (for instance choosing dynamically a table name) you can use the facilities provided by the psycopg2.sql module

Using this module, the above query is better formulated as:

from psycopg2 import sql

query = sql.SQL("CREATE USER {username} WITH PASSWORD {password}").format(
    username=sql.Identifier('abcdefgh'),
    password=sql.Placeholder()
)
cur.execute(query, ('0h/9warrAttrgd8EF0gkvQ==',))

Using https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries

Answer 2

Use psycopg2.extensions.AsIs(object):

from psycopg2.extensions import AsIs

cur.execute("create user %s with password %s", (AsIs('abcdefgh'), '0h/9warrAttrgd8EF0gkvQ==',))