Provision a labsdb useraccount that can be used to run replica-addusers.pl
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	yuvipanda
	Jul 1 2015, 6:37 PM

Description

So it turns out that the replica-addusers.pl script (https://github.com/wikimedia/operations-puppet/blob/3adbccf1b38a604bba991938d84349da52e7bad2/modules/labstore/files/replica-addusers.pl) responsible for creating user accounts and grants on the labsdb*** boxes depended on many unpuppetized things on labstore1001, among which is a mysql user / password combo that has permissions to create users and do grants - a root account basically.

Unfortunately this wasn't puppetized, and labstore1001 has been completely wiped, so I have no idea which account was being used. Either way, an account that can do nothing but create accounts and give them grants should be created and allowed login from labstore** hosts, and put in the private repository...

Related Objects
Search...

Status	Assigned	Task
Resolved	jcrespo	T140788 Labs databases rearchitecture (tracking)
		Restricted Task
		Restricted Task
Resolved	yuvipanda	T104453 replica.my.cnf creation broken
Resolved	coren	T104476 Provision a labsdb useraccount that can be used to run replica-addusers.pl

Event Timeline

yuvipanda created this task.Jul 1 2015, 6:37 PM

yuvipanda raised the priority of this task from to Needs Triage.

yuvipanda updated the task description. (Show Details)

yuvipanda added projects: Cloud-Services, DBA.

yuvipanda added subscribers: yuvipanda, jcrespo, coren.

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 1 2015, 6:37 PM

yuvipanda mentioned this in T104453: replica.my.cnf creation broken.Jul 1 2015, 6:39 PM

Ricordisamoa subscribed.Jul 1 2015, 6:45 PM

jeremyb subscribed.Jul 1 2015, 7:31 PM

Legoktm added a parent task: T104453: replica.my.cnf creation broken.Jul 2 2015, 7:23 AM

jcrespo triaged this task as Medium priority.Jul 2 2015, 7:47 AM

jcrespo moved this task from Triage to Backlog on the DBA board.

jcrespo set Security to None.

jcrespo moved this task from Backlog to In progress on the DBA board.Jul 2 2015, 8:04 AM

This SUPER user already exists and is already on the private repo (as the previous script worked). I will not put its name publicly, for obvious reasons.

However,

I will block you to continue until:

Grants are fixed: https://github.com/wikimedia/operations-puppet/blob/3adbccf1b38a604bba991938d84349da52e7bad2/modules/labstore/files/replica-addusers.pl#L129 as per T101758, so changes performed on that ticker are not undone.

Is labstore1001 accessible for non-ops? If yes, I would also highly suggest to avoid running the script from a server that end users can access, as that would give them permissions to become root just by guessing/obtaining the password.

@jcrespo nope, labstore1001/2 is not accessible to non-ops. I've fixed the grants escaping problem.

I've just created a 'labsdbadmin'@'10.64.37.6' user on mysql@labsdb100X with the minimum grants to execute for other users:

CREATE USER '${user_name}'@'%' IDENTIFIED BY '${user_pass}';
GRANT SELECT, SHOW VIEW ON `%\_p`.* TO '${user_name}'@'%';
GRANT ALL PRIVILEGES ON `${user_name}\_\_\%`.* TO '${user_name}'@'%';

(again, be careful to escape some of those characters on scripts, as I did on T101758).

I have put the password on the shared repository, will puppetize it next week, when there is more people around (otherwise I know I will break the private repo :-)).

jcrespo added a parent task: Restricted Task.Jul 6 2015, 7:27 PM

@jcrespo sweet! Have you managed to make a commit in the puppet repo yet?

I've just commited the new user/pass on the class "passwords::mysql::labsdb". I will be working with that user to identify and add which are the correct grants.

Roles cannot be implemented transparently in MariaDB 10.0, we either need to force user to execute SET ROLE 'replication'; before querying the databases or migrate to 10.1, which is not suitable for production. I would recommend for now doing the following:

Authenticate as the user labsadmin from dbstore1001
Execute the following for each new user:

CREATE USER '${user_name}'@'%' IDENTIFIED BY '${user_pass}';
GRANT SELECT, SHOW VIEW ON `%\_p`.* TO '${user_name}'@'%';
GRANT ALL PRIVILEGES ON `${user_name}\_\_%`.* TO '${user_name}'@'%';

