Page MenuHomePhabricator

labsdbaccounts database grant for cloudcontrol1005
Closed, ResolvedPublic

Description

Context
We are moving a script (that authenticates to the labsdbaccounts database on m5-master.eqiad.wmnet) from labstore1004.eqiad.wmnet (10.64.37.19) to cloudcontrol1005.wikimedia.org (208.80.154.85). We need to give cloudcontrol1005.wikimedia.org (208.80.154.85) the same database grants that labstore1004.eqiad.wmnet (10.64.37.19) is given in the labsdbaccounts database.

database host: m5-master.eqiad.wmnet
database: labsdbaccounts
username: labsdbaccounts

old host: labstore1004.eqiad.wmnet
old host ip: 10.64.37.19

new host: cloudcontrol1005.wikimedia.org
new host ip: 208.80.154.85

grant: labsdbaccounts database grants currently assigned to labstore1004.eqiad.wmnet (I don't have access to m5-master.eqiad.wmnet so can't check this myself)

Note: the labsdbaccounts database grants currently assigned to labstore1004.eqiad.wmnet should not be revoked because labstore1004.eqiad.wmnet is still in use.

Event Timeline

@dcaro can you check this if I missed anything that should be here?

Currently these are the grants existing on m5:

+----------------+--------------+
| User           | Host         |
+----------------+--------------+
| labsdbaccounts | 10.192.48.47 |
| labsdbaccounts | 10.64.32.180 |
| labsdbaccounts | 10.64.37.19  |
| labsdbaccounts | 10.64.37.20  |
| labsdbaccounts | 10.64.48.43  |
+----------------+--------------+
5 rows in set (0.002 sec)

I'd like to take this opportunity to clean them up before adding the new ones. Could you clarify which ones aren't needed anymore? - if all of them are still needed that's ok.

Marostegui triaged this task as Medium priority.
Marostegui edited projects, added DBA; removed Data-Persistence.
Marostegui moved this task from Triage to In progress on the DBA board.

For the record the GRANTS are:

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 `labsdbaccounts`.* TO `labsdbaccounts`@`10.64.37.19`

In case they change, those ips are currently:

  • 10.192.48.47 -> dbproxy2004.codfw.wmnet
  • 10.64.32.180 -> dbproxy1021.eqiad.wmnet
  • 10.64.37.19 -> labstore1004.eqiad.wmnet
  • 10.64.37.20 -> labstore1005.eqiad.wmnet
  • 10.64.48.43 -> dbproxy1017.eqiad.wmnet

Currently all of them are needed, but once we have moved the NFS servers, both the labstore ones will not be needed (just added it to the bucket list of the process).

Thanks @dcaro

I have created the user for 208.80.154.85, so this is what we have now on m5 master:

root@db1183.eqiad.wmnet[mysql]> show grants for 'labsdbaccounts'@'208.80.154.85';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for labsdbaccounts@208.80.154.85                                                                                                                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `labsdbaccounts`@`208.80.154.85` IDENTIFIED BY PASSWORD '*xx'                                                                                                                                    |
| 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 `labsdbaccounts`.* TO `labsdbaccounts`@`208.80.154.85` |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.001 sec)

I don't know if you might need to open FW holes to reach m5-master.

Once you are ready to remove grants, please create a different task.
Reopen if you find issues!

Change 892898 had a related patch set uploaded (by Marostegui; author: Marostegui):

[operations/puppet@production] production-m5.sql.erb: New IP

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

Change 892898 merged by Marostegui:

[operations/puppet@production] production-m5.sql.erb: New IP

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

I just remembered that nowadays we do use dbproxy for m5, so you should be fine by simply connecting from cloudcontrol1005 as you'd do from labstore1004 through the proxies.
Tested it and it worked fine, so I have deleted the grant I added and it keeps working fine:

root@db1183.eqiad.wmnet[mysql]> drop user 'labsdbaccounts'@'208.80.154.85';
Query OK, 0 rows affected (0.001 sec)

root@db1183.eqiad.wmnet[mysql]>
root@cloudcontrol1005:~# mysql --skip-ssl -hm5-master.eqiad.wmnet -ulabsdbaccounts -p -P3306
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 32865784
Server version: 10.4.26-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql:labsdbaccounts@m5-master.eqiad.wmnet [(none)]>