Page MenuHomePhabricator

Migrate existing labs users from the old servers, if possible using roles and start maintaining users on the new database servers, too
Closed, ResolvedPublic

Details

Related Gerrit Patches:

Event Timeline

jcrespo created this task.Nov 3 2016, 6:17 PM

I was in a position this week to work through a few issues with create-dbusers and I came away with a few thoughts here. The current mechanism is meant to manage consistency for a user and their password across two mediums:

  • actual labsdb instances in service
  • replica.my.cnf file in the $home directory in question

At the moment passwords are generated adhoc on discovery from LDAP at an interval (currently 300s). There is a loop through each Labsdb host for account creation and then logic to dump the info to the file and after that no state is kept by the create-dbuser script. The fallout from this is that once a user is created in the labsdb system the script will ignore it even if a replica.my.cnf file has never been created. Another consequence is the check logic for whether a user exists in the Labsdb systems seems to be irregular i.e. if it exists in 1 labsdb system then skip it. There is no update password logic, and there is no native handling of broken accounts in other ways. This is not idempotent. Also create-dbusers in no way handles adding a new Labsdb hosts to the cluster. Since there is no canonical store for credentials, there is no established way to seed a Labsdb host from nothing. If an issue affected both the remaining two servers in the cluster it would require a regeneration of every tools credentials (logic for which would have to be written) and a restart of every service to accommodate.

I have patched the existing script to run on the new NFS cluster an issue which surfaced in T150946, but in https://gerrit.wikimedia.org/r/#/c/322298/ I note that the above are existing issues. T140832 is on the table but I think we are going to be forced into putting a bit of work into a transitional model.

My thinking atm on changes to create-dbusers:

  • Make it the canonical store for tool credentials (on disk permission protected storage on the labstore host itself ...for now). To do this I am hoping I can mine the replica.my.cnf files in existence now.
  • Add a --force option to wipe and recreate user accounts in a bad state (account only exists on some labsdb host, replica.my.cnf is not present, password needs to be changed etc
  • Change the permissions logic within create-dbusers to reflect roles if that if that proves viable

This should allow us to easily change up permission models (roles, whatever) and allow for actual consistency across backends.

I think what this boils down to is: if you guys DBA's can verify the roles permission scheme is viable I will attempt to work it out on our side so that seeding a new box is possible.

Yes, only 2 comments-

  • Try to avoid looping over created accounts. MySQL account storage is not optimized for large number of accounts; it is not a problem to store those, but going over every single of them is probably not a very efficient operation

Make it the canonical store for tool credentials

  • Have you considered MySQL as a backed to store an inventory of credentials? It will work better than a file, and we were thinking of doing that already for all production accounts. A table of accounts will be very efficient to store and query, and it is already being used for the tools management application.
Marostegui added a comment.EditedNov 21 2016, 3:19 PM

Make it the canonical store for tool credentials

  • Have you considered MySQL as a backed to store an inventory of credentials? It will work better than a file, and we were thinking of doing that already for all production accounts. A table of accounts will be very efficient to store and query, and it is already being used for the tools management application.

+1
This would simplify a lot the management of those accounts + it will be easier to migrate in the future if it is needed.
Keep in mind that we can always export those records to a file if needed, so you won't lose that "functionality" .

That would probably be best as the first question in dealing w/ a static file is keeping state in sync between (NFSd) nodes in the cluster. I haven't worked all this out yet, I mainly wanted to persist where I was currently :)

I have been testing the roles, they work as advertised:

mysql -u u2029 -p$PASS
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 55298
Server version: 10.1.19-MariaDB MariaDB Server

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW GRANTS;
+------------------------------------------------------------------------------------------------------+
| Grants for u2029@%                                                                                   |
+------------------------------------------------------------------------------------------------------+
| GRANT labsdbuser TO 'u2029'@'%'                                                                      |
| GRANT USAGE ON *.* TO 'u2029'@'%' IDENTIFIED BY PASSWORD '<redacted>' |
| GRANT ALL PRIVILEGES ON `u2029\_\_%`.* TO 'u2029'@'%'                                                |
| GRANT USAGE ON *.* TO 'labsdbuser'                                                                   |
| GRANT SELECT, SHOW VIEW ON `enwiki\_p`.* TO 'labsdbuser'                                             |
+------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

MariaDB [(none)]> CREATE DATABASE u2029_test;
ERROR 1044 (42000): Access denied for user 'u2029'@'%' to database 'u2029_test'
MariaDB [(none)]> CREATE DATABASE u2029__test;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> SELECT * FROM enwiki_p.revision;
+--------+----+
| rev_id | ip |
+--------+----+
|      1 |    |
+--------+----+
1 row in set (0.00 sec)

MariaDB [(none)]> SHOW CREATE VIEW enwiki_p.revision;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View     | Create View                                                                                                                                                                                | character_set_client | collation_connection |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| revision | CREATE ALGORITHM=UNDEFINED DEFINER=`viewmaster`@`%` SQL SECURITY DEFINER VIEW `enwiki_p`.`revision` AS select `enwiki`.`revision`.`rev_id` AS `rev_id`,'' AS `ip` from `enwiki`.`revision` | binary               | binary               |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> DROP enwiki_p.revision;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'enwiki_p.revision' at line 1
MariaDB [(none)]> SELECT * FROM enwiki.revision;
ERROR 1142 (42000): SELECT command denied to user 'u2029'@'localhost' for table 'revision'
MariaDB [(none)]>

@chasemp @yuvipanda @Marostegui With the above, the changes to permission handling are as follow:

On creating a new user (labsdbadmin script), we do:

  • Creating the user as usual, with CREATE USER giving it a user name and a password
  • Granting write privileges GRANT ALL PRIVILEGES ON `<username>\_\_%`.* TO '<username>'@'%' it would be nice if this was done only on demand, but that is not part of the goal. This can be omitted first, you know the issues with writing to replica databases.
  • Granting labsdbuser role: GRANT labsdbuser TO '<username>'@'%'
  • Setting the role as given by default, so the user doesn't have to change any code (10.1 specific part): SET DEFAULT ROLE labsdbuser FOR '<username>'@'%';

On deleting (or renaming) a user, we can just DROP USER '<username>'@'%' as usual.

On adding/removing wikis to labsdb (maintain-views)- I want to control the wikis as a whitelist to prevent accidental grants, so instead of having a static GRANT SELECT, SHOW VIEW on %\_p.* TO labsdbuser;, I would like to maintain that on a per wiki:

  • Adding a new wiki: GRANT SELECT, SHOW VIEW on <wiki>\_p.* TO labsdbuser
  • Deleting/making private a wiki: REVOKE SELECT, SHOW VIEW on <wiki>\_p.* FROM labsdbuser

labsdbuser centralizes the grants:

MariaDB [(none)]> SHOW GRANTS FOR labsdbuser;
+----------------------------------------------------------+
| Grants for labsdbuser                                    |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'labsdbuser'                       |
| GRANT SELECT, SHOW VIEW ON `enwiki\_p`.* TO 'labsdbuser' |
+----------------------------------------------------------+
2 rows in set (0.00 sec)

so there is one grant per wiki, but you cannot authenticate as it (it is a role, not a user):

MariaDB [(none)]> Bye
root@labsdb1009:~$ mysql -u labsdbuser
ERROR 1045 (28000): Access denied for user 'labsdbuser'@'localhost' (using password: NO)

@jcrespo how do you feel about setting limits via http://dev.mysql.com/doc/refman/5.7/en/user-resources.html for users from the start?

Yes, I wanted to set something like 10 max connections for the web request access point with 300 second execution, and 4 hours/2 connections for the analytics one.

jcrespo added a comment.EditedDec 2 2016, 3:40 PM

These should be the ideal grants for new users:

GRANT USAGE ON *.* TO '<user_name>'@'%' IDENTIFIED BY '<user_pass>' WITH MAX_USER_CONNECTIONS 10;
GRANT labsdbuser TO '<user_name>'@'%';
SET DEFAULT ROLE labsdbuser FOR '<user_name>'@'%';

I'm going to write a script called maintain-dbusers with the following properties:

  1. Keeps canonical information in a MySQL database
    1. Usernames
    2. Passwords (hopefully encrypted)
    3. db hosts this exists on (to allow us to audit / expand to other hosts later)
  2. Runs on a cron or watch to create new user accounts as necessary
  3. Has a manual command someone can run to add accounts to a new labsdb
  4. Has a manual command someone can run to force regenerating all accounts
  5. Will be bootstrapped by harvesting all current usernames and passwords.
  6. Use the new role granting permissions for new labsdbs, and same ol' current code for the old ones.
  7. Work only for the tools project.

Change 325268 had a related patch set uploaded (by Yuvipanda):
labs: Add db structure for keeping info about labsdb accounts

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

Change 325268 merged by Yuvipanda:
labs: Add db structure for keeping info about labsdb accounts

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

Change 325301 had a related patch set uploaded (by Jcrespo):
labsdb: Add minor fixes for maintain-dbusers schema

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

Change 325301 merged by Jcrespo:
labsdb: Add minor fixes for maintain-dbusers schema

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

chasemp removed yuvipanda as the assignee of this task.Dec 5 2016, 3:50 PM

I'm going to write a script called maintain-dbusers with the following properties:

  1. Keeps canonical information in a MySQL database
    1. Usernames
    2. Passwords (hopefully encrypted)
    3. db hosts this exists on (to allow us to audit / expand to other hosts later)
  2. Runs on a cron or watch to create new user accounts as necessary
  3. Has a manual command someone can run to add accounts to a new labsdb
  4. Has a manual command someone can run to force regenerating all accounts
  5. Will be bootstrapped by harvesting all current usernames and passwords.
  6. Use the new role granting permissions for new labsdbs, and same ol' current code for the old ones.
  7. Work only for the tools project.

We are also looking at per account restrictions w/ defaults via

https://phabricator.wikimedia.org/T149933#2829103

Probably makes sense to store this in the database for persistence?

After more chat, we decided that in the config we'll have the per-host limits, and also an exception list of users that'll have higher / lower limits (with rationale).

As a reminder, ALTER TABLEs on m5, with just a few thousand records, and close to 0 throughput on those tables, are "free", so we do not have to get it right on the first time. We can have something usable first, and tune it later.

I will now create the account administration user on the new hosts, now that the import has finished, although probably you will need more grants for it, for additional features.

I have added the several admin users to labsdb1009/10/11- the same actions that could be executed on labsdb1001/3 now can be executed there. Of course, if the features change, they may need additional grants, please tell me so.

As part of this, I've fixed all tools that didn't have a replica.my.cnf, and some other edge cases as well.

The following tools didn't have a replica.my.cnf, and these were regenerated:

tools.lolrrit-wm
tools.fatemi
tools.shfbot
tools.ryu
tools.mjbmrbot
tools.mjbmr-tools
tools.weather
tools.wikistreak
tools.wlm-jury-yarl
tools.yemen
tools.ytcleaner
tools.checkdictation-fa
tools.ircredirector
tools.qsgenerator
tools.luke081515bot-beta
tools.luke081515bot-dev
tools.piagetbot
tools.paws-stats
tools.paws-status

The following tools had accounts missing in one or more dbs, and their replica.my.cnf was also regenerated:

tools.plagiabot
tools.piagetbot
tools.repo

With this, all currently existing tools should have a working replica.my.cnf

When I finally got to actually creating the users and setting the roles, I ran into:

MariaDB [(none)]>         GRANT labsdbuser TO 's51051'@'%';
ERROR 1959 (OP000): Invalid role specification `labsdbuser`.
MariaDB [(none)]>         SET DEFAULT ROLE labsdbuser FOR 's51051'@'%';
ERROR 1959 (OP000): Invalid role specification `labsdbuser`.

on labsdb1009.

A small note owed as part of a previous conversation, at the moment create-dbusers is running on both labsdb1004 and labsdb1005 which is not a good outcome. We have handled this sort of scenario in the past by puppet fiat that declares an "active" node which services and jobs can take their cue from. This approach is detached from the canonical state management script nfs-manage so I'm hoping we can do something a bit different here in looking for a marker that script already manages. For instance, checking for the cluster ip would be a reasonable way to determine state. It will only ever exist on the primary in the pair. This does mean the service runs on both (which costs is very little but is true) but I'm thinking we will be wise to follow the single source of truth narrative.

I have created the role:

CREATE ROLE labsdbuser; on labsdb1009/10/11

So that now can be assigned (it has no extra grants at the moment).

@yuvipanda Check if you can assign that role to a user- if you cannot, we may have to create a role (actually, it would work like a group) for the several admin accounts and give that role the posibility of admin the labsdbuser role (we will try the simpler approach first).

@jcrespo thanks! And yup - the labsdbadmin role doesn't have the permissions to execute any of the three commands :

mysql> GRANT USAGE ON *.* TO blah@'%' IDENTIFIED BY '<user_pass>' WITH MAX_USER_CONNECTIONS 10;
ERROR 1045 (28000): Access denied for user 'labsdbadmin'@'10.64.37.19' (using password: YES)
mysql>  GRANT labsdbuser TO 'blah'@'%';
ERROR 1698 (28000): Access denied for user 'labsdbadmin'@'10.64.37.19'
mysql> SET DEFAULT ROLE labsdbuser FOR blah@'%';
ERROR 1044 (42000): Access denied for user 'labsdbadmin'@'10.64.37.19' to database 'mysql'
mysql>

Fixed. It turned out that you need GRANT OPTION and mysql edit grants to edit user accounts beyond mere creation:

labstore1004$ mysql -u labsdbadmin -h labsdb1009.eqiad.wmnet -p$PASS

mysql> GRANT USAGE ON *.* TO blah@'%' IDENTIFIED BY '<user_pass>' WITH MAX_USER_CONNECTIONS 10;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT labsdbuser TO 'blah'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SET DEFAULT ROLE labsdbuser FOR blah@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER blah@'%';
Query OK, 0 rows affected (0.00 sec)

This has been deployed now, and all tools have accounts in new labsdbs as well. They can't access those yet because of firewalling.

I still need to figure out best way of making this run only on one host.

Update: the replica script doesn't actually work for users yet, only for
tools. Will look into adding support for users too at some point soon.
However, for now, it only works for tools.

yuvipanda removed yuvipanda as the assignee of this task.Mar 22 2017, 10:31 PM

This was done, and @madhuvishy just made it work for users too.

madhuvishy closed this task as Resolved.Mar 27 2017, 5:24 PM

Closing this :)