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 triaged this task as Medium priority.Oct 29 2021, 4:49 AM
Marostegui moved this task from Triage to Ready on the DBA board.
Marostegui added a subscriber: Marostegui.

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