Page MenuHomePhabricator

Audit labswiki grants
Closed, ResolvedPublic

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

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald Transcript
Marostegui triaged this task as Medium priority.May 6 2021, 5:34 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

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.

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?

hashar added subscribers: hoo, hashar.
wikiadmin208.80.154.136
wikiuser208.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:

wikiadmin208.80.153.14NXDOMAIN
wikiuser208.80.153.14NXDOMAIN

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

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.

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

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

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

Marostegui added a project: DBA.

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!