Page MenuHomePhabricator
Create Task
Maniphest T330697

labsdbaccounts database grant for cloudcontrol1005
Closed, ResolvedPublic
Actions

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.

Details

Related Changes in Gerrit:
SubjectAuthorRepoBranchLines +/-
production-m5.sql.erb: New IPMarosteguioperations/puppetproduction+3 -0
Customize query in gerrit

Related Objects
Search...

Event Timeline

Raymond_Ndibe created this task.Feb 27 2023, 7:55 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 27 2023, 7:55 PM
Raymond_Ndibe updated the task description. (Show Details)Feb 27 2023, 7:56 PM
Raymond_Ndibe added a subscriber: dcaro.
Raymond_Ndibe added a comment.Feb 27 2023, 7:59 PM

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

Marostegui subscribed.Feb 28 2023, 8:55 AM

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 claimed this task.Feb 28 2023, 8:56 AM
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`
dcaro added a comment.Feb 28 2023, 9:09 AM

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).

dcaro added a parent task: T303663: Split maintain-dbusers.py into two parts, one to run on cloudcontrol nodes and one to run on an NFS server VM.Feb 28 2023, 9:10 AM
Marostegui closed this task as Resolved.Feb 28 2023, 9:21 AM

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!

gerritbot added a comment.Feb 28 2023, 9:25 AM

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

gerritbot added a project: Patch-For-Review.Feb 28 2023, 9:25 AM
Maintenance_bot moved this task from In progress to Done on the DBA board.Feb 28 2023, 9:29 AM
gerritbot added a comment.Feb 28 2023, 9:30 AM

Change 892898 merged by Marostegui:

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

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

Maintenance_bot removed a project: Patch-For-Review.Feb 28 2023, 9:30 AM
Marostegui added a comment.Feb 28 2023, 9:32 AM

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)]>
dcaro added a comment.Feb 28 2023, 9:49 AM

Thanks!

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits