Running up/down-grades as a role

[keriksson-rosenqvist]

I have a database in which I would like a NOLOGIN role to be the owner of everything and then allow other users to be members of that role for limited times.

Example

1. owner_1 is a member of the owner_role and used to log in and create the new database my_db as owner_role:

SET ROLE owner_role;
CREATE DATABASE my_db;

2. Somehow alembic is configured to log in as owner_1 but execute commands as owner_role. Doing this either with a command line flag or by a setting in alembic.ini would be ideal.

alembic upgrade head --role=owner_role

3. Down the line owner_1 is used to create a new user in the owner role

CREATE USER owner_2 WITH CREATEDB CREATEROLE ENCRYPTED PASSWORD 'owner_password_2' IN ROLE owner_role;

4. owner_2 is used to drop owner_1 and then replaces owner_1 in step 2 and repeating.

DROP USER owner_1;

How can I get alembic to log in as owner_x but execute commands as the shared role owner_role?

[zzzeek]

hey there -

alembic and SQLAlchemy have no interaction with roles. the URL that you provide is how the driver connects, and from there, it just executes commands. so this would be a question of configuring your database to allow a login that comes in as what you need. on this end I dont even know what database this is, so if you happen to share that information, I can point you to the driver documentation where they might have advice on this.

if the solution ends up that you need to execute a command after connecting, you can configure that in your env.py by running that command on the connection after you've created it, something like:

    with connectable.connect() as connection:
        connection.exec_driver_sql("emit SQL to set up local role / login / etc here")
        context.configure(
            connection=connection, target_metadata=target_metadata
        )

        with context.begin_transaction():
            context.run_migrations()

[keriksson-rosenqvist]

Thank you. The second part here works perfectly. How would you run it on "offline mode"?
For example in a unit test suit with a temporary docker database?

    url = config.get_main_option("sqlalchemy.url")
    context.configure(
        url=url,
        target_metadata=target_metadata,
        literal_binds=True,
        dialect_opts={"paramstyle": "named"},
    )

    with context.begin_transaction():
        context.run_migrations()

[zzzeek]

"offline mode" usually refers to --sql where there is no database in play. I think you are instead referring towards running this on a database that doesn't require special role information. if you in fact do need to normally run an additional statement in your env.py that establishes some detail about roles for the current connection, you can encode the details about that statement in your alembic.ini and then retrieve those details using the config object in env.py.

[keriksson-rosenqvist]

Turns out I had set some environment variables wrong. Thanks for the help on this!