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 ?

alukach · Accepted Answer · 2020-09-22 02:05:27Z

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

klin · Accepted Answer · 2023-01-07 00:26:51Z

2

Use psycopg2.extensions.AsIs(object):

from psycopg2.extensions import AsIs

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

edited Jan 7, 2023 at 0:26

answered Oct 27, 2018 at 21:15

klin

123k15 gold badges240 silver badges262 bronze badges

4 Comments

Amin Etesamian Over a year ago

with encrypted password is a better thing

webwurst Over a year ago

encrypted is default for recent versions.

Migwell Over a year ago

Although if you're not sure that the username is actually a valid identifier, I suppose you'd prefer quote_ident.

klin Over a year ago

@Migwell - I don't see any reason a developer should not know if a string is a valid identifier. If he wants to use a quoted identifier nothing prevents him from using double-quotes, e.g. AsIs('"NewUser"')

Collectives™ on Stack Overflow

Postgres psycopg2 create user

2 Answers 2

Comments

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Linked

Related