Thanks for the question, Derek B. I haven't used SQLAlchemy before but there should be a few ways to leverage this Postgres feature with that library:

Roles that can login

If the role you would like to use was created with login you can specify it in your connection:

engine = create_engine('postgresql://USER_ROLE:db_name@db_host/db_name')

Note that this should only be done if you are creating a new DB connection for each query or for each user. If you reuse connections, you may inadvertently allow a user to access a role that is now theirs.

In a transaction

The method I use most often should work fine regardless of which language/library you are using: each query is run in it’s own transaction. You will need to ensure your server is setting up the connection reliably, and only runs the user’s query in a transaction after it has been sanitized. In SQlAlchemy it would look something like this:

engine = create_engine('postgresql://ROOT_APPLICATION_USER_ROLE@db_host/db_name')
Session = sessionmaker(bind=engine)

# new session
session = Session()
try:
    # start a transaction
    session.begin()
    # set the role and/or session variable for the specific user
    session.execute('set role :user_role', { user_role: USER_ROLE })
    session.execute('set local our_application.user_id=:user_id', {user_id: USER_ID)
    # now run the user's query, to get all Items for example:
    result = session.query(Item).all()
    # or a raw query, which should be sanitized first to avoid SQL injection:
    result = session.execute('raw sql here')
    # execute the transaction
    session.commit()
except:
    session.rollback()
    raise
finally:
    session.close()

I’m not fluent in Python and haven’t used SQLAlchemy so you may need to check my syntax and variable binding.

With an additional library

The Coaster library seems to cover this use case well. This doc may provide the best approach: https://coaster.readthedocs.io/en/latest/sqlalchemy/roles.html

Let us know what works for you to help others with the same question!