Maniphest T282074

Audit labswiki grants
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	• Marostegui
	May 6 2021, 5:09 AM

Description

We are aiming to move labswiki from m5 to s6, we are elaborating a migration document (which, once defined, will be posted on the parent task). However, we have multiple grants for wikiuser and wikiadmin, we need to know which ones are really needed to be copied to s6.

root@db1128.eqiad.wmnet[mysql]> select user,host from user where user like '%wik%';
+-----------+----------------+
| User      | Host           |
+-----------+----------------+
| wikiadmin | %              |
| wikiuser  | %              |
| wikiadmin | 10.192.32.22   |
| wikiadmin | 10.192.32.34   |
| wikiadmin | 10.192.32.7    |
| wikiadmin | 10.192.48.45   |
| wikiadmin | 10.64.0.196    |
| wikiadmin | 10.64.16.77    |
| wikiadmin | 10.64.32.28    |
| wikiadmin | 208.80.153.14  |
| wikiuser  | 208.80.153.14  |
| wikiadmin | 208.80.154.136 |
| wikiuser  | 208.80.154.136 |
| wikiadmin | 208.80.154.160 |
| wikiuser  | 208.80.154.160 |
| wikiadmin | 208.80.155.109 |
| wikiuser  | 208.80.155.109 |
| wikiadmin | localhost      |
| wikiuser  | localhost      |
+-----------+----------------+
19 rows in set (0.002 sec)

Can we get these reviewed?
Thanks

Related Objects
Search...

Status	Assigned	Task
Resolved	Clement_Goubert	T327920 March 2023 Datacenter Switchover
Open	Clement_Goubert	T328907 Post March 2023 Datacenter Switchover Tasks
		Restricted Task
Resolved	Legoktm	T281515 June 2021 Datacenter switchover
Resolved	• Marostegui	T285139 DB maintenance work to do while eqiad is passive (June 2021)
Open	None	T189531 All Wikimedia developer services should use single sign-on
Open	None	T363125 sustainability of wikitech.wikimedia.org
Open	None	T161859 Make Wikitech an SUL wiki
Duplicate	None	T237771 Make Wikitech a normal wiki
Duplicate	None	T237772 Move Wikitech's database off of M5 and onto a normal production wiki DB server
Declined	None	T171570 Rename database 'labswiki' to 'wikitechwiki'
Open	None	T217792 Add wikitech (labswiki) to the sqoop list
Open	None	T305233 consider eliminating labweb/cloudweb hardware servers
Open	None	T237773 Move Wikitech onto the production MW cluster
Resolved	• Marostegui	T167973 Move database for wikitech (labswiki) to a main cluster section
Resolved	• Marostegui	T282074 Audit labswiki grants

Event Timeline

• Marostegui created this task.May 6 2021, 5:09 AM

Restricted Application edited projects, added cloud-services-team (Kanban); removed cloud-services-team. · View Herald TranscriptMay 6 2021, 5:09 AM

Restricted Application added a subscriber: Aklapper. · View Herald Transcript

• Marostegui triaged this task as Medium priority.May 6 2021, 5:34 AM

• Marostegui mentioned this in T282209: Enable connection from labweb1001/labweb1002 to s6.May 7 2021, 4:53 AM

I can see grants there for gerrit1001, does gerrit need to connect to wikitech? If that is the case, then in T282209 we probably need to open that rule too.

It might an old grant or something we can clean up as there is no connectivity at the moment:

root@gerrit1001:~# telnet db1131.eqiad.wmnet 3306
Trying 10.64.32.6...
^C
root@gerrit1001:~# telnet db1128.eqiad.wmnet 3306
Trying 10.64.0.98...
telnet: Unable to connect to remote host: Connection timed out

In T282074#7068485, @Marostegui wrote:

I can see grants there for gerrit1001, does gerrit need to connect to wikitech? If that is the case, then in T282209 we probably need to open that rule too.

@thcipriani can you think of any reason that Gerrit actively needs a db connection to wikitech? The only think I can think of is that maybe someone tried to integrate with the 2FA on wikitech at some point? There was a period where we had hacked Horizon to do that before I made an Action API to handle token verification that Horizon and Striker use now.

Andrew claimed this task.Jun 1 2021, 8:15 PM

Adding some digging for context

