Page MenuHomePhabricator

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

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

Event Timeline

yuvipanda raised the priority of this task from to Needs Triage.
yuvipanda updated the task description. (Show Details)
yuvipanda added projects: Cloud-Services, DBA.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJul 1 2015, 6:37 PM
jeremyb added a subscriber: jeremyb.Jul 1 2015, 7:31 PM
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:

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

jcrespo added a comment.EditedJul 3 2015, 5:04 PM

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
coren added a comment.Jul 8 2015, 3:20 PM

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.

coren added a comment.Jul 8 2015, 3:59 PM

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)

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
coren closed this task as Resolved.Dec 1 2015, 4:20 PM
coren claimed this task.

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