Change SECRET_KEY and get error `ValueError: Invalid decryption key` on K8s #8538

newtonjose · 2019-11-10T14:47:49Z

When I try change SECRET_KEY = '\2\1thisismyscretkey\1\2\e\y\y\h' (default), to
(for example):

SECRET_KEY = '\2\1888D237694D70843CDBC38195215E5623EDAA74C3D47E8C4F1156\1\2\e\y\y\h'

I getting the error ValueError: Invalid decryption key in sqlalchemy_utils when on execute superset-init.

To reproduce:

$ cd ./install/helm/superset 
$ mkdir config/ && touch config/superset_config.py
$ nano config/superset_config.py
---
import os

# Your App secret key
SECRET_KEY = '\2\1<new long random string>\1\2\e\y\y\h'

# The SQLAlchemy connection string to your database backend
# This connection defines the path to the database that stores your
# superset metadata (slices, connections, tables, dashboards, ...).
# Note that the connection information to connect to the datasources
# you want to explore are managed directly in the web UI
# SQLALCHEMY_DATABASE_URI = 'sqlite:////var/lib/superset/superset.db'
SQLALCHEMY_DATABASE_URI = 'postgresql+psycopg2://XXXXX:XXXX@host/db'

# Flask-WTF flag for CSRF
WTF_CSRF_ENABLED = True
# Add endpoints that need to be exempt from CSRF protection
WTF_CSRF_EXEMPT_LIST = []

# Set this API key to enable Mapbox visualizations
MAPBOX_API_KEY = os.environ.get("MAPBOX_API_KEY", "")
---
$ helm upgrade --install superset .
$ kubectl exec -it superset-<pod_id> superset-init

Expected results

