SSL on flask-migrate

[Luttik]

Hi I have a (possibly dumb) issue with SSL and Flask-Migrate.

I instanciate the flask app with SQLALCHEMY_DATABASE_URI and SQLALCHEMY_ENGINE_OPTIONS using app.config.from_object (drawing inspiration from the Falsk Mega-Tutorial).

The arguments seems to work, I tested with:

config = DevelopmentConfig()
engine = create_engine(
    config.SQLALCHEMY_DATABASE_URI,
    **config.SQLALCHEMY_ENGINE_OPTIONS
)
connection = engine.connect()

My engine_options are as follows:

self.SQLALCHEMY_ENGINE_OPTIONS = dict(
    connect_args=dict(
        sslmode='require',
        sslrootcert=self.db.ssl_ca,
        sslcert=self.db.ssl_cert,
        sslkey=self.db.ssl_key,
    )

where self.db.ssl_ca, self.db.ssl_cert and self.db.ssl_key are absolute paths or the necessary ssl files.

It seems to me like flask-migrate ignores these since SSL is off when I according to the error message.

sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL:  connection requires a valid client certificate
FATAL:  pg_hba.conf rejects connection for host "<hidden>", user "<also hidden>", database "<my hidden database>", SSL off

I have attached the traceback below:
Traceback.txt

I hope you can help me since I'm fully stuck at this point.

[miguelgrinberg]

Yes, at this point Flask-Migrate only takes the database URL from the Flask-SQLAlchemy configuration, not the engine options. If you move your SSL options to the query string of your database URL you should be fine, I think. But in any case, I should look into adding the engine options if possible.

[Luttik]

@miguelgrinberg Thanks for the quick response.
For now I replaced the connectable like this, I'm not sure if that'll generate other issues though.

connectable = create_engine(
    current_app.config['SQLALCHEMY_DATABASE_URI'],
    **current_app.config['SQLALCHEMY_ENGINE_OPTIONS']
)

[miguelgrinberg]

@Luttik yeah, that is probably okay. I was actually thinking that this might work:

connectable = current_app.extensions['migrate'].db.engine

This basically steals the engine object from Flask-SQLAlchemy, so you are not creating a second engine just for Alembic.

[Luttik]

That seems like a great way to simplify the env.py file.
Would you even need config.set_main_option and target_metadata then?
I'm definitely not an expert on this plugin. But these kinds of simplifications do make my engineering brain happy.

[Korhm]

Edit: Ok, I think I did understand. I modified the env.py file in the migration folder. It works ;)

Hello,

is this forecasted to make Flask-Migrate use the engine options to connect to the DB with ssl?
I'm facing the same issue as Luttik, but I don't manage to path ssl paramters in the Database URI. pymysql seems to not support
it.

There is maybe this solution:

connectable = current_app.extensions['migrate'].db.engine

This basically steals the engine object from Flask-SQLAlchemy, so you are not creating a second engine just for Alembic.

But I don't know where/how to use it ?

Thank you