Page MenuHomePhabricator

Remove grants for the old dbproxy hosts from the misc databases
Open, MediumPublic

Description

Once the old proxies are not active, we need to remove those grants from the databases.
The list of proxies and IPs:

Related Objects

Event Timeline

Marostegui triaged this task as Medium priority.Aug 27 2019, 9:19 AM
Marostegui created this task.
Marostegui moved this task from Triage to In progress on the DBA board.

Mentioned in SAL (#wikimedia-operations) [2019-08-27T09:21:30Z] <marostegui> Remove grants for dbproxy1004 and dbproxy1009 from m4 hosts (db1107 and db1108) - T231280

m4: Grants removed on db1107 and db1108 for dbproxy1004 and dbproxy1009

dbproxy1004.eqiad.wmnet has address 10.64.16.148
dbproxy1009.eqiad.wmnet has address 10.64.32.158

The users were eventlog and haproxy.

root@db1107.eqiad.wmnet[mysql]> select user,host from user where host='10.64.32.148' or host='10.64.32.158';
Empty set (0.00 sec)

root@db1107.eqiad.wmnet[mysql]>
Marostegui updated the task description. (Show Details)Aug 27 2019, 9:24 AM

Mentioned in SAL (#wikimedia-operations) [2019-09-05T05:42:56Z] <marostegui> Remove grants for dbproxy1005 T231280 T231967

Marostegui updated the task description. (Show Details)Sep 5 2019, 6:01 AM

I have removed the following grants that were for dbproxy1005, from m5 databases (and also checked they don't exist anywhere else):

# host 10.64.16.155
155.16.64.10.in-addr.arpa domain name pointer dbproxy1005.eqiad.wmnet.
+----------------+--------------+
| user           | host         |
+----------------+--------------+
| designate      | 10.64.16.155 |
| haproxy        | 10.64.16.155 |
| labsdbaccounts | 10.64.16.155 |
| root           | 10.64.16.155 |
| ssastry        | 10.64.16.155 |
| testreduce     | 10.64.16.155 |
+----------------+--------------+
6 rows in set (0.00 sec)
root@db1133.eqiad.wmnet[(none)]> select user,host from mysql.user where host='10.64.16.155';
Empty set (0.00 sec)
Marostegui updated the task description. (Show Details)Sep 5 2019, 6:03 AM

Mentioned in SAL (#wikimedia-operations) [2019-10-07T07:09:58Z] <marostegui> Remove grants for dbproxy1006 on m1 databases - T231280

The following grants for dbproxy1006 IP's have been removed from m1 databases:

# host dbproxy1006
dbproxy1006.eqiad.wmnet has address 10.64.16.159


root@db1135.eqiad.wmnet[mysql]> select user,host from user where host='10.64.16.159';
+--------------+--------------+
| user         | host         |
+--------------+--------------+
| bacula       | 10.64.16.159 |
| etherpadlite | 10.64.16.159 |
| haproxy      | 10.64.16.159 |
| librenms     | 10.64.16.159 |
| rddmarc      | 10.64.16.159 |
| rt           | 10.64.16.159 |
+--------------+--------------+
6 rows in set (0.00 sec)

After removing them:

root@db1135.eqiad.wmnet[mysql]> select user,host from user where host='10.64.16.159';
Empty set (0.00 sec)
Marostegui updated the task description. (Show Details)Oct 7 2019, 7:12 AM

Change 545142 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/software@master] report_users: Remove dbproxy1004,dbproxy1009

https://gerrit.wikimedia.org/r/545142

Change 545142 merged by jenkins-bot:
[operations/software@master] report_users: Remove dbproxy1004,dbproxy1009

https://gerrit.wikimedia.org/r/545142

Marostegui moved this task from In progress to Next on the DBA board.Nov 19 2019, 1:41 PM

Mentioned in SAL (#wikimedia-operations) [2020-02-11T06:48:57Z] <marostegui> Remove grants in m1 for dbproxy1001 - T231280

Grants removed in db1135 with replication

root@db1135.eqiad.wmnet[(none)]> select user from mysql.user where host like '10.64.0.165';
+--------------+
| user         |
+--------------+
| bacula       |
bacula
| bacula9      |
| bloguser     |
| bugs         |
| contacts     |
| designate    |
| etherpadlite |
| haproxy      |
| librenms     |
| pdns         |
| pdns_admin   |
| rddmarc      |
| rt           |
+--------------+

root@db1135.eqiad.wmnet[(none)]> select user from mysql.user where host like '10.64.0.165';
Empty set (0.01 sec)

root@db1135.eqiad.wmnet[(none)]>
Marostegui updated the task description. (Show Details)Feb 11 2020, 6:53 AM

Change 571424 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m1.sql: Remove grants for dbproxy1001

https://gerrit.wikimedia.org/r/571424

Change 571424 merged by Marostegui:
[operations/puppet@production] production-m1.sql: Remove grants for dbproxy1001

https://gerrit.wikimedia.org/r/571424

Marostegui updated the task description. (Show Details)Feb 17 2020, 6:34 AM
Marostegui updated the task description. (Show Details)Feb 17 2020, 9:47 AM
Marostegui updated the task description. (Show Details)Feb 18 2020, 6:20 AM
Marostegui moved this task from Next to In progress on the DBA board.Feb 18 2020, 6:52 AM

Mentioned in SAL (#wikimedia-operations) [2020-02-19T08:50:48Z] <marostegui> Remove dbproxy1007 grants from m2 - T231280

Marostegui updated the task description. (Show Details)Feb 19 2020, 8:53 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-02T09:47:32Z] <marostegui> Remove haproxy@10.64.37.14 from labsdb hosts - T231280 T248944

Marostegui updated the task description. (Show Details)Apr 2 2020, 9:49 AM
root@cumin1001:~# host dbproxy1010
dbproxy1010.eqiad.wmnet has address 10.64.37.14
root@cumin1001:~# for i in labsdb1009 labsdb1010 labsdb1011 labsdb1012; do mysql.py -h$i -e "show grants for 'haproxy'@'10.64.37.14';";done
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.37.14'
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.37.14'
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.37.14'
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.37.14'
Marostegui updated the task description. (Show Details)Apr 2 2020, 10:01 AM

Mentioned in SAL (#wikimedia-operations) [2020-04-13T08:15:06Z] <marostegui> Remove grants for haproxy@10.64.37.15 from labsdb hosts T231280

dbproxy1011 grants removed

root@cumin1001:/home/marostegui# host dbproxy1011
dbproxy1011.eqiad.wmnet has address 10.64.37.15
root@cumin1001:/home/marostegui# for i in labsdb1009 labsdb1010 labsdb1011 labsdb1012; do mysql.py -h$i -e "show grants for 'haproxy'@'10.64.37.15';";done
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.37.15'
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.37.15'
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.37.15'
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.37.15'
Marostegui updated the task description. (Show Details)Apr 13 2020, 8:17 AM
Marostegui updated the task description. (Show Details)

Mentioned in SAL (#wikimedia-operations) [2020-06-24T05:14:56Z] <marostegui> Remove grants from dbproxy1008 - T231280 T255406

dbproxy1008 grants removed from m3 (and also checked all the other mX sections):

root@cumin2001:/home/marostegui# ./section m3 | while read host port; do echo $host; mysql.py -h$host:$port -e "show grants for 'haproxy'@'10.64.32.157';"; done
db2134.codfw.wmnet
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.32.157'
db2078.codfw.wmnet
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.32.157'
db1128.eqiad.wmnet
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.32.157'
db1117.eqiad.wmnet
ERROR 1141 (42000) at line 1: There is no such grant defined for user 'haproxy' on host '10.64.32.157'

Change 607407 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m3.sql: Remove grants for dbproxy1008

https://gerrit.wikimedia.org/r/607407

Change 607407 merged by Marostegui:
[operations/puppet@production] production-m3.sql: Remove grants for dbproxy1008

https://gerrit.wikimedia.org/r/607407

Marostegui updated the task description. (Show Details)Wed, Jun 24, 5:28 AM
Marostegui updated the task description. (Show Details)Wed, Jun 24, 10:27 AM