Using different postgres user for application db request and migration

[CANEVETGASPARD]

Hello there.

I am currently working on a Flask application with Flask==2.2.5, Flask-SQLAlchemy==3.1.1 and Flask-Migrate==4.0.5. This app use a postgres db with two different users:

• app_user for data access like data insert, update and delete within a table.
• admin_user for db migration. This means table modfication like adding column, deleting table or else..

Currently, I am creating my projet like that :

from flask import Flask
from flask_migrate import Migrate
from flask_sqlalchemy import SQLAlchemy

api = Flask(__name__)
api.config['SQLALCHEMY_DATABASE_URI'] = postgresql://app_user:password@host:port/db
db = SQLAlchemy()
db.init_app(api)
migrate = Migrate(api, db)

With that configuration, the migration process will use the app_user. The process will fail because the user has no right to alter table.
I tried many things to change the user while running the migration command like changing the database_uri but at the end it was always using the preconfigured database_uri.
I was wondering if using a different user for migration with Flask_Migration is something that we can do.

Thanks for your answer.

[miguelgrinberg]

First of all, it is a very bad idea to enter credentials directly in the code. You should import your database URL from an environment variable:

api.config['SQLALCHEMY_DATABASE_URI'] =  os.environ['DATABASE_URL']

And now that you have an environment variable, you can set it to different values depending on your needs. When using Flask-Migrate you can set DATABASE_URL to your admin credentials, and when using the web app you can set your regular user's URL.

[mikebionic]

You can also create a selarate function that you trigger to re-set the app.config with needed user's credentials that you have pulled from .env and after successfull operation set your app.config back

[CANEVETGASPARD]

Thanks @miguelgrinberg for the response.