superset db upgrade mishandles DATABASE_URL with % in it

[tv42]

Superset passes DATABASE_URL to Alembic by setting it as a value in a ConfigParser instance.

ConfigParser values are interpolated: https://docs.python.org/3/library/configparser.html#interpolation-of-values

This results in error message (or wrong behavior) if the DATABASE_URL contains %.

DATABASE_URL containing % is needed for some Postgres features, for example setting the schema where Superset metadata is stored: postgres://postgres:s3kr1t@localhost:5432/postgres?application_name=superset&options=--search_path%3D%22%24user%22,superset

How to reproduce the bug

1. Set in superset config:

SQLALCHEMY_DATABASE_URI = "postgres://postgres:s3kr1t@localhost:5432/postgres?application_name=superset&options=--search_path%3D%22%24user%22,superset"

2. Run superset db upgrade

Expected results

Superset medata tables created inside Postgres schema named superset.

Actual results

  File "<frozen importlib._bootstrap_external>", line 843, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/app/superset/extensions/../migrations/env.py", line 45, in <module>
    config.set_main_option("sqlalchemy.url", DATABASE_URI)
  File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 231, in set_main_option
    self.set_section_option(self.config_ini_section, name, value)
  File "/usr/local/lib/python3.8/site-packages/alembic/config.py", line 258, in set_section_option
    self.file_config.set(section, name, value)
  File "/usr/local/lib/python3.8/configparser.py", line 1201, in set
    super().set(section, option, value)
  File "/usr/local/lib/python3.8/configparser.py", line 894, in set
    value = self._interpolation.before_set(self, section, option,
  File "/usr/local/lib/python3.8/configparser.py", line 402, in before_set
    raise ValueError("invalid interpolation syntax in %r at "
ValueError: invalid interpolation syntax in 'postgres://postgres:s3kr1t@localhost:5432/postgres?application_name=superset&options=--search_path%3D%22%24user%22,superset' at position 97

Environment

• superset version: 0.0.0-dev (docker image 3c95779d0029 from 2023-02-21)
• python version: Python 3.8.16

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

How to fix it:

diff --git i/superset/migrations/env.py w/superset/migrations/env.py
index d0220e33f..274a4e24a 100755
--- i/superset/migrations/env.py
+++ w/superset/migrations/env.py
@@ -42,7 +42,11 @@ if "sqlite" in DATABASE_URI:
         "SQLite Database support for metadata databases will \
         be removed in a future version of Superset."
     )
-config.set_main_option("sqlalchemy.url", DATABASE_URI)
+config.set_main_option(
+    "sqlalchemy.url",
+    # Avoid ConfigParser interpolation.
+    DATABASE_URI.replace('%', '%%'),
+)
 target_metadata = Base.metadata  # pylint: disable=no-member

[ross-at-finix]

FYI, this happens when you have to quote_plus URL escape passwords with special characters as well.

[rusackas]

Looks like the PR resolving this was merged, but the issue was never closed. Closing now. Thanks!

[villebro]

Reopened this, as the fix #23176 caused a regression. Opened #30532 which should resolve both this issue, and the one that the author of #23176 was running into.