When creating a user with psycopg, I would create the following composite in order to avoid SQL injection:
from psycopg.sql import SQL, Identifier, Literal
username = "test_user"
password = "test_password"
query = SQL(
"CREATE USER {username} WITH ENCRYPTED PASSWORD {password};"
).format(username=Identifier(username), password=Literal(password))
This can be tested in the scope of a psycopg.Connection:
print(query.as_string(connection)) # CREATE USER "test_user" WITH ENCRYPTED PASSWORD 'test_password';
What is the recommended way to do the same with sqlalchemy? I tried quoted_name and bind_params, but the former was not successful:
from sqlalchemy import quoted_name, text
query = text(
f"CREATE USER {quoted_name(username, True)} WITH ENCRYPTED PASSWORD :password;"
).bindparams(password=password).compile(compile_kwargs={"literal_binds": True})
print(query) # CREATE USER test_user WITH ENCRYPTED PASSWORD 'test_password';
(Changing the second parameter of quoted_name to False or None yields the same result.)