Page MenuHomePhabricator

Access to raw database tables on labsdb* for wmcs-admin users
Closed, ResolvedPublic

Description

In T166310: Grant root access for Bryan Davis on labstore* and admin for maintain scripts for labsdb* the original ask was for me to get full sudo rights on labsdb*. After discussion on that task about the implications of labsdb root the request was modified to create a wmcs-adminuser group and grant that group limited sudo access to run some maintenance scripts on labsdb hosts.

As time goes on I am finding that not have a mysql user login capable of at least seeing the raw tables on the labsdb servers greatly limits the Data-Services tasks I can work on. The only way that I can interact with the databases on these hosts is via normal Toolforge replica.my.cnf credentials. This makes working on tickets like T173891: Create core ip_changes view for replicas difficult and working on others like T177223: Determine schema differences between labsdb1001 and labsdb1009 functionally impossible. Even more frustrating is that I, and likely anyone else who will end up in the wmcs-admin group, have access to the full data in most of these databases via /usr/local/bin/sql $wikidb from tin/terbium/etc.

Can we add some shared role or per-user account(s) on the labsdb mysql instances that will at least allow read-only queries of the raw tables? I'd like to be able to see:

  • raw table structure
  • raw table data
  • indexes
  • views
  • grants for other users

Even without being able to directly change any of these things, having the ability to see the actual state of the systems will make it much easier for me to triage and fix a larger class of bugs.

Event Timeline

bd808 created this task.Oct 12 2017, 11:17 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 12 2017, 11:17 PM
bd808 moved this task from Backlog to Kanban on the cloud-services-team board.Oct 13 2017, 12:00 AM
bd808 edited projects, added cloud-services-team (Kanban); removed cloud-services-team.
bd808 moved this task from Inbox to Needs discussion on the cloud-services-team (Kanban) board.
jcrespo added a subscriber: jcrespo.EditedOct 16 2017, 1:36 PM

This is a problem created by cloud people themselves. I told them to create the view user with localhost permissions, and the labs-admin user with remote (but specific ips) permissions; it was done in reverse. Having a remote admin user will be easier and will not require root privileges on the db hosts.

To be fair, things were done in a rush back then.

chasemp triaged this task as Normal priority.Oct 17 2017, 4:15 PM
chasemp assigned this task to madhuvishy.
chasemp added subscribers: madhuvishy, chasemp.

@madhuvishy is going to take a tour here and document from our end what we need to do (and maybe do it?) since she has handled labsdb account creation and such.

madhuvishy added a comment.EditedOct 25 2017, 9:14 PM

@bd808 I looked at the accounts set up we have now, and it looks like the labsdbadmin user is already set up with remote (specific ips) permissions, but it only has Grant_priv and Create_user_priv, which in turn we use to Create accounts and grant View privileges for toolforge users/tool accounts.

We can make an admin role and grant it more privileges that we need and apply the role to the labsdbadmin user in the new servers, and directly apply the additional privileges in the older servers (no role mechanism available in the legacy servers).

I am happy to set this up, but not sure what extra privileges we need to grant, full list being

Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv

(Pinging @Marostegui since Jaime is on vacation)

Not sure I am getting your last point right, @madhuvishy, taking what  @bd808 wrote on the original task description regarding the grants he'd need, if we add the following to labsdbadmin we should be good to go:

GRANT SELECT, SHOW VIEW ON `%wik%`.* TO 'labsdbadmin'@'$specific_IP'

From the list Bryan gave, that user can already see:

  • views
  • grants for other users (by doing SELECT directly to the mysql database)

What it is missing is being able to check data on non "_p" databases.
Let me know if I am misunderstanding you, which is probably the case! :-)

@Marostegui Yeah that sounds right to me! Cool if I run that across the wiki replicas?

Maybe only labsdb1010 (sby host) and check that it does what is required and needed.

Cool, I've run

GRANT SELECT, SHOW VIEW ON `%wik%`.* TO 'labsdbadmin'@'10.64.37.19';
GRANT SELECT, SHOW VIEW ON `%wik%`.* TO 'labsdbadmin'@'10.64.37.20';

In labsdb1010. @bd808 has confirmed he can access all the data he needs. Now going to roll out to the other replica hosts.

bd808 added a comment.Oct 27 2017, 5:43 PM
$ ssh labstore1004.eqiad.wmnet
$ mysql -h labsdb1010.eqiad.wmnet -u labsdbadmin -p
Enter password:
(labsdbadmin@labsdb1010.eqiad.wmnet) [(none)]> use enwiki;

(labsdbadmin@labsdb1010.eqiad.wmnet) [enwiki]> show create table user\G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `user_id` int(5) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varbinary(255) NOT NULL DEFAULT '',
  `user_real_name` varbinary(255) NOT NULL DEFAULT '',
  `user_password` tinyblob NOT NULL,
  `user_newpassword` tinyblob NOT NULL,
  `user_email` tinyblob NOT NULL,
  `user_options` blob NOT NULL,
  `user_touched` varbinary(14) NOT NULL DEFAULT '',
  `user_token` varbinary(32) NOT NULL DEFAULT '',
  `user_email_authenticated` varbinary(14) DEFAULT NULL,
  `user_email_token` varbinary(32) DEFAULT NULL,
  `user_email_token_expires` varbinary(14) DEFAULT NULL,
  `user_registration` varbinary(14) DEFAULT NULL,
  `user_newpass_time` varbinary(14) DEFAULT NULL,
  `user_editcount` int(11) DEFAULT NULL,
  `user_password_expires` varbinary(14) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  KEY `user_email_token` (`user_email_token`),
  KEY `user_email` (`user_email`(50))
) ENGINE=InnoDB AUTO_INCREMENT=32252305 DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
1 row in set (0.00 sec)

(labsdbadmin@labsdb1010.eqiad.wmnet) [enwiki]> show grants for u3518\G
*************************** 1. row ***************************
Grants for u3518@%: GRANT labsdbuser TO 'u3518'@'%'
*************************** 2. row ***************************
Grants for u3518@%: GRANT USAGE ON *.* TO 'u3518'@'%' IDENTIFIED BY PASSWORD '*****' WITH MAX_USER_CONNECTIONS 10
2 rows in set (0.00 sec)

I think this gets me what I need. :)

I've now rolled this out to labsdb10[01|03|09|10|11]. @Marostegui Is there a file/config/logs somewhere you'd like me to persist these grants? Thanks for your help :)

Marostegui added a comment.EditedOct 27 2017, 6:17 PM

I don't think we are tracking the GRANTS for labs anywhere in puppet, but I cannot check now (I am with my phone).
Maybe we should create a .sql file like: modules/role/files/labs/db/views/extra-wikireplicas-only-indexes.sql which will not be applied or anything by puppet, but more as a tracking of which grants we have.
Would you mind creating one? Of course without any passwords or hashes.

Thanks!

Change 387214 had a related patch set uploaded (by Marostegui; owner: Marostegui):
[operations/puppet@production] wiki-replicas.sql: New file just to track GRANTS

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

@madhuvishy please review this: https://gerrit.wikimedia.org/r/#/c/387214/ and if it looks good, just feel free to merge and close this ticket

Marostegui moved this task from Triage to In progress on the DBA board.Oct 30 2017, 11:09 AM

Change 387214 merged by Marostegui:
[operations/puppet@production] wiki-replicas.sql: New file just to track GRANTS

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

madhuvishy closed this task as Resolved.Oct 30 2017, 1:27 PM