Redashでログインしてしまったユーザを削除

概要

  • Redashのユーザを作って、そのユーザでログインしてしまった後にそのユーザを物理削除する方法の紹介です。
  • 管理画面からEnable/Disableはできますが、削除はできません。SSOでユーザを作ってしまったらメアドの変更ができないので退避もできません。

前提

  • Redashのバージョン8.0.0.b32245
  • AWS AMIで構築しているので、docker-compose管理下

削除方法

削除したいユーザを見つけます。

root@ip-172-31-28-245:/opt/redash# docker exec -it redash_server_1 ./manage.py users list
<省略>
--------------------
Id: 5
Name: Yuki Matsukura
Email: xxxxxxxxxx@xxxxxxxx.com
Organization: Minedia, Inc.
Active: False
Groups: default

消そうとしても、外部キー制約にひっかかって消せません。

root@ip-172-31-28-245:/opt/redash# docker exec -it redash_server_1 ./manage.py users delete xxxxxxxxxx@xxxxxxxx.com
Traceback (most recent call last):
  File "./manage.py", line 9, in <module>
    manager()
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 716, in __call__
    return self.main(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/flask/cli.py", line 380, in main
    return AppGroup.main(self, *args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 696, in main
    rv = self.invoke(ctx)
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 1060, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 1060, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 889, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 534, in invoke
    return callback(*args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/click/decorators.py", line 17, in new_func
    return f(get_current_context(), *args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/flask/cli.py", line 257, in decorator
    return __ctx.invoke(f, *args, **kwargs)
  File "/usr/local/lib/python2.7/site-packages/click/core.py", line 534, in invoke
    return callback(*args, **kwargs)
  File "/app/redash/cli/users.py", line 165, in delete
    synchronize_session=False)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3353, in delete
    delete_op.exec_()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1329, in exec_
    self._do_exec()
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1521, in _do_exec
    self._execute_stmt(delete_stmt)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/persistence.py", line 1336, in _execute_stmt
    mapper=self.mapper)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1176, in execute
    bind, close_with_result=True).execute(clause, params or {})
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 948, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement
    compiled_sql, distilled_params
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception
    exc_info
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
    context)
  File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 509, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) update or delete on table "users" violates foreign key constraint "events_user_id_fkey" on table "events"