Again, be careful, you may need to escape the \ and ` as seen here: T101758#1416580. The final grants for a user, on the command line should be:

MariaDB LABS localhost (none) > SHOW GRANTS FOR 'u100000000000000'@'%';
+-----------------------------------------------------------------------------------------------------------------+
| Grants for u100000000000000@%                                                                                   |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'u100000000000000'@'%' IDENTIFIED BY PASSWORD '*HASHED_PASSWORD' |
| GRANT ALL PRIVILEGES ON `u100000000000000\_\_%`.* TO 'u100000000000000'@'%'                                     |
| GRANT SELECT, SHOW VIEW ON `%\_p`.* TO 'u100000000000000'@'%'                                                   |
+-----------------------------------------------------------------------------------------------------------------+

Ideally, change '%' by a more strict subnetwork '10.10.10.%'

Obviously, feel free to argue about the grants provided or ask any question.

jcrespo moved this task from In progress to Done on the DBA board.Jul 7 2015, 10:46 AM

In T104476#1433541, @jcrespo wrote:

Obviously, feel free to argue about the grants provided or ask any question.

My only real question is: why not WITH GRANT OPTION? I know tools granting access to some of their databases to other tools has been used in the past (though perhaps rather infrequently), so I presume there is a security impact I am not entirely aware of?

Because as there are no roles, and no centralizing management of privileges, I now have to check individually the 8000 accounts for each server in order to fully solve any security issues, as each user can have an arbitrary number of extra privileges (aside from those assigned initially). T101758 is still ongoing and I am trying that it will not happen again.

Specific grants can be provided on request, given that they are documented.

Having no GRANT OPTION at least allow us to script the changes.

In T104476#1437995, @jcrespo wrote:

Having no GRANT OPTION at least allow us to script the changes.

Fair enough.

@jcrespo using these credentials from labstore1002 to try to grant grants ends up with:

Can\'t connect to MySQL server on \'labsdb1001.eqiad.wmnet\' ((1045, "Access denied for user \'labsdbadmin\'@\'10.64.37.7\' (using password: YES)

Krenair subscribed.Jul 28 2015, 1:40 AM

And if I use the root pw (to check rest of script), it fails at executing:

GRANT SELECT, SHOW VIEW ON `%\_p`.* TO 's52632'@'%';

with

Access denied for user 'root'@'10.%' to database '%\\_p'

Messing around, even:

mysql:root@labsdb1001.eqiad.wmnet [(none)]> GRANT SELECT, SHOW VIEW ON enwiki_p.* TO 's52632'@'%';
ERROR 1044 (42000): Access denied for user 'root'@'10.%' to database 'enwiki_p'

But

mysql:root@labsdb1001.eqiad.wmnet [(none)]> select * from enwiki_p.page limit 1;
+---------+----------------+---------------------+-------------------+--------------+------------------+-------------+---------------------+----------------+--------------------+-------------+----------+--------------------+
| page_id | page_namespace | page_title          | page_restrictions | page_counter | page_is_redirect | page_is_new | page_random         | page_touched   | page_links_updated | page_latest | page_len | page_content_model |
+---------+----------------+---------------------+-------------------+--------------+------------------+-------------+---------------------+----------------+--------------------+-------------+----------+--------------------+
|      10 |              0 | AccessibleComputing |                   |            0 |                1 |           0 | 0.33167112649574004 | 20150726140704 | 20150723081909     |   631144794 |       69 | wikitext           |
+---------+----------------+---------------------+-------------------+--------------+------------------+-------------+---------------------+----------------+--------------------+-------------+----------+--------------------+
1 row in set (0.01 sec)

And

mysql:root@labsdb1001.eqiad.wmnet [(none)]> show grants;
+----------------------------------------------------------------------------------------+
| Grants for root@10.%                                                                   |
+----------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.%' IDENTIFIED BY PASSWORD 'mohohohohohohohoho' |
+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Not really sure what's going on?

The actual SQL I need to execute for each user is:

CREATE USER 's52632'@'%' IDENTIFIED BY 'somepasswordhere';
GRANT SELECT, SHOW VIEW ON `%\_p`.* TO 's52632'@'%';
GRANT ALL PRIVILEGES ON `s52632\_\_%`.* TO 's52632'@'%';

At Yuvi's request on IRC I added 'labsdbadmin'@'10.64.37.7' with the same permissions/password as @jcrespo added for 'labsdbadmin'@'10.64.37.6', since the catastrophic failure of the latter.

Also added:

GRANT SELECT ON `mysql`.* TO 'labsdbadmin'@'10.64.37.7';

... as Yuvi's script is checking for user existence with manual SELECT (maybe this could become SHOW GRANTS FOR statement ?).

Also added both accounts to labsdb1005.

jcrespo moved this task from Done to Backlog on the DBA board.Sep 2 2015, 3:50 PM

This appears to be long fixed, and correspondingly has had no activity in months. Reopen if needed.

• Phabricator_maintenance removed a subscriber: yuvipanda.Jun 7 2017, 6:49 PM

Provision a labsdb useraccount that can be used to run replica-addusers.plClosed, ResolvedPublicActions

Description

Related ObjectsSearch...

Event Timeline

Provision a labsdb useraccount that can be used to run replica-addusers.pl
Closed, ResolvedPublic
Actions

Related Objects
Search...