Page MenuHomePhabricator

Drop OAuth-related tables from foundationwiki
Closed, ResolvedPublic

Description

foundationwiki used to have OAuth tables, as it was a fishbowl wiki, detached from CentralAuth, our global source of authentication details. Today, that wiki was changed to be a CentralAuth-connected wiki, and it uses the global oauth tables from metawiki now.

As a cleanup, please drop the following tables from foundationwiki:

wikiadmin@10.64.16.208(foundationwiki)> show tables like 'oauth%';
+-----------------------------------+
| Tables_in_foundationwiki (oauth%) |
+-----------------------------------+
| oauth2_access_tokens              |
| oauth_accepted_consumer           |
| oauth_registered_consumer         |
+-----------------------------------+
3 rows in set (0.001 sec)

wikiadmin@10.64.16.208(foundationwiki)>

Nothing should write to them/read from them from now on.

Event Timeline

Marostegui moved this task from Triage to Ready on the DBA board.
Marostegui subscribed.

We'll rename them first to make sure nothing breaks and after a few days we'll nuke them!.
(Removing the Schema-change tag as DROPs aren't considered a schema change)

We'll rename them first to make sure nothing breaks and after a few days we'll nuke them!.

Sounds good to me! Thanks.

(Removing the Schema-change tag as DROPs aren't considered a schema change)

Just out of curiosity, why does Schema-change have a column called "Delete / Drop / Remove", if drops aren't schema changes?

We'll rename them first to make sure nothing breaks and after a few days we'll nuke them!.

Sounds good to me! Thanks.

(Removing the Schema-change tag as DROPs aren't considered a schema change)

Just out of curiosity, why does Schema-change have a column called "Delete / Drop / Remove", if drops aren't schema changes?

I don't know, we don't really own that tag :-)

Marostegui moved this task from Ready to In progress on the DBA board.

The tables have not been written in years and are tiny:

root@db1112:/srv/sqldata/foundationwiki# ls -lh oauth2_access_tokens.ibd
-rw-rw---- 1 mysql mysql 128K Nov 22  2019 oauth2_access_tokens.ibd
root@db1112:/srv/sqldata/foundationwiki# ls -lh oauth_accepted_consumer.ibd
-rw-rw---- 1 mysql mysql 160K Jul  9  2020 oauth_accepted_consumer.ibd
root@db1112:/srv/sqldata/foundationwiki# ls -lh oauth_registered_consumer.ibd
-rw-rw---- 1 mysql mysql 160K Nov 19  2019 oauth_registered_consumer.ibd

I am going to rename this table on db1123 and see if something attempts to read from them.

Mentioned in SAL (#wikimedia-operations) [2021-11-02T06:45:01Z] <marostegui> Rename oauth2_access_tokens oauth_accepted_consumer oauth_registered_consumer tables on db1123 T294595

Tables renamed, let's leave them for a few days before dropping them

mysql:root@localhost [foundationwiki]> show tables like 'T%';
+-----------------------------------+
| Tables_in_foundationwiki (T%)     |
+-----------------------------------+
| T294595_oauth2_access_tokens      |
| T294595_oauth_accepted_consumer   |
| T294595_oauth_registered_consumer |
+-----------------------------------+
3 rows in set (0.000 sec)

mysql:root@localhost [foundationwiki]> select @@hostname;
+------------+
| @@hostname |
+------------+
| db1123     |
+------------+
1 row in set (0.000 sec)

oauth2_access_tokens and oauth_accepted_consumer are empty. oauth_registered_consumer has 1 row.

For the record:

CREATE TABLE `oauth2_access_tokens` (
  `oaat_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `oaat_identifier` varbinary(255) NOT NULL,
  `oaat_expires` varbinary(14) NOT NULL,
  `oaat_acceptance_id` int(10) unsigned NOT NULL,
  `oaat_revoked` tinyint(4) NOT NULL DEFAULT 0,
  PRIMARY KEY (`oaat_id`),
  UNIQUE KEY `oaat_identifier` (`oaat_identifier`),
  KEY `oaat_acceptance_id` (`oaat_acceptance_id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;


CREATE TABLE `oauth_accepted_consumer` (
  `oaac_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `oaac_wiki` varbinary(255) NOT NULL,
  `oaac_user_id` int(10) unsigned NOT NULL,
  `oaac_consumer_id` int(10) unsigned NOT NULL,
  `oaac_access_token` varbinary(32) NOT NULL,
  `oaac_access_secret` varbinary(32) NOT NULL,
  `oaac_grants` blob NOT NULL,
  `oaac_accepted` varbinary(14) NOT NULL,
  `oaac_oauth_version` tinyint(4) NOT NULL DEFAULT 1,
  PRIMARY KEY (`oaac_id`),
  UNIQUE KEY `oaac_access_token` (`oaac_access_token`),
  UNIQUE KEY `oaac_user_consumer_wiki` (`oaac_user_id`,`oaac_consumer_id`,`oaac_wiki`),
  KEY `oaac_consumer_user` (`oaac_consumer_id`,`oaac_user_id`),
  KEY `oaac_user_id` (`oaac_user_id`,`oaac_id`)
) ENGINE=InnoDB DEFAULT CHARSET=binary;

CREATE TABLE `oauth_registered_consumer` (
  `oarc_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `oarc_consumer_key` varbinary(32) NOT NULL,
  `oarc_name` varbinary(128) NOT NULL,
  `oarc_user_id` int(10) unsigned NOT NULL,
  `oarc_version` varbinary(32) NOT NULL,
  `oarc_callback_url` blob NOT NULL,
  `oarc_callback_is_prefix` tinyblob DEFAULT NULL,
  `oarc_description` blob NOT NULL,
  `oarc_email` varbinary(255) NOT NULL,
  `oarc_email_authenticated` varbinary(14) DEFAULT NULL,
  `oarc_developer_agreement` tinyint(4) NOT NULL DEFAULT 0,
  `oarc_owner_only` tinyint(4) NOT NULL DEFAULT 0,
  `oarc_wiki` varbinary(32) NOT NULL,
  `oarc_grants` blob NOT NULL,
  `oarc_registration` varbinary(14) NOT NULL,
  `oarc_secret_key` varbinary(32) DEFAULT NULL,
  `oarc_rsa_key` blob DEFAULT NULL,
  `oarc_restrictions` blob NOT NULL,
  `oarc_stage` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `oarc_stage_timestamp` varbinary(14) NOT NULL,
  `oarc_deleted` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `oarc_oauth_version` tinyint(4) NOT NULL DEFAULT 1,
  `oarc_oauth2_allowed_grants` blob DEFAULT NULL,
  `oarc_oauth2_is_confidential` tinyint(4) NOT NULL DEFAULT 1,
  PRIMARY KEY (`oarc_id`),
  UNIQUE KEY `oarc_consumer_key` (`oarc_consumer_key`),
  UNIQUE KEY `oarc_name_version_user` (`oarc_name`,`oarc_user_id`,`oarc_version`),
  KEY `oarc_user_id` (`oarc_user_id`),
  KEY `oarc_stage_timestamp` (`oarc_stage`,`oarc_stage_timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=binary;

Mentioned in SAL (#wikimedia-operations) [2021-11-03T07:50:19Z] <marostegui> Drop oauth2_access_tokens oauth_accepted_consumer oauth_registered_consumer from foundationwiki T294595

root@db1157.eqiad.wmnet[foundationwiki]> select count(*) from oauth2_access_tokens; select count(*) from oauth_accepted_consumer; select count(*) from oauth_registered_consumer;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.002 sec)

+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.003 sec)

+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.003 sec)

Left a temporary backup at:

root@cumin1001:/home/marostegui/T294595# ls -lh
total 12K
-rw-r--r-- 1 root root 2.2K Nov  3 07:39 oauth2_access_tokens.sql
-rw-r--r-- 1 root root 2.6K Nov  3 07:39 oauth_accepted_consumer.sql
-rw-r--r-- 1 root root 3.8K Nov  3 07:39 oauth_registered_consumer.sql