Incubator-superset: SECRET_KEY encrypts database secrets without warning, possibly breaking web interface if changed

[andor-pierdelacabeza]

Make sure these boxes are checked before submitting your issue - thank you!

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

Superset version

0.20.4

Expected results

Usually, on web apps, app secrets are just used for generating cookies (see secrets.secret_key_base in Rails, SECRET_KEY in Django, or Wordpress security keys and salts), so you can happily use different ones for dev and production environments, or change them if you see fit or any of your configuration files has leaked. The only problem you'll have is logged users will lose their session, but they can login again.

As Superset configuration doesn't specify any other use for this secret, the expected result for changing this value would be losing connected sessions.

Actual results

• When accessing to any menu that connects so databases (e.g. a dashboard or a slice), we get a unicode exception, as in issues #2600 or #2966 .
• Then, if you figure out that's not an Unicode error, but an encryption one, and you want to overwrite/change stored passwords on the Database Sources configuration, you'll get a similar Unicode error, so, not being able to edit MySQL connections.
• Unless you manually edit the database, removing password blobs from connections, you won't be able to do it.

Steps to reproduce

• Add a database source and or dashboard
• Change your SECRET_KEY in your config file
• Restart Superset
• Open a slice or dashboard
• EXCEPTION

Recommended fix behaviour

• Docs should WARN about backing up secret key (I can do that if docs are in source)
• Exception should be captured with a more obvious error
• Database Sources configuration menu should be accessible, also capturing the error and showing a message that passwords are not accessible, and allow overwriting that password with a new one that will be encoded with the new secret_key

[xrmx]

What exception do you get? I think i've already fixed the issue upstream. For the rest feel free to open a PR against doc.

[andor-pierdelacabeza]

I take a working superset 0.20.4 install, change the secret, then enter a Dashboard, and I get this:

        Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy_utils/types/encrypted.py", line 98, in decrypt
    decrypted = decrypted.decode('utf-8')
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa5 in position 0: invalid start byte

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.5/dist-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.5/dist-packages/superset/utils.py", line 601, in wraps
    return f(self, *args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/superset/views/core.py", line 1733, in dashboard
    if datasource and not self.datasource_access(datasource):
  File "/usr/local/lib/python3.5/dist-packages/superset/views/base.py", line 99, in datasource_access
    self.schema_access(datasource, user=user) or
  File "/usr/local/lib/python3.5/dist-packages/superset/views/base.py", line 92, in schema_access
    self.database_access(datasource.database, user=user) or
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/attributes.py", line 237, in __get__
    return self.impl.get(instance_state(instance), dict_)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/attributes.py", line 584, in get
    value = self.callable_(state, passive)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/strategies.py", line 557, in _load_for_state
    return self._emit_lazyload(session, state, ident_key, passive)
  File "<string>", line 1, in <lambda>
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/strategies.py", line 603, in _emit_lazyload
    return loading.load_on_ident(q, ident_key)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 223, in load_on_ident
    return q.one()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2814, in one
    ret = self.one_or_none()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2784, in one_or_none
    ret = list(self)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 90, in instances
    util.raise_from_cause(err)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 75, in instances
    rows = [proc(row) for row in fetch]
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 75, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 437, in _instance
    loaded_instance, populate_existing, populators)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 498, in _populate_full
    dict_[key] = getter(row)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/type_api.py", line 1122, in process
    return process_value(impl_processor(value), dialect)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy_utils/types/encrypted.py", line 272, in process_result_value
    decrypted_value = self.engine.decrypt(value)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy_utils/types/encrypted.py", line 100, in decrypt
    raise ValueError('Invalid decryption key')
ValueError: Invalid decryption key

Then, if I enter the Sources/Databases menu to overwrite/rewrite the passwords, I get this:

        Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy_utils/types/encrypted.py", line 98, in decrypt
    decrypted = decrypted.decode('utf-8')
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xa5 in position 0: invalid start byte

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1982, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1614, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1517, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.5/dist-packages/flask/_compat.py", line 33, in reraise
    raise value
  File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1612, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.5/dist-packages/flask/app.py", line 1598, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/usr/local/lib/python3.5/dist-packages/flask_appbuilder/security/decorators.py", line 26, in wraps
    return f(self, *args, **kwargs)
  File "/usr/local/lib/python3.5/dist-packages/flask_appbuilder/views.py", line 475, in list
    widgets = self._list()
  File "/usr/local/lib/python3.5/dist-packages/flask_appbuilder/baseviews.py", line 877, in _list
    page_size=page_size)
  File "/usr/local/lib/python3.5/dist-packages/flask_appbuilder/baseviews.py", line 791, in _get_list_widget
    count, lst = self.datamodel.query(joined_filters, order_column, order_direction, page=page, page_size=page_size)
  File "/usr/local/lib/python3.5/dist-packages/flask_appbuilder/models/sqla/interface.py", line 118, in query
    return count, query.all()
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/query.py", line 2703, in all
    return list(self)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 90, in instances
    util.raise_from_cause(err)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/util/compat.py", line 187, in reraise
    raise value
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 75, in instances
    rows = [proc(row) for row in fetch]
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 75, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 437, in _instance
    loaded_instance, populate_existing, populators)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/orm/loading.py", line 498, in _populate_full
    dict_[key] = getter(row)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy/sql/type_api.py", line 1122, in process
    return process_value(impl_processor(value), dialect)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy_utils/types/encrypted.py", line 272, in process_result_value
    decrypted_value = self.engine.decrypt(value)
  File "/usr/local/lib/python3.5/dist-packages/sqlalchemy_utils/types/encrypted.py", line 100, in decrypt
    raise ValueError('Invalid decryption key')
