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.

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

@Marostegui Sounds good, thanks

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.

$ 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 :)

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

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

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