[SIP] Ability to connect with a second user to run db migrations only

[justmike1]

[SIP] Proposal for Ability to connect with a second user to run db migrations only

Motivation

We have internal policy which the flyway or application migrations will run on certain user which has ability to create/delete tables, and another user for runtime which can only write or read from existing tables

Proposed Change

currently I use:

SQLALCHEMY_DATABASE_URI = f"postgresql+psycopg2://{env('DB_USER')}:{env('DB_PASS')}@{env('DB_HOST')}:{env('DB_PORT')}/{env('DB_NAME')}"

superset db upgrade

I would like:

superset db upgrade --user flywayuser --password admin

New or Changed Public Interfaces

NONE

New dependencies

NONE

Migration Plan and Compatibility

Add default fallback when the parameter isn't included to use the already connected user, thus existing users doesn't need to do anything

Rejected Alternatives

NONE

[Habeeb556]

@justmike1

I think we can work around this since there is no --user option with superset db upgrade. You can split the export SUPERSET_CONFIG_PATH=/app/superset_config.py and create one for /etc/systemd/system/superset.service setting its Environment=SUPERSET_CONFIG_PATH=/app/superset_config.py.

When you need to run superset db update create another superset_config_upgrade.py with the username replaced in the file and export SUPERSET_CONFIG_PATH=/app/superset_config_upgrade.py.

Please ensure that the user has the necessary privileges to access the database.

I want to highlight that it is not best practice to use a different user as the owner of the table during upgrades. This discrepancy can lead to permission conflicts when the system runs.

[justmike1]

@Habeeb556 Thanks!

[rusackas]

Taking off the SIP label here to make bookkeeping easier. We won't number this one since it was solved before it began. Thanks @Habeeb556 :)