+-----------+----------------+
| User      | Host           |
+-----------+----------------+
| wikiadmin | %              |
| wikiuser  | %              |
| wikiadmin | 10.192.32.22   | restbase2015-a.codfw.wmnet
| wikiadmin | 10.192.32.34   | mwmaint2002.codfw.wmnet
| wikiadmin | 10.192.32.7    | deploy2002.codfw.wmnet
| wikiadmin | 10.192.48.45   | wcqs2003.codfw.wmnet
| wikiadmin | 10.64.0.196    | mw1392.eqiad.wmnet
| wikiadmin | 10.64.16.77    | mwmaint1002.eqiad.wmnet
| wikiadmin | 10.64.32.28    | deploy1002.eqiad.wmnet
| wikiadmin | 208.80.153.14  | NXDOMAIN
| wikiuser  | 208.80.153.14  | NXDOMAIN
| wikiadmin | 208.80.154.136 | gerrit1001.wikimedia.org
| wikiuser  | 208.80.154.136 | gerrit1001.wikimedia.org
| wikiadmin | 208.80.154.160 | labweb1001.wikimedia.org
| wikiuser  | 208.80.154.160 | labweb1001.wikimedia.org
| wikiadmin | 208.80.155.109 | labweb1002.wikimedia.org
| wikiuser  | 208.80.155.109 | labweb1002.wikimedia.org
| wikiadmin | localhost      |
| wikiuser  | localhost      |
+-----------+----------------+

The only ones in that list that I /know/ I need is labweb1001 and labweb1002. I'd expect the remaining grants to either already be present on s6 (becaose of other wikis needed them) or be unneeded.

Thanks Andrew.
Yes, the other ones should be covered by 10.64.% and 10.192.% ones we have.
The gerrit ones are finally not needed then?

Andrew added projects: Gerrit, Release-Engineering-Team.Jun 7 2021, 6:25 PM

wikiadmin	208.80.154.136
wikiuser	208.80.154.136

Gerrit no more use a database since we upgrade it back in June 2020 and it definitely never used wikiadmin or wikiuser. The IP is currently assigned to gerrit1001.wikimedia.org which is relatively recent, so I guess that grant has been assigned to the IP while it was assigned to another host.

And from this 2015 comment:

In T98682#1285179, @hoo wrote:

...
On top of that, the wikiadmin/wikiuser logins wont work from anything but silver itself:

mysql:root@localhost [(none)]> SELECT Host, User FROM mysql.user WHERE User = 'wikiadmin' OR User = 'wikiuser';
+----------------+-----------+
| Host           | User      |
+----------------+-----------+
| 208.80.154.136 | wikiadmin |
| 208.80.154.136 | wikiuser  |
+----------------+-----------+
2 rows in set (0.01 sec)

The history of assignement can be found via operations/dns.git:

commit 56ba16473f18e3bbaf7dc127b58b04dd264f4e3f
Author: cmjohnson <cmjohnson@wikimedia.org>
Date: Wed Aug 29 13:57:37 2018 -0400

Removing production dns for decom host silver
Bug: T191357
Change-Id: I750fa3e7981f10ee4ad3bffa452c1f9c333c67f7

commit 8513f0dec41e9e675e258e6a8862e94e5b12b466
Author: RobH <rob@wikimedia.org>
Date: Thu Aug 22 15:15:42 2019 -0700

adding gerrit1001 production dns entries

both ipv4 and ipv6 entries

Bug: T231046
Change-Id: I7c53a714e55b025debce78e8963e27ad56ee5d40

Thus 208.80.154.136 used to be on silver and get later reassigned to the "new" gerrit1001. Gerrit does not rely on any MySQL database anymore. In conclusion: the grant can safely be removed :]

And still from dns.git:

wikiadmin	208.80.153.14	NXDOMAIN
wikiuser	208.80.153.14	NXDOMAIN

That was labtestweb2001

git log -G14 templates/153.80.208.in-addr.arpa

commit 207eefab4f67ae8fed6cc3ef504a73ebb28d2b49
Author: RobH <rob@wikimedia.org>
Date: Tue Apr 23 12:59:50 2019 -0700

decommission labtestweb2001 production dns entries

system prod dns entry removal

Bug: T218024

> Change-Id: I215a70715b4bacfdb24c4efc02f3006cd54daed5

antoine-approve

I think that's all of them explained! Back to you @Marostegui

hashar unsubscribed.Jun 9 2021, 8:00 AM

Thanks for all the comments.
I have dropped the following:

# host 208.80.153.14
Host 14.153.80.208.in-addr.arpa. not found: 3(NXDOMAIN)

root@db1128.eqiad.wmnet[(none)]> drop user if exists 'wikiadmin'@'208.80.153.14';
Query OK, 0 rows affected (0.002 sec)

root@db1128.eqiad.wmnet[(none)]> drop user if exists 'wikiuser'@'208.80.153.14';
Query OK, 0 rows affected (0.002 sec)

And now the gerrit ones:

show grants for 'wikiadmin'@'208.80.154.136';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for wikiadmin@208.80.154.136                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, REPLICATION CLIENT ON *.* TO `wikiadmin`@`208.80.154.136` IDENTIFIED BY PASSWORD '*x'                                                                                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `labswiki`.* TO `wikiadmin`@`208.80.154.136` |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)