ValueError: Invalid decryption key

[xrmx]

The exception chain looks good, maybe we just have a catch all on the frontend that returns a generic error?

[ottomata]

This is happening to me as well, although not after changing the SECRET_KEY. I've upgraded to python 3.4, and now trying to access Sources -> Databases gives me the same error. Perhaps python2 did not store whatever it needs to as utf-8?

[mistercrunch]

Not much we can do about the encryption lib and/or related logic not working across py2/3 . Seems like you'll have to re-enter the passwords post migration.

[leorochael]

@andor-pierdelacabeza, Can you please add to your description how to do the workaround you mentioned of "removing password blobs from connections"?

This way other people affected that bump into this issue report (like me) can easily get out of the menu breakage as well.

[mistercrunch]

It should be easy to add support to lookup environment variables for connection string. Let me do a quick PR.

[mistercrunch]

FYI the easiest way is to use DB_CONNECTION_MUTATOR that you can define in your superset_config.py. More info here:
https://github.com/apache/incubator-superset/blob/master/superset/config.py#L386

[czue]

this is also an issue if you switch between debug mode and non-debug mode. debug mode seems to not load your superset_config.py which means that if you start your server in one mode and then switch to the other you also get this error.

is there a supported way to change your secret key if this happens?

[Hitman23]

@czue did you find a solution to this? Getting the same error after changing secret_key.

[mistercrunch]

@czue debug mode should not have anything to do with loading or not loading superset_config

[czue]

@mistercrunch when I add the -d flag it does not pick up my local config, but when I remove it it does. see the output below (note the "loaded your LOCAL config" lines are missing in the debug output). Have also verified that the values are not present in the config, so it's not just a logging thing:

(superset) $ superset runserver -d
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Starting Superset server in DEBUG mode
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

2018-06-28 08:30:48,210:INFO:werkzeug: * Running on http://0.0.0.0:8088/ (Press CTRL+C to quit)
2018-06-28 08:30:48,211:INFO:werkzeug: * Restarting with stat
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Starting Superset server in DEBUG mode
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

2018-06-28 08:30:49,964:WARNING:werkzeug: * Debugger is active!
2018-06-28 08:30:49,964:INFO:werkzeug: * Debugger PIN: 172-601-678

(superset) $ superset runserver 
2018-06-28 08:30:59,269:INFO:root:The Gunicorn 'superset runserver' command is deprecated. Please use the 'gunicorn' command instead.
Starting server with command: 
gunicorn -w 2 --timeout 60 -b  0.0.0.0:8088 --limit-request-line 0 --limit-request-field_size 0 superset:app

[2018-06-28 08:30:59 +0200] [14133] [INFO] Starting gunicorn 19.8.1
[2018-06-28 08:30:59 +0200] [14133] [INFO] Listening at: http://0.0.0.0:8088 (14133)
[2018-06-28 08:30:59 +0200] [14133] [INFO] Using worker: sync
[2018-06-28 08:30:59 +0200] [14136] [INFO] Booting worker with pid: 14136
[2018-06-28 08:30:59 +0200] [14138] [INFO] Booting worker with pid: 14138
Loaded your LOCAL configuration at [/home/czue/superset/superset_config.py]
Loaded your LOCAL configuration at [/home/czue/superset/superset_config.py]

[mistercrunch]

The line that goes Loaded your LOCAL configuration should be right after the $ superset runserver command. Your PYTHONPATH is not set properly. Somehow /home/czue/superset/ appears to only be in your PYTHONPATH in the context of the gunicorn subprocess.

[czue]

what's strange is that the file is in the same directory as I'm running the command in. should the current directory not always be on the PYTHONPATH?

[Hitman23]

So mine worked out. After changing the SECRET_KEY, I went into superset and re-entered the database passwords sources>databases .

database_engine://database_user:[database_password]@ip_address/database

[anshbansal]

This is happening with me also.

[anshbansal]

@czue debug mode should not have anything to do with loading or not loading superset_config

Agree that logically it should not have anything to do with loading the config file. But in reality, it is affecting the config loading.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

[reesezxf]

So mine worked out. After changing the SECRET_KEY, I went into superset and re-entered the database passwords sources>databases .

database_engine://database_user:[database_password]@ip_address/database

but when i change the secret_key, i can not go to sources>databases , i also got this error, Can you tell me in more detail

[reesezxf]

So mine worked out. After changing the SECRET_KEY, I went into superset and re-entered the database passwords sources>databases .
database_engine://database_user:[database_password]@ip_address/database

but when i change the secret_key, i can not go to sources>databases , i also got this error, Can you tell me in more detail

i got an solution, add a config DB_SECRET_KEY in config.py it's value is origin SECRET_KEY, then use config["DB_SECRET_KEY"] replace config["SECRET_KEY"] in superset/models/core.py Database model

[mistercrunch]

@reesezxf we'd accept a PR that does this

[magic-overflow]

So mine worked out. After changing the SECRET_KEY, I went into superset and re-entered the database passwords sources>databases .
database_engine://database_user:[database_password]@ip_address/database

but when i change the secret_key, i can not go to sources>databases , i also got this error, Can you tell me in more detail

i got an solution, add a config DB_SECRET_KEY in config.py it's value is origin SECRET_KEY, then use config["DB_SECRET_KEY"] replace config["SECRET_KEY"] in superset/models/core.py Database model

I got this error after upgrade. I followed ur instruction, it doesn't work.

File "/home/ubuntu/venv/lib/python3.8/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 128, in decrypt
    raise ValueError('Invalid decryption key')
ValueError: Invalid decryption key