Actual results

Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
/usr/local/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
2019-11-10 14:44:31,592:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
Loaded your LOCAL configuration at [/etc/superset/superset_config.py]
/usr/local/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
2019-11-10 14:44:38,625:INFO:root:Configured event logger of type <class 'superset.utils.log.DBEventLogger'>
Traceback (most recent call last):
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 124, in decrypt
    decrypted = decrypted.decode('utf-8')
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xca in position 1: invalid continuation byte

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/bin/superset", line 31, in <module>
    cli()
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 722, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/flask/cli.py", line 586, in main
    return super(FlaskGroup, self).main(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 697, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 1066, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 895, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/flask/cli.py", line 426, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/click/core.py", line 535, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python3.6/site-packages/superset/cli.py", line 51, in init
    utils.get_or_create_main_db()
  File "/usr/local/lib/python3.6/site-packages/superset/utils/core.py", line 948, in get_or_create_main_db
    get_main_database()
  File "/usr/local/lib/python3.6/site-packages/superset/utils/core.py", line 971, in get_main_database
    return get_or_create_db("main", conf.get("SQLALCHEMY_DATABASE_URI"))
  File "/usr/local/lib/python3.6/site-packages/superset/utils/core.py", line 956, in get_or_create_db
    db.session.query(models.Database).filter_by(database_name=database_name).first()
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3228, in first
    ret = list(self[0:1])
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 3018, in __getitem__
    return list(res)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 105, in instances
    util.raise_from_cause(err)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 153, in reraise
    raise value
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 85, in instances
    rows = [proc(row) for row in fetch]
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 85, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 572, in _instance
    populators,
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/loading.py", line 693, in _populate_full
    dict_[key] = getter(row)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/type_api.py", line 1247, in process
    return process_value(impl_processor(value), dialect)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 409, in process_result_value
    decrypted_value = self.engine.decrypt(value)
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 126, in decrypt
    raise ValueError('Invalid decryption key')
ValueError: Invalid decryption key

Screenshots

If applicable, add screenshots to help explain your problem.

How to reproduce the bug

Go to '...'
Click on '....'
Scroll down to '....'
See error

Environment

(please complete the following information):

superset version: superset version
0.34.1
python version: python --version
3.6.1
node.js version: node -v
npm version: npm -v

Checklist

Make sure these boxes are checked 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

Add any other context about the problem here.

The text was updated successfully, but these errors were encountered:

issue-label-bot · 2019-11-10T14:47:52Z

Issue-Label Bot is automatically applying the label #bug to this issue, with a confidence of 0.61. Please mark this comment with 👍 or 👎 to give our bot feedback!

Links: app homepage, dashboard and code for this bot.

dpgaspar · 2019-11-10T16:39:42Z

Hi @newtonjose,

This is probably because you already have database connections that were encrypted with the old key. You have to recreate them or update

reesezxf · 2020-08-18T09:24:05Z

This is probably because you already have database connections that were encrypted with the old key. You have to recreate them or update

how to recreate or update ,this page get error

reesezxf · 2020-08-18T12:02:31Z

This is probably because you already have database connections that were encrypted with the old key. You have to recreate them or update

how to recreate or update ,this page get error

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

ustramooner · 2021-07-09T00:59:58Z

I used a different approach:
I could only find 1 place that the DB_SECRET_KEY was being used - which was in the dbs table under password. After rotating the SECRET_KEY, i set that field to null in the database. After that the database screen will work again (it crashes because it can't decrypt the passwords) - and then reset the passwords.

jhult · 2021-09-17T20:25:48Z

The databases page would not load for me after changing the SECRET_KEY. Here is how I fixed it:

psql -d superset -U superset -W
# Password for user superset:
select database_name, password from dbs;
update dbs set password = null;

Now the databases page will load but clicking edit on certain databases still didn't work and caused this stack trace:

Unable to load dialect <class 'sqlalchemy.dialects.mssql.adodbapi.MSDialect_adodbapi'>: type object 'MSDialect_adodbapi' has no attribute 'dbapi'
2021-09-17 18:29:43,812:WARNING:superset.db_engine_specs:Unable to load dialect <class 'sqlalchemy.dialects.mssql.adodbapi.MSDialect_adodbapi'>: type object 'MSDialect_adodbapi' has no attribute 'dbapi'
2021-09-17 18:29:43,819:ERROR:root:Invalid decryption key
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 126, in decrypt
    decrypted = decrypted.decode('utf-8')
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xae in position 2: invalid start byte

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/api/__init__.py", line 85, in wraps
    return f(self, *args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/api/__init__.py", line 155, in wraps
    return f(self, *args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/api/__init__.py", line 1448, in get
    return self.get_headless(pk, **kwargs)
  File "/app/superset/utils/log.py", line 242, in wrapper
    value = f(*args, **kwargs)
  File "/app/superset/views/base_api.py", line 390, in get_headless
    duration, response = time_function(super().get_headless, pk, **kwargs)
  File "/app/superset/utils/core.py", line 1461, in time_function
    response = func(*args, **kwargs)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/api/__init__.py", line 1344, in get_headless
    item = self.datamodel.get(pk, self._base_filters, self.show_select_columns)
  File "/usr/local/lib/python3.7/site-packages/flask_appbuilder/models/sqla/interface.py", line 935, in get
    query, _filters, select_columns=select_columns
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3459, in one_or_none
    ret = list(self)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 100, in instances
    cursor.close()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
    with_traceback=exc_tb,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 80, in instances
    rows = [proc(row) for row in fetch]
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 80, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 588, in _instance
    populators,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/loading.py", line 725, in _populate_full
    dict_[key] = getter(row)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/type_api.py", line 1278, in process
    return process_value(impl_processor(value), dialect)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 469, in process_result_value
    value = super().process_result_value(value=value, dialect=dialect)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 414, in process_result_value
    decrypted_value = self.engine.decrypt(value)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 128, in decrypt
    raise ValueError('Invalid decryption key')
ValueError: Invalid decryption key

Create a new database (e.g. name = "New DB") from the UI. You can get the SQLAlchemy URI using this SQL:

select sqlalchemy_uri from dbs where database_name = 'Original DB';

Once the new database is created, back in psql:

select database_name, id from dbs where database_name = 'New DB' or database_name = 'Original DB';
# 5 = New DB
# 2 = Original DB
update tables set database_id = 5 where database_id = 2;
update tab_state set database_id = 5 where database_id = 2;
update query set database_id = 5 where database_id = 2;
update saved_query set db_id = 5 where db_id = 2;
update table_schema set database_id = 5 where database_id = 2;
delete from dbs where database_name = 'Original DB';
update dbs set database_name = 'Original DB' where database_name = 'New DB';

andtlopez · 2022-01-11T06:48:51Z

I've unfortunately encountered this and here's what I've done to fix this:

Change SECRET_KEY on superset_config.py
Access psql client and connect to the superset DB (\connect db_name;)
update dbs set password = null; (thanks @jhult), exit out
superset db upgrade
superset init
superset run
Update password on Superset: Data > Databases (I'm using 1.2.0 btw)

novemberde · 2022-02-20T13:30:20Z

I've encountered the same issue when upgraded superset to 1.4.1 from 1.4.0.
I resolved the issue @andtlopez comment with one more step.

Change SECRET_KEY on superset_config.py
Access psql client and connect to the superset DB (\connect db_name;)
update dbs set password = null, encrypted_extra where id=?; <- encrypted_extra should be set to null
superset db upgrade
superset init
superset run
Update password on Superset: Data > Databases (I'm using 1.4.1 btw)

nikhil-kuyya-talentas · 2022-03-09T06:59:39Z

just sharing my observation that having same secret_key and connecting the from the different host fails with invalid decryption key. would like to know views from community.( i think this by design)

searain · 2022-03-19T16:32:43Z

I had the same issue when I use docker-compose to install superset on MacOS.

Due to it is my local test, it is easy for me just to remove old containers and old data and start fresh.

docker-compose down -v (or docker-compose down and docker volume prune), this will remove the old containers and old data
docker-compose up, to start fresh

The key point is you need to remove old volumes.

jeverling · 2022-03-29T05:25:52Z

just sharing my observation that having same secret_key and connecting the from the different host fails with invalid decryption key. would like to know views from community.( i think this by design)

Same for me, upgrading (in my case from 1.3.2 to 1.4.2rc1) breaks the encryption, even if the SECRET_KEY stays exactly the same (Superset even warns about using the default SECRET_KEY).

jeverling · 2022-03-29T06:07:14Z

I've encountered the same issue when upgraded superset to 1.4.1 from 1.4.0. I resolved the issue @andtlopez comment with one more step.

Change SECRET_KEY on superset_config.py

Access psql client and connect to the superset DB (\connect db_name;)

update dbs set password = null, encrypted_extra where id=?; <- encrypted_extra should be set to null

superset db upgrade

superset init

superset run

Update password on Superset: Data > Databases (I'm using 1.4.1 btw)

In step 3 I'm getting a syntax error.
Would update dbs set password = null; update dbs set encrypted_extra = null; work?

Shkurupii · 2022-05-09T12:42:41Z

@andtlopez thanks for the hint.

Same issue. But what I also wanted was to change SECRET_KEY from default one to new one. I couldn't find anything about how to decrypt paswords with the SECRET_KEY. I found useful to decrypt passwords from working superset before upgrade. To do that:

Run a shell in the app context

superset shell

Paste a snippet

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

from superset import config
from superset.models.core import Database

engine = create_engine(config.SQLALCHEMY_DATABASE_URI)
connection = engine.connect()
Session = sessionmaker(bind=connection)
session = Session()

db_ids = session.query(Database.id).all()  # -> [(1,), (2,), (3,), (6,), (4,), (5,)]
db_ids = [db_id[0] for db_id in db_ids]  # Flatten

for db_id in db_ids:
    db = session.query(Database).get(db_id)
    if db.password is not None:
        print(db.id, db.database_name, db.password)

session.close_all()
connection.close()
engine.dispose()

Hope it'll help!

shydrate · 2022-05-27T07:55:52Z

I have upgraded superset from 1.3.0 to 1.4.2, When i run the command superset init, i get the same error which is ValueError: Invalid Decryption key. I have set it up on ubuntu machine(from scratch). Even though i am able to login into superset portal and view charts that were created in the previous version, the init command throws the error.
Let me know how to resolve it.
NOTE: For db's we are using managed database, and sqlite for superset which is default one.

sfirke · 2022-07-15T18:12:02Z

In step 3 I'm getting a syntax error. Would update dbs set password = null; update dbs set encrypted_extra = null; work?

I just ran update dbs set password = null; without any reference to encrypted_extra and it worked. When I restarted Superset, I could access the list of Databases, and just had to update the password in the SQLALCHEMY_DATABASE_URI for the connection to work again.

andy-clapson · 2022-09-28T17:18:10Z

I ran into this too when updating the SECRET_KEY, the most reliable/surgical fix seemed to be:

run update dbs set password = null, encrypted_extra = null; in the superset database (sets the secrets to blank, DB will not connect)
Then edit the BI database connection in the UI, and update the password. Those two fields will be updated and your datasets and charts should all be working normally, as before.

💡 UPDATE: in a classic RTFD moment, looks like this is far simpler if you just rotate secrets the right way: https://superset.apache.org/docs/installation/configuring-superset/#secret_key-rotation

yanickrochon · 2022-10-31T15:38:20Z

@andtlopez thanks for the hint.

...
for db_id in db_ids:
    db = session.query(Database).get(db_id)
    if db.password is not None:
        print(db.id, db.database_name, db.password)
 ...

And I get

Traceback (most recent call last):
  File "/path/to/lib/python3.8/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 128, in decrypt
    decrypted = decrypted.decode('utf-8')
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x96 in position 1: invalid start byte

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<console>", line 2, in <module>
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 1018, in get
    return self._get_impl(ident, loading.load_on_pk_identity)
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 1135, in _get_impl
    return db_load_fn(self, primary_key_identity)
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 286, in load_on_pk_identity
    return q.one()
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3490, in one
    ret = self.one_or_none()
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/orm/query.py", line 3459, in one_or_none
    ret = list(self)
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 100, in instances
    cursor.close()
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
    compat.raise_(
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 182, in raise_
    raise exception
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 80, in instances
    rows = [proc(row) for row in fetch]
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 80, in <listcomp>
    rows = [proc(row) for row in fetch]
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 579, in _instance
    _populate_full(
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/orm/loading.py", line 725, in _populate_full
    dict_[key] = getter(row)
  File "/path/to/lib/python3.8/site-packages/sqlalchemy/sql/type_api.py", line 1278, in process
    return process_value(impl_processor(value), dialect)
  File "/path/to/lib/python3.8/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 477, in process_result_value
    value = super().process_result_value(value=value, dialect=dialect)
  File "/path/to/lib/python3.8/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 422, in process_result_value
    decrypted_value = self.engine.decrypt(value)
  File "/path/to/lib/python3.8/site-packages/sqlalchemy_utils/types/encrypted/encrypted_type.py", line 130, in decrypt
    raise ValueError('Invalid decryption key')
ValueError: Invalid decryption key

Nope, this does not help.

yanickrochon · 2022-10-31T17:29:15Z

What I do not understand is this : I can open the SQLite database with sqlite3, I can query tables, I can see data, etc. When I go in the admin interface (Superset), I can vew dashbaords, queries, etc. but the Databases list is empty!

even if there is actual data in the dbs table :

sqlite> select id, database_name FROM dbs;
1|examples
2|mysql_db_1
3|mysql_db_2

When I create a new database, using Superset, the database is created in the SQLite database, but it is still not listed.

Why is it complaining about an "invalid decryption key" when the database is fully readable! How to fix this?

hussein-hijazi856 · 2022-12-22T09:10:59Z

This workaround #8538 (comment), assumes that the meta-data db is using postgres; in my case it was sqlite. So the steps in my case where:

Change SECRET_KEY on superset_config.py
Access sqlite3 client and connect to the sqlite db file ( sqlite3 superset.db )
update dbs set password = null, encrypted_extra = null where id=?; <- encrypted_extra should be set to null
superset db upgrade
superset init
superset run
Update password on Superset: Data > Databases (I'm using 1.4.1 btw)

I only changed step 2.

dpgaspar · 2022-12-22T11:25:27Z

Do note that superset has a cli for this:

https://superset.apache.org/docs/installation/configuring-superset/#secret_key-rotation

wuchuguang · 2023-01-29T02:50:56Z

This workaround #8538 (comment), assumes that the meta-data db is using postgres; in my case it was sqlite. So the steps in my case where:

Change SECRET_KEY on superset_config.py

Access sqlite3 client and connect to the sqlite db file ( sqlite3 superset.db )

update dbs set password = null, encrypted_extra = null where id=?; <- encrypted_extra should be set to null

superset db upgrade

superset init

superset run

Update password on Superset: Data > Databases (I'm using 1.4.1 btw)

I only changed step 2.

i use
1/ sqlite3 superset.db
2/ delete from dbs;

delete dbs data

is ok;

andy-clapson · 2023-01-29T03:18:17Z

As noted here and here above, there is a cli for this.

meymeynard · 2023-03-27T09:59:46Z

I had the same issue when I use docker-compose to install superset on MacOS.

Due to it is my local test, it is easy for me just to remove old containers and old data and start fresh.

docker-compose down -v (or docker-compose down and docker volume prune), this will remove the old containers and old data

docker-compose up, to start fresh

The key point is you need to remove old volumes.

this worked for me

andhikam · 2023-07-10T10:00:42Z

This is probably because you already have database connections that were encrypted with the old key. You have to recreate them or update

how to recreate or update ,this page get error

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

hi sir, can you tell me where is config["SECRET_KEY"] in superset/models/core.py Database model, really i dont find it, thankyou

ajeety4 · 2023-07-14T15:04:28Z

This worked for me
https://superset.apache.org/docs/installation/configuring-superset/#rotating-to-a-newer-secret_key

vitaly-dt · 2023-09-13T11:44:14Z

select database_name, id from dbs where database_name = 'New DB' or database_name = 'Original DB';
'# 5 = New DB'
'# 2 = Original DB'
update tables set database_id = 5 where database_id = 2;
update tab_state set database_id = 5 where database_id = 2;
update query set database_id = 5 where database_id = 2;
update saved_query set db_id = 5 where db_id = 2;
update table_schema set database_id = 5 where database_id = 2;
delete from dbs where database_name = 'Original DB';
update dbs set database_name = 'Original DB' where database_name = 'New DB';

This solved it for me!
also in the new superset versions, need to add:

update sl_datasets set database_id = 5 where database_id = 2;
update sl_tables set database_id = 5 where database_id = 2;

gavinsweet · 2023-09-22T20:24:13Z

Just to help anyone else coming across this.

Trying to migrate / upgrade superset using backup of postgres metadata, shifting to new version.
I went from superset 2.0.x to 2.10.
Then seeing problems with invalid decryption key, so you see the datasets in superset ui but the database connections barf.

First, fix secret keys in superset_config.py

PREVIOUS_SECRET_KEY = 'CHANGE_ME_TO_A_COMPLEX_RANDOM_SECRET'
SECRET_KEY = 'some-really-random-key'

<= determined original key from other (original) installation by getting session on superset container:
$ docker exec -it superset_worker bash
then:

superset shell

then:

from flask import current_app; print(current_app.config["SECRET_KEY"])

<= generate from:
bash$ openssl rand -base64 42

Second, ensure to specify a version in your docker-compose config:
$ vi docker-compose-non-dev.yml
x-superset-image: &superset-image apachesuperset.docker.scarf.sh/apache/superset:2.1.0

Third, as per comments above, update the password and encrypted_extra values for your db connections in the restored metadata db:
psql: update dbs set password = null, encrypted_extra = null

Fourth, you should now be able to login to superset ui, go to database connections, and just re-enter the password(s) for your database(s) - then the dataset(s) for those databases come back to life.

xiluoxi · 2023-12-04T10:35:05Z

modify in config.py :
SECRET_KEY = "your now value"
PREVIOUS_SECRET_KEY = "your pre value"

then exec: superset re-encrypt-secrets

lelelethu · 2023-12-21T02:08:06Z

I've unfortunately encountered this and here's what I've done to fix this:

Change SECRET_KEY on superset_config.py

Access psql client and connect to the superset DB (\connect db_name;)

update dbs set password = null; (thanks @jhult), exit out

superset db upgrade

superset init

superset run

Update password on Superset: Data > Databases (I'm using 1.2.0 btw)

step 7, what uri for that?
postgresql://examples:<what_value>@db:5432/examples

db · 2023-12-22T08:52:01Z

<what_value> would be the password I believe.

yjcsivsuk · 2024-01-05T02:53:22Z

我不明白的是：我可以打开SQLite数据库，我可以查询表，我可以查看数据等等。当我进入管理界面（Superset）时，我可以查看破折号、查询等，但数据库列表是空的！sqlite3

即使表中有实际数据：dbs
sqlite> select id, database_name FROM dbs;
1|examples
2|mysql_db_1
3|mysql_db_2
当我使用Superset创建一个新数据库时，该数据库是在SQLite数据库中创建的，但仍未列出。

为什么当数据库完全可读时，它抱怨“解密密钥无效”！如何解决这个问题？

I also met this problem, may I ask how you finally solved it?

yjcsivsuk · 2024-01-05T02:55:50Z

此解决方法 #8538 （comment）假定元数据数据库正在使用 postgres;就我而言，它是SQLITE。因此，在我的情况下，步骤是：

更改SECRET_KEY superset_config.py

访问 sqlite3 客户端并连接到 sqlite db 文件（sqlite3 superset.db )

update dbs set password = null， encrypted_extra = null where id=？;<- encrypted_extra应设置为 null

Superset DB 升级

超集初始化

超集运行

在 Superset 上更新密码：数据>数据库（顺便说一句，我使用的是 1.4.1）

我只更改了第 2 步。

How does step 2 here access the sqlite3 client and connect to the sqlite.db file?

Teesmo · 2024-02-05T15:21:29Z

I unfortunately cannot remember my previous secret key (My config file got deleted, somehow), please help!!!

yjcsivsuk · 2024-02-06T04:42:26Z

I unfortunately cannot remember my previous secret key (My config file got deleted, somehow), please help!!!

You can try to reset your secret key by usingopenssl rand -base64 42 andexport SUPERSET_SECRET_KEY='(just generated secret key)'.

badf007 · 2024-04-16T23:00:11Z

@yjcsivsuk Can you please elaborate a little more on the steps? where should I export the key? Is the only step needed? restart?

I'm in the same case as @Teesmo, I don't have access to my previous secret key.

rusackas · 2024-04-18T15:39:20Z

I'm not sure we have docs on "what to do if you lose your secret key" - is there a standard procedure? CC @dpgaspar @sfirke

shanebusenitz · 2024-09-20T20:32:09Z

Ok so I spent all day on this and finally got it so I'm going to add some more detail to help others out.

We had a previous deployment running on K8s deployed from Helm. I upgraded to 3.0.0 and tried setting the SUPERSET_SECRET_KEY in a variety of ways using the openssl rand -base64 42 with no luck.

A couple things I found to be true for me was that setting:

  extraSecretEnv:
    SUPERSET_SECRET_KEY: SomelongBase64String

did not in fact set the SECRET_KEY in the superset_config.py like the docs say it will.
Another thing that seems to be true is if you have a previous deployment you have to set a PREVIOUS_SECRET_KEY which you can only get from the previous version.

After all that, these are the steps I followed to get it to work:

From 3.0.0 I downgraded to 2.1.1 (along with helm to 0.7.7) and deployed (we use ArgoCD so it syncs with any update)
Shell logged into the running web superset running and ran cat pythonpath/superset_config.py. At the bottom there should be a SECRET_KEY='thisISaSECRET_56789' or something basic like that. Grab that string.
Upgraded to 3.0.0 and set values.yaml like this:

  extraSecretEnv:
    SUPERSET_SECRET_KEY: <some string you generate by using `openssl rand -base64 42`>
    # old default secret goes here
    PREVIOUS_SECRET_KEY: thisISaSECRET_56789

   # you could just hardcode them here but this is was what I did when I figured out `extraSecretEnv` were not being used.
  configOverrides:
    secret: |
      # Secret key for signing cookies
      SECRET_KEY = os.getenv('SUPERSET_SECRET_KEY')
      PREVIOUS_SECRET_KEY = os.getenv('PREVIOUS_SECRET_KEY')

deploy these changes.
4. shell into the web superset again and run superset re-encrypt-secrets

Hopefully this helps someone not spend the time I did figuring this out.

issue-label-bot bot added the !deprecated-label:bug Deprecated label - Use #bug instead label Nov 10, 2019

newtonjose closed this as completed Nov 11, 2019

This was referenced Sep 17, 2021

UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe5 in position 4 #2600

Closed

SECRET_KEY encrypts database secrets without warning, possibly breaking web interface if changed #3724

Closed

jhult mentioned this issue Oct 12, 2021

Superset 0.39 => 1.3.0 upgrade fails with ValueError: Invalid decryption key #17059

Closed

3 tasks

HumairAK mentioned this issue Sep 6, 2022

Superset has issues using SECRET_KEY to decrypt encrypted data in postgres operate-first/support#714

Closed

anamitraadhikari mentioned this issue Jan 20, 2022

Invalid decryption key issue #18077

Closed

This was referenced Feb 3, 2022

Cannot edit DBs ("An error occurred while fetching databases") #18563

Closed

After DB migration and "Invalid decryption key" fix, correct schemas not displaying #18602

Closed

cnstlungu mentioned this issue Apr 17, 2022

missing dashbord cnstlungu/portable-data-stack-airflow#1

Closed

eschutho mentioned this issue Jul 7, 2023

sqllab - Invalid decryption key #24620

Closed

MarkupMaster mentioned this issue Sep 12, 2023

After PC restart Superset won't fetch databases: Invalid decryption key #25261

Closed

lllong33 mentioned this issue Dec 28, 2023

Getting error when running test connection - ERROR: Invalid decryption key #24254

Closed

Jarxinho mentioned this issue Aug 1, 2024

ValueError: Invalid decryption key - multiple superset instance and single database #29829

Open

3 tasks

Change SECRET_KEY and get error ValueError: Invalid decryption key on K8s #8538

Change SECRET_KEY and get error ValueError: Invalid decryption key on K8s #8538

Comments

newtonjose commented Nov 10, 2019 • edited Loading

Expected results

Actual results

Screenshots

How to reproduce the bug

Environment

Checklist

Additional context

issue-label-bot bot commented Nov 10, 2019

dpgaspar commented Nov 10, 2019

reesezxf commented Aug 18, 2020

reesezxf commented Aug 18, 2020

ustramooner commented Jul 9, 2021

jhult commented Sep 17, 2021

andtlopez commented Jan 11, 2022 • edited Loading

novemberde commented Feb 20, 2022 • edited Loading

nikhil-kuyya-talentas commented Mar 9, 2022

searain commented Mar 19, 2022

jeverling commented Mar 29, 2022

jeverling commented Mar 29, 2022

Shkurupii commented May 9, 2022

shydrate commented May 27, 2022

sfirke commented Jul 15, 2022

andy-clapson commented Sep 28, 2022 • edited Loading

yanickrochon commented Oct 31, 2022

yanickrochon commented Oct 31, 2022

hussein-hijazi856 commented Dec 22, 2022 • edited Loading

dpgaspar commented Dec 22, 2022

wuchuguang commented Jan 29, 2023

andy-clapson commented Jan 29, 2023

meymeynard commented Mar 27, 2023

andhikam commented Jul 10, 2023

ajeety4 commented Jul 14, 2023

vitaly-dt commented Sep 13, 2023

gavinsweet commented Sep 22, 2023

superset shell

xiluoxi commented Dec 4, 2023

lelelethu commented Dec 21, 2023

db commented Dec 22, 2023

yjcsivsuk commented Jan 5, 2024

yjcsivsuk commented Jan 5, 2024

Teesmo commented Feb 5, 2024

yjcsivsuk commented Feb 6, 2024

badf007 commented Apr 16, 2024

rusackas commented Apr 18, 2024

shanebusenitz commented Sep 20, 2024

Change SECRET_KEY and get error `ValueError: Invalid decryption key` on K8s #8538

Change SECRET_KEY and get error `ValueError: Invalid decryption key` on K8s #8538

newtonjose commented Nov 10, 2019 •

edited

Loading

andtlopez commented Jan 11, 2022 •

edited

Loading

novemberde commented Feb 20, 2022 •

edited

Loading

andy-clapson commented Sep 28, 2022 •

edited

Loading

hussein-hijazi856 commented Dec 22, 2022 •

edited

Loading