root@db1128.eqiad.wmnet[(none)]> drop user if exists 'wikiadmin'@'208.80.154.136';
Query OK, 0 rows affected (0.001 sec)

root@db1128.eqiad.wmnet[(none)]> drop user if exists 'wikiuser'@'208.80.154.136';
Query OK, 0 rows affected (0.002 sec)

And some that look like left overs:

# host 10.192.32.22
22.32.192.10.in-addr.arpa domain name pointer restbase2015-a.codfw.wmnet.

root@db1128.eqiad.wmnet[(none)]> show grants for 'wikiadmin'@'10.192.32.22';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for wikiadmin@10.192.32.22                                                                                                                                                                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, REPLICATION CLIENT ON *.* TO `wikiadmin`@`10.192.32.22` IDENTIFIED BY PASSWORD '*x'                                                                                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `labswiki`.* TO `wikiadmin`@`10.192.32.22` |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

show grants for 'wikiadmin'@'10.192.48.45';
+---------------------------------------------------------------------------------------------------------------------+
| Grants for wikiadmin@10.192.48.45                                                                                   |
+---------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `wikiadmin`@`10.192.48.45` IDENTIFIED BY PASSWORD '*x' |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

root@db1128.eqiad.wmnet[(none)]> drop user if exists 'wikiadmin'@'10.192.48.45';
Query OK, 0 rows affected (0.001 sec)

root@db1128.eqiad.wmnet[(none)]> show grants for 'wikiadmin'@'10.64.0.196';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for wikiadmin@10.64.0.196                                                                                                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS, REPLICATION CLIENT ON *.* TO `wikiadmin`@`10.64.0.196` IDENTIFIED BY PASSWORD '*x'                                                                                                        |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON `labswiki`.* TO `wikiadmin`@`10.64.0.196` |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

root@db1128.eqiad.wmnet[(none)]> drop user if exists 'wikiadmin'@'10.64.0.196';;
Query OK, 0 rows affected (0.001 sec)

So this looks a lot more clean now:

root@db1128.eqiad.wmnet[mysql]> select user,host from user where user like '%wik%';
+-----------+----------------+
| User      | Host           |
+-----------+----------------+
| wikiadmin | %              |
| wikiuser  | %              |
| wikiadmin | 10.192.32.34   |
| wikiadmin | 10.192.32.7    |
| wikiadmin | 10.64.16.77    |
| wikiadmin | 10.64.32.28    |
| wikiadmin | 208.80.154.160 |
| wikiuser  | 208.80.154.160 |
| wikiadmin | 208.80.155.109 |
| wikiuser  | 208.80.155.109 |
| wikiadmin | localhost      |
| wikiuser  | localhost      |
+-----------+----------------+
12 rows in set (0.002 sec)

Next step is to add the grants for labweb1001,1002 to s6:

# host 208.80.154.160
160.154.80.208.in-addr.arpa domain name pointer labweb1001.wikimedia.org.
# host 208.80.155.109
109.155.80.208.in-addr.arpa domain name pointer labweb1002.wikimedia.org.

hashar removed projects: Release-Engineering-Team (Radar), Gerrit.Jun 10 2021, 8:16 AM

Mentioned in SAL (#wikimedia-operations) [2021-06-10T08:17:45Z] <marostegui> Drop several grants from labswiki (wikitech) T282074

In T282074#7147852, @Marostegui wrote:
Thanks for all the comments.
I have dropped the following:
# host 208.80.153.14

This used to be labtestweb2001, see https://gerrit.wikimedia.org/r/c/operations/dns/+/505894

In T282074#7147991, @ArielGlenn wrote:
In T282074#7147852, @Marostegui wrote:
Thanks for all the comments.
I have dropped the following:
# host 208.80.153.14
This used to be labtestweb2001, see https://gerrit.wikimedia.org/r/c/operations/dns/+/505894

Yeah, and as it no longer exists, I have dropped it

I have deployed the following grants on s6:

| wikiadmin | 208.80.154.160 |
| wikiuser  | 208.80.154.160 |
| wikiadmin | 208.80.155.109 |
| wikiuser  | 208.80.155.109 |

# host 208.80.154.160 ; host 208.80.155.109
160.154.80.208.in-addr.arpa domain name pointer labweb1001.wikimedia.org.
109.155.80.208.in-addr.arpa domain name pointer labweb1002.wikimedia.org.

Thanks everyone!

Audit labswiki grantsClosed, ResolvedPublicActions

Description

Related ObjectsSearch...

Event Timeline

Audit labswiki grants
Closed, ResolvedPublic
Actions

Related Objects
Search...