Page MenuHomePhabricator

Drop 'designate_pool_manager' database from m5 and remove associated grants
Closed, ResolvedPublic

Description

This database is no longer used by our designate deployment. Note that the 'designate' database IS still used and important.

Event Timeline

Andrew created this task.Thu, Sep 26, 6:20 PM
Marostegui triaged this task as Normal priority.Fri, Sep 27, 10:42 AM
Marostegui moved this task from Triage to Backlog on the DBA board.
Andrew removed Andrew as the assignee of this task.Fri, Sep 27, 2:03 PM

Change 539839 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] dumps-misc.sh.erb: Remove designate_pool_manager from backups

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

Change 539839 merged by Marostegui:
[operations/puppet@production] dumps-misc.sh.erb: Remove designate_pool_manager from backups

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

Marostegui moved this task from Backlog to In progress on the DBA board.

Mentioned in SAL (#wikimedia-operations) [2019-10-03T06:37:56Z] <marostegui> Rename tables on m5 master on designate_pool_manager - T233978

I have renamed the tables, will leave them like this for a few days to make sure nothing breaks

+----------------------------------+
| Tables_in_designate_pool_manager |
+----------------------------------+
| TO_DROP_migrate_version          |
| TO_DROP_pool_manager_statuses    |
+----------------------------------+

@Andrew let me know if you see something on your end.

Change 540534 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m5.sql.erb: Remove grants from designate_pool_manager

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

Apart from the grants at: https://gerrit.wikimedia.org/r/#/c/operations/puppet/+/540534/1/modules/role/templates/mariadb/grants/production-m5.sql.erb

The following need to be also dropped from the DB:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES ON `designate\\_pool\\_manager`.* TO 'designate'@'208.80.154.12'
208.80.154.135
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES ON `designate\\_pool\\_manager`.* TO 'designate'@'208.80.154.135'
208.80.155.117
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES ON `designate\\_pool\\_manager`.* TO 'designate'@'208.80.155.117'

Change 540609 had a related patch set uploaded (by Andrew Bogott; owner: Andrew Bogott):
[operations/puppet@production] designate: remove mitaka files and templates and a mitaka-specific arg

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

Change 540609 merged by Andrew Bogott:
[operations/puppet@production] designate: remove mitaka files and templates and a mitaka-specific arg

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

Change 540534 merged by Marostegui:
[operations/puppet@production] production-m5.sql.erb: Remove grants from designate_pool_manager

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

Mentioned in SAL (#wikimedia-operations) [2019-10-10T06:33:31Z] <marostegui> Revoke privileges from designate user on the designate_pool_manager database - T233978

Grants removed for the designate_pool_manager DB:

root@db1133.eqiad.wmnet[(none)]> show grants for 'designate'@'208.80.154.12';
+------------------------------------------------------------------------------------------------------------------------------------+
| Grants for designate@208.80.154.12                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'designate'@'208.80.154.12' IDENTIFIED BY PASSWORD '*x'               |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES ON `designate`.* TO 'designate'@'208.80.154.12' |
| GRANT INDEX ON `designate`.`recordsets` TO 'designate'@'208.80.154.12'                                                             |
| GRANT INDEX ON `designate`.`domains` TO 'designate'@'208.80.154.12'                                                                |
| GRANT INDEX ON `designate`.`records` TO 'designate'@'208.80.154.12'                                                                |
+------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

root@db1133.eqiad.wmnet[(none)]> show grants for 'designate'@'208.80.154.135';
+-------------------------------------------------------------------------------------------------------------------------------------+
| Grants for designate@208.80.154.135                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'designate'@'208.80.154.135' IDENTIFIED BY PASSWORD '*x'               |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, CREATE TEMPORARY TABLES ON `designate`.* TO 'designate'@'208.80.154.135' |
+-------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@db1133.eqiad.wmnet[(none)]> show grants for 'designate'@'208.80.155.117';
+--------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for designate@208.80.155.117                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'designate'@'208.80.155.117' IDENTIFIED BY PASSWORD '*x'                      |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES ON `designate`.* TO 'designate'@'208.80.155.117' |
+--------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Mentioned in SAL (#wikimedia-operations) [2019-10-10T06:45:40Z] <marostegui> Drop designate_pool_manager database from m5 - T233978

Marostegui closed this task as Resolved.Thu, Oct 10, 6:46 AM

I have dropped the database.
It didn't have much data:

mysql.py -hdb1117:3325 designate_pool_manager -e "select count(*) from migrate_version; select count(*) from pool_manager_statuses"
+----------+
| count(*) |
+----------+
|        1 |
+----------+
+----------+
| count(*) |
+----------+
|       12 |
+----------+

However, I have taken a temporary mysqldump:

root@cumin1001:/home/marostegui/T233978# pwd
/home/marostegui/T233978
root@cumin1001:/home/marostegui/T233978# ls -lh
total 8.0K
-rw-r--r-- 1 root root 5.0K Oct 10 06:42 designate_pool_manager.sql