[BUGS-0.2.0] Renew Authentication tokens

[fnikolai]

When connecting to external systems, we assume that connections are persistent and last forever.

This is not always the case. For example, the snowflake authentication token expires after 1h and the connection returns an exception.

We have to capture this exception and re-establish the connection.

The snowflake log:

{"error":"ProgrammingError","messages":"10.244.12.146:37902 - \"GET /db/show\"                    500 Internal Server Error <ProgrammingError:                    (snowflake.connector.errors.ProgrammingError) 390114 (08001): Authentication token has expired.  The user must authenticate again.\n(Background on this error at: https://sqlalche.me/e/14/f405)>","traceback":"Traceback (most recent call last):\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/network.py\", line 755, in _post_request\n    ret = self._renew_session()\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/network.py\", line 529, in _renew_session\n    return self._token_request(REQUEST_TYPE_RENEW)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/network.py\", line 584, in _token_request\n    raise ReauthenticationRequest(\nsnowflake.connector.network.ReauthenticationRequest: 390114 (08001): Authentication token has expired.  The user must authenticate again.\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py\", line 1094, in _commit_impl\n    self.engine.dialect.do_commit(self.connection)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py\", line 686, in do_commit\n    dbapi_connection.commit()\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/connection.py\", line 824, in commit\n    self.cursor().execute(\"COMMIT\")\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/cursor.py\", line 977, in execute\n    ret = self._execute_helper(query, **kwargs)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/cursor.py\", line 694, in _execute_helper\n    ret = self._connection.cmd_query(\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/connection.py\", line 1309, in cmd_query\n    ret = self.rest.request(\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/network.py\", line 493, in request\n    return self._post_request(\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/network.py\", line 758, in _post_request\n    raise ex.cause\nsnowflake.connector.errors.ProgrammingError: 390114 (08001): Authentication token has expired.  The user must authenticate again.\n\nThe above exception was the direct cause of the following exception:\n\nTraceback (most recent call last):\n  File \"/home/superduper/superduperdb/superduperdb/server/app.py\", line 35, in dispatch\n    return await call_next(request)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/middleware/base.py\", line 165, in call_next\n    raise app_exc\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/middleware/base.py\", line 151, in coro\n    await self.app(scope, receive_or_disconnect, send_no_error)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/middleware/exceptions.py\", line 65, in __call__\n    await wrap_app_handling_exceptions(self.app, conn)(scope, receive, send)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/_exception_handler.py\", line 64, in wrapped_app\n    raise exc\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/_exception_handler.py\", line 53, in wrapped_app\n    await app(scope, receive, sender)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/routing.py\", line 756, in __call__\n    await self.middleware_stack(scope, receive, send)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/routing.py\", line 776, in app\n    await route.handle(scope, receive, send)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/routing.py\", line 297, in handle\n    await self.app(scope, receive, send)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/routing.py\", line 77, in app\n    await wrap_app_handling_exceptions(app, request)(scope, receive, send)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/_exception_handler.py\", line 64, in wrapped_app\n    raise exc\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/_exception_handler.py\", line 53, in wrapped_app\n    await app(scope, receive, sender)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/routing.py\", line 72, in app\n    response = await func(request)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/fastapi/routing.py\", line 278, in app\n    raw_response = await run_endpoint_function(\n  File \"/home/superduper/.local/lib/python3.10/site-packages/fastapi/routing.py\", line 193, in run_endpoint_function\n    return await run_in_threadpool(dependant.call, **values)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/starlette/concurrency.py\", line 42, in run_in_threadpool\n    return await anyio.to_thread.run_sync(func, *args)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/anyio/to_thread.py\", line 56, in run_sync\n    return await get_async_backend().run_sync_in_worker_thread(\n  File \"/home/superduper/.local/lib/python3.10/site-packages/anyio/_backends/_asyncio.py\", line 2144, in run_sync_in_worker_thread\n    return await future\n  File \"/home/superduper/.local/lib/python3.10/site-packages/anyio/_backends/_asyncio.py\", line 851, in run\n    result = context.run(func, *args)\n  File \"/home/superduper/superduperdb/superduperdb/rest/app.py\", line 82, in db_show\n    return app.db.show(\n  File \"/home/superduper/superduperdb/superduperdb/base/datalayer.py\", line 272, in show\n    out = self.metadata.show_components()\n  File \"/home/superduper/superduperdb/superduperdb/backends/sqlalchemy/metadata.py\", line 397, in show_components\n    with self.session_context() as session:\n  File \"/usr/lib/python3.10/contextlib.py\", line 142, in __exit__\n    next(self.gen)\n  File \"/home/superduper/superduperdb/superduperdb/backends/sqlalchemy/metadata.py\", line 146, in session_context\n    session.commit()\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/orm/session.py\", line 1454, in commit\n    self._transaction.commit(_to_root=self.future)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/orm/session.py\", line 839, in commit\n    trans.commit()\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py\", line 2469, in commit\n    self._do_commit()\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py\", line 2659, in _do_commit\n    self._connection_commit_impl()\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py\", line 2630, in _connection_commit_impl\n    self.connection._commit_impl()\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py\", line 1096, in _commit_impl\n    self._handle_dbapi_exception(e, None, None, None, None)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py\", line 2134, in _handle_dbapi_exception\n    util.raise_(\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/util/compat.py\", line 211, in raise_\n    raise exception\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/engine/base.py\", line 1094, in _commit_impl\n    self.engine.dialect.do_commit(self.connection)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/sqlalchemy/engine/default.py\", line 686, in do_commit\n    dbapi_connection.commit()\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/connection.py\", line 824, in commit\n    self.cursor().execute(\"COMMIT\")\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/cursor.py\", line 977, in execute\n    ret = self._execute_helper(query, **kwargs)\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/cursor.py\", line 694, in _execute_helper\n    ret = self._connection.cmd_query(\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/connection.py\", line 1309, in cmd_query\n    ret = self.rest.request(\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/network.py\", line 493, in request\n    return self._post_request(\n  File \"/home/superduper/.local/lib/python3.10/site-packages/snowflake/connector/network.py\", line 758, in _post_request\n    raise ex.cause\nsqlalchemy.exc.ProgrammingError: (snowflake.connector.errors.ProgrammingError) 390114 (08001): Authentication token has expired.  The user must authenticate again.\n(Background on this error at: https://sqlalche.me/e/14/f405)\n"}

show.json

[blythed]

If we reconnect will it work or do we need to fetch the token from somewhere?

[fnikolai]

I 'm guessing that reconnect should work (since it works on the first connection, I don't see why it shouldn't work on reconnect)

[fnikolai]

https://community.snowflake.com/s/article/Authentication-token-has-expired-The-user-must-authenticate-again

Specifically for snowflake, there is the possibility to allow the session to stay active indefinitely by setting CLIENT_SESSION_KEEP_ALIVE=TRUE.

[blythed]

Idea: retain a self.connect() callback in meta-data store and data-backend to reconnect without caching credentials.

[fnikolai]

The same problem exists in the apiserver in golang and on the frontend in javascript