Page MenuHomePhabricator

Deploy access to performance_schema/sys for the administrative mediawiki account (mediawiki deployers)
Open, MediumPublic

Description

Performance schema statistics were not deployed to public prometheus/grafana because of privacy reasons (it could contain sensitive information AND could reveal user information for wikis with low load, we were told). The alternative was going to be to deploy and aggregate that to tendril or to a private prometheus instance, but that is not that easy/quick.

For short term, we realized all deployers did not have access to that (only to wiki dbs/those with root access). Please check performance schema/sys utility metrics on each database, see if they are helpful, and if there is something exposed that shouldn't be to deployers. It is available on the production, but not pooled with active use db2083. If everythings is correct, we will provide access to all mediawiki servers to all deployers.

Log in to db2083 (sql wikidatawiki -h db2083, use sys), and query some starts/query digest/index stats.

I have added some people to this ticket that may be interested on this- but I require no actionable from you (except test it/break it), also feel free to unubscribe if this is not interesting for you.

For a quick documentation, check my mediawiki query optimization slides at: https://www.slideshare.net/jynus/query-optimization-from-0-to-10-and-up-to-57/158 / https://github.com/jynus/query-optimization

For Security-Team, the request is:

we want your ok to expose low level internal mw database performance metrics to mw deployers for debugging purposes, through the existing mediawiki admin database account. Think of logs, but real time and database layer. Metrics will be accessed, for now, through the SQL interface only. There is a lot of metrics involved (literally billions of them) but it includes file, thread, user, transaction, query and query digest, and indexes individual metrics and counters (aggregations). Currently that access is reserved for mysql root users only.

For DBA s, the actual changes to be done are:

grants
set sql_log_bin=0;
GRANT SELECT, EXECUTE ON `sys`.* TO <mw_admin_account>;
GRANT SELECT ON `performance_schema`.* TO <mw_admin_account>;

on all mw core dbs.

Event Timeline

jcrespo created this task.May 25 2018, 12:18 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 25 2018, 12:18 PM
jcrespo triaged this task as Medium priority.May 25 2018, 12:18 PM
jcrespo moved this task from Triage to In progress on the DBA board.
Marostegui moved this task from In progress to Next on the DBA board.Jun 29 2018, 2:19 PM
Vvjjkkii renamed this task from Deploy access to performance_schema/sys for the administrative mediawiki account (mediawiki deployers) to lacaaaaaaa.Jul 1 2018, 1:08 AM
Vvjjkkii raised the priority of this task from Medium to High.
Vvjjkkii updated the task description. (Show Details)
Vvjjkkii removed a subscriber: Aklapper.
Marostegui renamed this task from lacaaaaaaa to Deploy access to performance_schema/sys for the administrative mediawiki account (mediawiki deployers).Jul 1 2018, 6:59 PM
Marostegui lowered the priority of this task from High to Medium.
Marostegui updated the task description. (Show Details)
jcrespo added subscribers: Reedy, Bawolff, JBennett.

@Security-team Do you see any blocker or reason not to enable this on all hosts? This was already available for roots only, and now it will be extended to the admin account (deployers with mediawiki database access). It contains performance information similar to that of tendril, but more detailed. Please have a look at it on db2083 yourselves if on doubt.

If no blocker, I will enable this on all mediawiki db hosts. If you are unsure, I will wait for your feedback.

Tgr added a subscriber: Tgr.Jul 16 2018, 8:55 AM

Security any comments about ^

Ladsgroup added a subscriber: Ladsgroup.

Having something like this would be extremely useful for me. I have been looking for something like this for a while now.

I think this should be tagged with the security team. The Security tag is very general.

@jcrespo @Marostegui @Ladsgroup -

Sorry for the ridiculous delay here. As @Ladsgroup noted above, adding Security-Team to a task should now get said task within our team's weekly triage/review process. For this task, given its age, is it still relevant? Has the work already been completed? If it's still being blocked on a risk analysis from the Security-Team, could we formalize the request a bit more so we can better understand the scope and get a review scheduled? Thanks.

sbassett moved this task from Incoming to Back Orders on the Security-Team board.Feb 10 2020, 4:14 PM
jcrespo added a comment.EditedFeb 10 2020, 4:16 PM

For this task, given its age, is it still relevant?

This is still very relevant. Let me know what process is needed, other than a deeper description of the task, as the new requests formats didn't exist back when this query was first created.

For starters, does anyone on your team have access to performance_schema/sys on production databases, or on a test host?

@jcrespo -

Just confirming this is the specific ask of the Security-Team:

Please check performance schema/sys utility metrics on each database, see if they are helpful, and if there is something exposed that shouldn't be to deployers ... If everythings is correct, we will provide access to all mediawiki servers to all deployers.

I'm not certain that members of the Security-Team have easy access to do this, so we might require some assistance and perhaps some further definition of what, specifically, we should review. Thanks.

jcrespo added a comment.EditedFeb 10 2020, 5:11 PM

We don't need Security-Team feedback in terms of usefulness, that part of the ticket was for general deployers to comment if they found it helpful as to enable it on all hosts.

For Security-Team, the request is:

we want your ok to expose low level internal mw database performance metrics to mw deployers for debugging purposes, through the existing mediawiki admin database account. Think of logs, but real time and database layer. Metrics will be accessed, for now, through the SQL interface only. There is a lot of metrics involved (literally billions of them) but it includes file, thread, user, transaction, query and query digest, and indexes individual metrics and counters (aggregations). Currently that access is reserved for mysql root users only.

It is my fault for mixing the "user feedback" with the "security ok/heads up".

I 100% understand you may lack lots of context here, that is why I am asking what would be the requirement for oking this ? For example,

  • Access to an example database (e.g. "Log in to db2083 (sql wikidatawiki -h db2083, use sys), and query some starts/query digest/index stats.") for playing around?
  • Documentation of every metric exposed?
  • Delegate who makes the call on what is the appropriate level of access on any other team (DBAs or Releng) because this is out of your scope/area of expertise?

What we don't want is to proceed with this without at least you being aware.

chasemp moved this task from Back Orders to Waiting on the Security-Team board.
chasemp added a subscriber: chasemp.

@jcrespo thanks for explaining, I'll get this on our agenda for this week. If there is some big unforseen concern it may need another week to germinate as @JBennett is mostly occupied till then.

Just to be clear, not precisely a #1 priority thing, we didn't make much noise about this for a reason... but this got unearthed lately after a couple of performance-related requests from mw maintainers, as it could help with as a self-service model rather than pinging DBAs everytime. :-D

Feel free to ask for anything- access, info, etc.

Krinkle removed a subscriber: Krinkle.Feb 10 2020, 11:41 PM

Can I at least have it temporarily? I need lots of information for doing T246415: Investigate a different db load groups for wikidata / wikibase

Security never came back with comments, and we gave them a full year to do so, even insisting by several methods/people. At this point, from my point of view, if @Marostegui is cool with it, I would give you full access to p_s/sys- as long as fine-grained access statistics are not exposed, specially of small wikis, which is why this cannot be fully public in the first place.

I think it is ok to give access to @Ladsgroup (NDA signed, full access to our MW databases...) but as you said, let's not expose that data.
Thanks Jaime! (If you have time, can you document the procedure of giving access to individuals so we have written it somewhere?).

@Marostegui Allow me to deploy for now only the access he needs (wikidata) to understand what that would be (and use him to check he can access properly), and I will document here what's an appropiate deployment procedure for all dbs.

@Marostegui Allow me to deploy for now only the access he needs (wikidata) to understand what that would be (and use him to check he can access properly), and I will document here what's an appropiate deployment procedure for all dbs.

+1 thank you

chasemp moved this task from Waiting to Incoming on the Security-Team board.Thu, Sep 17, 2:39 PM

Security-Team dropped the ball here on feedback. This is a bit of one-off, and I'll feed it back into our queue so it gets attention, but I support @jaime and @Marostegui in their practicality. Thanks gents.

Mentioned in SAL (#wikimedia-operations) [2020-09-17T15:02:22Z] <jynus> deploying extended grants for admin account on sys/p_s at s8@codfw T195578

To unblock @Ladsgroup, and so he can serve as "beta tester", I have added to the following hosts (s8 mw-pooled instances on codfw):

list of instances
db2079
db2080
db2081
db2082
db2083
db2084
db2085:3318
db2086:3318
db2091

The following grants:

grants
set sql_log_bin=0;
GRANT SELECT, EXECUTE ON `sys`.* TO <mw_admin_account>;
GRANT SELECT ON `performance_schema`.* TO <mw_admin_account>;

So only reads can be done on the profiling engine.

Once he confirms it works ok (there could be some issues regarding needing additional privileges -SUPER or PROCESSLIST- for some functions, see https://fromdual.com/mysql-performance-schema-hints#grant-ps-schema-access ), we can add this to the grants on puppet and thinking about deploying it cluster-wide.

Complete usage manual is at: https://dev.mysql.com/doc/refman/5.7/en/sys-schema-object-index.html

jcrespo updated the task description. (Show Details)Thu, Sep 17, 3:31 PM

To unblock @Ladsgroup, and so he can serve as "beta tester", I have added to the following hosts (s8 mw-pooled instances on codfw):

list of instances
db2079
db2080
db2081
db2082
db2083
db2084
db2085:3318
db2086:3318
db2091

The following grants:

grants
set sql_log_bin=0;
GRANT SELECT, EXECUTE ON `sys`.* TO <mw_admin_account>;
GRANT SELECT ON `performance_schema`.* TO <mw_admin_account>;

So only reads can be done on the profiling engine.

Once he confirms it works ok (there could be some issues regarding needing additional privileges -SUPER or PROCESSLIST- for some functions, see https://fromdual.com/mysql-performance-schema-hints#grant-ps-schema-access ), we can add this to the grants on puppet and thinking about deploying it cluster-wide.

Complete usage manual is at: https://dev.mysql.com/doc/refman/5.7/en/sys-schema-object-index.html

Thank you for handling this.

Thank you for the right, it was extremely useful to me, I wrote a detailed report on the load of s8 thanks to this right on T246415#6474175 onwards

sbassett edited projects, added Privacy Engineering, WMF-Legal; removed Security-Team.