Page MenuHomePhabricator

Additional database user for XHGui administration
Closed, ResolvedPublic

Description

In T254795, I only requested a single user for XHGui, based on the permissions needed by the tool itself.

After going live, it's become apparent that we would benefit from an additional user, with some broader permissions, for debugging and manual tasks like schema upgrades and fixing corrupt data.

We'd thus like to add an 'xhguiadmin' user, with ALTER, CREATE, DELETE, INSERT, SELECT, and UPDATE privileges. (If ALTER is dangerous wrt replication, you can omit it, and we'll request help from a DBA if we ever need to drop/modify columns in the future.)

From the existing 'xhgui' user, you can remove CREATE (it was only needed initially) and DELETE (we've disabled the ability to delete profiles in our environment).

Please allow connections as this user from the same hosts as 'xhgui'.

You can assign it a random password.

Related Objects

Event Timeline

dpifke created this task.Aug 18 2020, 1:05 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 18 2020, 1:05 AM
dpifke triaged this task as Low priority.Aug 18 2020, 1:06 AM

This is not urgent; we can make due at the moment by logging in as 'xhgui'.

Marostegui moved this task from Triage to Next on the DBA board.Aug 18 2020, 7:22 AM
Marostegui added a subscriber: Marostegui.

I think it should be fine to give ALTER, most of the applications that live on misc, have users that have it.
However, it would be good to get a heads up anytime you're going to perform an alter table and/or run an application upgrade that might trigger it.

Change 620930 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m2.sql: Remove CREATE and DELETE from xhgui

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

Change 620930 merged by Marostegui:
[operations/puppet@production] production-m2.sql: Remove CREATE and DELETE from xhgui

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

Mentioned in SAL (#wikimedia-operations) [2020-08-18T13:54:54Z] <marostegui> Revoke DELETE and CREATE from xhgui user on m2 T260640

First part of the task (revoking grants) done:

# ./section m2 | while read host port; do echo "==== $host:$port ===="; mysql.py -h$host:$port -e "show grants for 'xhgui'@'10.192.16.9'; show grants for 'xhgui'@'10.64.0.135'; show grants for 'xhgui'@'10.64.16.19';" | grep -v PASS ; done
==== db2133.codfw.wmnet:3306 ====
Grants for xhgui@10.192.16.9
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.192.16.9`
Grants for xhgui@10.64.0.135
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.64.0.135`
Grants for xhgui@10.64.16.19
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.64.16.19`
==== db2078.codfw.wmnet:3322 ====
Grants for xhgui@10.192.16.9
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.192.16.9`
Grants for xhgui@10.64.0.135
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.64.0.135`
Grants for xhgui@10.64.16.19
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.64.16.19`
==== db1117.eqiad.wmnet:3322 ====
Grants for xhgui@10.192.16.9
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.192.16.9`
Grants for xhgui@10.64.0.135
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.64.0.135`
Grants for xhgui@10.64.16.19
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.64.16.19`
==== db1107.eqiad.wmnet:3306 ====
Grants for xhgui@10.192.16.9
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.192.16.9`
Grants for xhgui@10.64.0.135
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.64.0.135`
Grants for xhgui@10.64.16.19
GRANT SELECT, INSERT ON `xhgui`.* TO `xhgui`@`10.64.16.19`

Change 621100 had a related patch set uploaded (by Dave Pifke; owner: Dave Pifke):
[operations/puppet@production] xhgui: enable database access for admins

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

Change 621985 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] production-m2.sql: Add xhguiadmin user

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

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

Change 621985 merged by Marostegui:
[operations/puppet@production] production-m2.sql: Add xhguiadmin user

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

Marostegui moved this task from In progress to Done on the DBA board.Aug 24 2020, 9:32 AM

The new user has been created:

db2133.codfw.wmnet:3306
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.192.16.9`
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.64.0.135`
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.64.16.19`
db2078.codfw.wmnet:3322
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.192.16.9`
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.64.0.135`
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.64.16.19`
db1117.eqiad.wmnet:3322
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.192.16.9`
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.64.0.135`
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.64.16.19`
db1107.eqiad.wmnet:3306
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.192.16.9`
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.64.0.135`
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER ON `xhgui`.* TO `xhguiadmin`@`10.64.16.19`

The password is at the private repo under hieradata/role/common/webperf/xhgui.yaml

profile::webperf::xhgui::mysql_admin_password: 'REDACTED'

Please test it and let me know if everything works as expectred

dpifke moved this task from Inbox to Doing on the Performance-Team board.Aug 24 2020, 6:24 PM
Dzahn added a subscriber: Dzahn.Aug 26 2020, 7:47 PM

@dpifke all good from your side? Can this be resolved?

Marostegui closed this task as Resolved.Wed, Sep 16, 2:36 PM

I am going to consider this resolved - please reopen if you need something else
Thanks!