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?

Not exactly i probe different strategies nowaday for things like this. One easy way is something like: sql = f"CREATE USER quote_ident('{username}') WITH PASSWORD :passwd' it is sql injection safe an easy readable combining f-strings with parametrized queries. — Francisco Puga
– Francisco Puga, Commented Jul 2, 2021 at 18:41

Alex W · Accepted Answer · 2022-07-05 18:47:49Z

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.

This means that quoted_name is called without knowledge of the specific dialect in use. That's... not optimal.

Collectives™ on Stack Overflow

How to create a postgres user with sqlalchemy

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related