How to create a postgres user with sqlalchemy

Question

I need to allow the creation of database users/roles through a web application that uses Python and SQLAlchemy in the backend.

To do that i'm using something like:

sql = 'CREATE USER :username WITH PASSWORD :passwd'
sql_data = {
    "username": "the_user_name",
    "passwd": "the_password",
}
request.db.execute(sql, sql_data)

But the SQL created by this query is:

CREATE USER 'the_user_name' WITH PASSWORD 'the_password'

and what I need is:

CREATE USER "the_user_name" WITH PASSWORD 'the_password'

with the username properly escaped. Is there a way that SQLAlchemy can handle this or should I create the string manually? If so, how can I escape the user input name?

Answer 1

Using quoted_name:

Represent a SQL identifier combined with quoting preferences.

quoted_name is a Python unicode/str subclass which represents a particular identifier name along with a quote flag. This quote flag, when set to True or False, overrides automatic quoting behavior for this identifier in order to either unconditionally quote or to not quote the name. If left at its default of None, quoting behavior is applied to the identifier on a per-backend basis based on an examination of the token itself.

Here is how I was able to get it to work:

from sqlalchemy.sql import text, quoted_name

DATABASE_USER = "your_user_here"
DATABASE_USER_PASSWORD = "your_password"

create_user_sql = text(f"CREATE USER {quoted_name(DATABASE_USER, False)} WITH PASSWORD :database_password")\
    .bindparams(                                     # You can remove this line if you don't want to test
        database_password=DATABASE_USER_PASSWORD.    # You can remove this line if you don't want to test
    )\                                               # You can remove this line if you don't want to test
    .compile(compile_kwargs={"literal_binds": True}) # You can remove this line if you don't want to test
print(str(create_user_sql))                          # You can remove this line if you don't want to test

Results in the following being executed:

CREATE USER your_user_here WITH PASSWORD 'your_password'

You can remove the .bindparams() and .compile() and just pass the parameter(s) to connection.execute(create_user_sql, database_password=DATABASE_USER_PASSWORD) which looks cleaner, the code above is just a proof of concept.