概要
- Redashのユーザを作って、そのユーザでログインしてしまった後にそのユーザを物理削除する方法の紹介です。
- 管理画面からEnable/Disableはできますが、削除はできません。SSOでユーザを作ってしまったらメアドの変更ができないので退避もできません。
前提
- Redashのバージョン8.0.0.b32245
- AWS AMIで構築しているので、docker-compose管理下です。ubuntuユーザでSSHして、sudo -sでrootになります。
削除方法
削除したいユーザを見つけます。
root@ip-172-31-28-245:/opt/redash# docker exec -it redash_server_1 ./manage.py users list <省略> -------------------- Id: 5 Name: Yuki Matsukura Email: [email protected] 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 [email protected] 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'[email protected]'}] (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 [email protected] Deleted 1 users.
めでたしめでたし
Comments