DETAIL:  Key (id)=(5) is still referenced from table "events".
 [SQL: 'DELETE FROM users WHERE users.email = lower(%(lower_1)s)'] [parameters: {'lower_1': u'xxxxxxxxxx@xxxxxxxx.com'}] (Background on this error at: http://sqlalche.me/e/gkpj)

外部キー制約のある子供を物理的に消しに行きます。まず、postgresのコンソールに入ります。

root@ip-172-31-28-245:/opt/redash# docker exec -it redash_postgres_1 bash
bash-5.0# su postgres
/ $ psql
psql (9.6.15)
Type "help" for help.

postgres=# \d
                        List of relations
 Schema |               Name               |   Type   |  Owner
--------+----------------------------------+----------+----------
 public | access_permissions               | table    | postgres
 public | access_permissions_id_seq        | sequence | postgres
 public | alembic_version                  | table    | postgres
 public | alert_subscriptions              | table    | postgres
 public | alert_subscriptions_id_seq       | sequence | postgres
 public | alerts                           | table    | postgres
 public | alerts_id_seq                    | sequence | postgres
 public | api_keys                         | table    | postgres
 public | api_keys_id_seq                  | sequence | postgres
 public | changes                          | table    | postgres
 public | changes_id_seq                   | sequence | postgres
 public | dashboards                       | table    | postgres
 public | dashboards_id_seq                | sequence | postgres
 public | data_source_groups               | table    | postgres
 public | data_source_groups_id_seq        | sequence | postgres
 public | data_sources                     | table    | postgres
 public | data_sources_id_seq              | sequence | postgres
 public | events                           | table    | postgres
 public | events_id_seq                    | sequence | postgres
 public | favorites                        | table    | postgres
 public | favorites_id_seq                 | sequence | postgres
 public | groups                           | table    | postgres
 public | groups_id_seq                    | sequence | postgres
 public | notification_destinations        | table    | postgres
 public | notification_destinations_id_seq | sequence | postgres
 public | organizations                    | table    | postgres
 public | organizations_id_seq             | sequence | postgres
 public | queries                          | table    | postgres
 public | queries_id_seq                   | sequence | postgres
 public | query_results                    | table    | postgres
 public | query_results_id_seq             | sequence | postgres
 public | query_snippets                   | table    | postgres
 public | query_snippets_id_seq            | sequence | postgres
 public | users                            | table    | postgres
 public | users_id_seq                     | sequence | postgres
 public | visualizations                   | table    | postgres
 public | visualizations_id_seq            | sequence | postgres
 public | widgets                          | table    | postgres
 public | widgets_id_seq                   | sequence | postgres
(39 rows)
postgres=# \d events
                                         Table "public.events"
        Column         |           Type           |                      Modifiers
-----------------------+--------------------------+-----------------------------------------------------
 id                    | integer                  | not null default nextval('events_id_seq'::regclass)
 org_id                | integer                  | not null
 user_id               | integer                  |
 action                | character varying(255)   | not null
 object_type           | character varying(255)   | not null
 object_id             | character varying(255)   |
 additional_properties | text                     |
 created_at            | timestamp with time zone | not null
Indexes:
    "events_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "events_org_id_fkey" FOREIGN KEY (org_id) REFERENCES organizations(id)
    "events_user_id_fkey" FOREIGN KEY (user_id) REFERENCES users(id)

見つけて、レコードを消します。

postgres=# select * from events where user_id = 5;
  id   | org_id | user_id |     action     | object_type |     object_id     |                                                                                                             additional_properties                                                                                                              |         created_at
-------+--------+---------+----------------+-------------+-------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------
 17041 |      1 |       5 | login          | redash      |                   | {"ip": "172.31.28.17", "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36"}                                                                              | 2020-01-21 02:33:38+00
 17042 |      1 |       5 | load_favorites | query       |                   | {"ip": "172.31.28.17", "params": {"q": null, "page": 1, "tags": []}, "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36", "user_name": "Yuki Matsukura"} | 2020-01-21 02:33:39+00
 17043 |      1 |       5 | load_favorites | dashboard   |                   | {"ip": "172.31.28.17", "params": {"q": null, "page": 1, "tags": []}, "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36", "user_name": "Yuki Matsukura"} | 2020-01-21 02:33:39+00
 17044 |      1 |       5 | load_favorites | query       |                   | {"ip": "172.31.28.17", "params": {"q": null, "page": 1, "tags": []}, "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36", "user_name": "Yuki Matsukura"} | 2020-01-21 02:33:39+00
 17045 |      1 |       5 | load_favorites | dashboard   |                   | {"ip": "172.31.28.17", "params": {"q": null, "page": 1, "tags": []}, "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36", "user_name": "Yuki Matsukura"} | 2020-01-21 02:33:39+00
 17046 |      1 |       5 | view           | page        | personal_homepage | {"screen_resolution": "1920x1200", "ip": "172.31.28.17", "user_name": "Yuki Matsukura", "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36"}             | 2020-01-21 02:33:39.859+00
 17047 |      1 |       5 | view           | user        | 5                 | {"ip": "172.31.28.17", "user_name": "Yuki Matsukura", "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36"}                                               | 2020-01-21 02:33:44+00
 17048 |      1 |       5 | list           | group       | groups            | {"ip": "172.31.28.17", "user_name": "Yuki Matsukura", "user_agent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.117 Safari/537.36"}                                               | 2020-01-21 02:33:44+00
(8 rows)

postgres=# delete from events where user_id = 5;
DELETE 8

そして、削除

root@ip-172-31-28-245:/opt/redash# docker exec -it redash_server_1 ./manage.py users delete xxxxxxxxxx@xxxxxxxx.com
Deleted 1 users.

めでたしめでたし

コメントを残す