PgAdmin Upgrade From 6.14 to 6.15 (or anything newer than that) Failure

[constantin-baciu]

Please note that security bugs or issues should be reported to security@pgadmin.org.

Describe the bug

Currently running a docker PgAdmin 6.14 instance with a SQLite database. Trying to upgrade to 6.15 or anything beyond that results in errors and PgAdmin does not start.

2023-03-09 17:46:05,301: ERROR  pgadmin:        Traceback (most recent call last):
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: duplicate column name: passexec_cmd

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/pgadmin4/pgadmin/__init__.py", line 390, in upgrade_db
    db_upgrade(app)
  File "/pgadmin4/pgadmin/setup/db_upgrade.py", line 25, in db_upgrade
    flask_migrate.upgrade(migration_folder)
  File "/venv/lib/python3.10/site-packages/flask_migrate/__init__.py", line 111, in wrapped
    f(*args, **kwargs)
  File "/venv/lib/python3.10/site-packages/flask_migrate/__init__.py", line 200, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/venv/lib/python3.10/site-packages/alembic/command.py", line 378, in upgrade
    script.run_env()
  File "/venv/lib/python3.10/site-packages/alembic/script/base.py", line 576, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/pgadmin4/pgadmin/setup/../../migrations/env.py", line 95, in <module>
    run_migrations_online()
  File "/pgadmin4/pgadmin/setup/../../migrations/env.py", line 87, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/venv/lib/python3.10/site-packages/alembic/runtime/environment.py", line 867, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/venv/lib/python3.10/site-packages/alembic/runtime/migration.py", line 624, in run_migrations
    step.migration_fn(**kw)
  File "/pgadmin4/migrations/versions/f79844e926ae_.py", line 30, in upgrade
    op.add_column('server', sa.Column('passexec_cmd', sa.String(length=256)))
  File "<string>", line 8, in add_column
  File "<string>", line 3, in add_column
  File "/venv/lib/python3.10/site-packages/alembic/operations/ops.py", line 2044, in add_column
    return operations.invoke(op)
  File "/venv/lib/python3.10/site-packages/alembic/operations/base.py", line 401, in invoke
    return fn(self, operation)
  File "/venv/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 154, in add_column
    operations.impl.add_column(table_name, column, schema=schema, **kw)
  File "/venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 322, in add_column
    self._exec(base.AddColumn(table_name, column, schema=schema))
  File "/venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 193, in _exec
    return conn.execute(  # type: ignore[call-overload]
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1380, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/venv/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1472, in _execute_ddl
    ret = self._execute_context(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column name: passexec_cmd
[SQL: ALTER TABLE server ADD COLUMN passexec_cmd VARCHAR(256)]

On top of that, it seems that PgAdmin is asking for some configuration changes in the background, only visible if you browse the logs:

NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

To Reproduce

Steps to reproduce the behavior:

1. Have a docker instance of PgAdmin 6.14 running with SQLite as database.
2. Try to upgrade the instance to anything above 6.15
3. Monitor the logs.

Expected behavior

The specified version of PgAdmin should be running

Error message

Please see above.

Screenshots

N/A

Desktop (please complete the following information): OS is Linux (AmazonLinux).

Additional context

N/A

[adityatoshniwal]

@constantin-baciu Looks like your SQLite pgAdmin config DB is corrupted. You can try to connect to the pgAdmin config DB and remove the columns - passexec_cmd, passexec_expiration from server table. If nothing works, rename the old config DB file and restart pgAdmin.

[constantin-baciu]

@adityatoshniwal If the database is corrupted, how come it works on v6.14? The problem is that we have some configurations in there (this is a multi-user, SSO enabled environment). I'd hate for something to happen and lose all of those.

[adityatoshniwal]

@constantin-baciu Your Config DB has columns which are not supposed to be there for your pgAdmin version. We're not sure how they were added. Now to make the upgrade go through, those column has to be removed to allow pgAdmin to add them back.

[constantin-baciu]

I can't find those tables:

[root@*** docker]# sqlite3 pgadmin4.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .tables
alembic_version              roles_users
database                     server
debugger_function_arguments  servergroup
keys                         setting
macros                       sharedserver
module_preference            user
preference_category          user_macros
preferences                  user_mfa
process                      user_preferences
query_history                version
role
sqlite>

[adityatoshniwal]

@constantin-baciu passexec_cmd, passexec_expiration are columns in server table.

[constantin-baciu]

Thanks @adityatoshniwal . That seems to make it work. But, we're running into other issues now. All our logins are SSO (Azure AD OAuth2). After the upgrade to 6.20, nobody can login. And, this is what I'm seeing in the logs:

2023-03-20 12:51:30,925: ERROR  pgadmin:        Missing "jwks_uri" in metadata
Traceback (most recent call last):
  File "/venv/lib/python3.10/site-packages/flask/app.py", line 1517, in full_dispatch_request
    rv = self.dispatch_request()
  File "/venv/lib/python3.10/site-packages/flask/app.py", line 1503, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 55, in oauth_authorize
    status, msg = auth_obj.login()
  File "/pgadmin4/pgadmin/authenticate/__init__.py", line 274, in login
    status, msg = self.source.login(self.form)
  File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 123, in login
    profile = self.get_user_profile()
  File "/pgadmin4/pgadmin/authenticate/oauth2.py", line 165, in get_user_profile
    self.oauth2_current_client].authorize_access_token()
  File "/venv/lib/python3.10/site-packages/authlib/integrations/flask_client/apps.py", line 108, in authorize_access_token
    userinfo = self.parse_id_token(token, nonce=state_data['nonce'], claims_options=claims_options)
  File "/venv/lib/python3.10/site-packages/authlib/integrations/base_client/sync_openid.py", line 66, in parse_id_token
    claims = _jwt.decode(
  File "/venv/lib/python3.10/site-packages/authlib/jose/rfc7519/jwt.py", line 96, in decode
    data = self._jws.deserialize_compact(s, load_key, decode_payload)
  File "/venv/lib/python3.10/site-packages/authlib/jose/rfc7515/jws.py", line 101, in deserialize_compact
    algorithm, key = self._prepare_algorithm_key(jws_header, payload, key)
  File "/venv/lib/python3.10/site-packages/authlib/jose/rfc7515/jws.py", line 254, in _prepare_algorithm_key
    key = key(header, payload)
  File "/venv/lib/python3.10/site-packages/authlib/integrations/base_client/sync_openid.py", line 38, in load_key
    jwk_set = JsonWebKey.import_key_set(self.fetch_jwk_set())
  File "/venv/lib/python3.10/site-packages/authlib/integrations/base_client/sync_openid.py", line 14, in fetch_jwk_set
    raise RuntimeError('Missing "jwks_uri" in metadata')

[adityatoshniwal]

@constantin-baciu This looks like - https://github.com/pgadmin-org/pgadmin4/issues/5666.