Yuvipanda has submitted this change and it was merged.
Change subject: labstore: Rewrite of replica-addusers.pl
......................................................................
labstore: Rewrite of replica-addusers.pl
- Switch it to opt-in per project rather than turned on by
default for all projects. That caused a deluge of unnecessary
and unused mysql accounts, and also presumed that all projects
got NFS by default (which is not the case).
- Opt-in only tools by default.
- Keeps state only in the replica.my.cnf files themselves - no
state kept elsewhere (other than in the mysql dbs themselves, ofc)
TODO:
- Add support for user accoutns, not just service groups
- Figure out what to do if the replica.my.cnf file is missing but
the user / grant already exists.
Bug: T104453
Change-Id: I90dc98401b89e769fa058943e3714e383dfe25ea
---
A modules/labstore/files/create-dbusers
A modules/labstore/manifests/account_services.pp
M modules/labstore/manifests/fileserver.pp
A modules/labstore/templates/initscripts/create-dbusers.systemd.erb
4 files changed, 223 insertions(+), 0 deletions(-)
Approvals:
Yuvipanda: Verified; Looks good to me, approved
jenkins-bot: Verified
diff --git a/modules/labstore/files/create-dbusers
b/modules/labstore/files/create-dbusers
new file mode 100755
index 0000000..b1329a0
--- /dev/null
+++ b/modules/labstore/files/create-dbusers
@@ -0,0 +1,164 @@
+#!/usr/bin/python3
+"""
+This script does the following:
+
+ - Check if users / service groups in opted in projects have
+ a replica.my.cnf file with mysql credentials
+ - If they do not exist, create a mysql user, give them
+ appropriate grants and write the replica.my.cnf file
+ - If there is no replica.my.cnf but grants already exist, do
+ nothing. This needs to be fixed with appropriate solution
+ later on - either by recreating the replica.my.cnf or...
+ something else
+"""
+import logging
+import argparse
+import ldap3
+import pymysql
+import yaml
+import re
+import os
+import string
+import random
+import configparser
+
+
+class User:
+ def __init__(self, project, name, uid, homedir):
+ self.project = project
+ self.name = name
+ self.uid = int(uid)
+ self.homedir = homedir
+
+ @property
+ def db_username(self):
+ """
+ The db username to use for this user.
+
+ Guaranteed to be of the form (s|u)\d+
+ """
+ return 's%s' % self.uid
+
+ def __repr__(self):
+ return "User(name=%s, uid=%s, homedir=%s)" % (
+ self.name, self.uid, self.homedir)
+
+ @classmethod
+ def from_ldap_servicegroups(cls, conn, projectname):
+ conn.search(
+ 'ou=people,ou=servicegroups,dc=wikimedia,dc=org',
+ '(cn=%s.*)' % projectname,
+ ldap3.SEARCH_SCOPE_WHOLE_SUBTREE,
+ attributes=['uidNumber', 'homeDirectory', 'cn']
+ )
+ users = []
+ for resp in conn.response:
+ attrs = resp['attributes']
+ users.append(cls(projectname, attrs['cn'][0],
attrs['uidNumber'][0], attrs['homeDirectory'][0]))
+
+ return users
+
+
+class CredentialCreator:
+ PASSWORD_LENGTH = 16
+ PASSWORD_CHARS = string.ascii_letters + string.digits
+ GRANT_SQL_TEMPLATE = """
+ 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}'@'%';"""
+
+ def __init__(self, hosts, username, password):
+ self.conns = [
+ pymysql.connect(host, username, password)
+ for host in hosts
+ ]
+
+ @staticmethod
+ def _generate_pass():
+ sysrandom = random.SystemRandom() # Uses /dev/urandom
+ return ''.join(sysrandom.sample(
+ CredentialCreator.PASSWORD_CHARS,
+ CredentialCreator.PASSWORD_LENGTH))
+
+ def check_user_exists(self, user):
+ exists = True
+ for conn in self.conns:
+ conn.ping(True)
+ cur = conn.cursor()
+ try:
+ cur.execute('SELECT * FROM mysql.user WHERE User = %s',
user.db_username)
+ result = cur.fetchone()
+ finally:
+ cur.close()
+ exists = exists and (result is not None)
+ return exists
+
+ def create_user(self, user, password):
+ for conn in self.conns:
+ conn.ping(True)
+ cur = conn.cursor()
+ try:
+ # is ok, because password is guaranteed to never
+ # contain a quote (only alphanumeric) and username
+ # is guaranteed to be (u|s)\d+.
+ sql = CredentialCreator.GRANT_SQL_TEMPLATE.format(
+ user_name=user.db_username,
+ user_pass=password
+ )
+ cur.execute(sql)
+ finally:
+ cur.close()
+
+
+if __name__ == '__main__':
+ PROJECTS = ['tools']
+
+ argparser = argparse.ArgumentParser()
+ argparser.add_argument('--config', help='Path to YAML config file')
+ argparser.add_argument('--debug', help='Turn on debug logging',
+ action='store_true')
+ args = argparser.parse_args()
+
+ logging.basicConfig(format='%(message)s',
+ level=logging.DEBUG if args.debug else logging.INFO)
+
+ with open(args.config) as f:
+ config = yaml.safe_load(f)
+
+ server = ldap3.Server(config['ldap']['host'])
+ conn = ldap3.Connection(
+ server, read_only=True,
+ user=config['ldap']['username'],
+ auto_bind=True,
+ password=config['ldap']['password'])
+
+ servicegroups = User.from_ldap_servicegroups(conn, 'tools')
+ cgen = CredentialCreator(
+ config['mysql']['hosts'],
+ config['mysql']['username'],
+ config['mysql']['password']
+ )
+
+ for sg in servicegroups:
+ # FIXME: for non tools/maps projects this path is different
+ replica_path = os.path.join(
+ '/srv/project/', sg.project, 'project',
+ re.sub(r'^%s\.' % sg.project, '', sg.name),
+ 'replica.my.cnf'
+ )
+ if not os.path.exists(replica_path):
+ if not cgen.check_user_exists(sg):
+ # No replica.my.cnf and no user in db
+ # Generate new creds and put them in there!
+ password = cgen._generate_pass()
+ replica_config = configparser.ConfigParser()
+ replica_config['client'] = {
+ 'user': sg.db_username,
+ 'password': password
+ }
+ cgen.create_user(sg, password)
+ with open(replica_path, 'w') as f:
+ replica_config.write(f)
+ logging.info("Created replica.my.cnf for %s, with username
%s", sg.name, sg.db_username)
+ else:
+ logging.info('Missing replica.my.cnf for user %s despite
grants present in db', sg.name)
diff --git a/modules/labstore/manifests/account_services.pp
b/modules/labstore/manifests/account_services.pp
new file mode 100644
index 0000000..65ee233
--- /dev/null
+++ b/modules/labstore/manifests/account_services.pp
@@ -0,0 +1,52 @@
+# == Class: labstore::account_services
+# Provides account services for labs user accounts,
+# currently in the labstore module because we put these
+# on the user's homedirs on NFS.
+#
+# Currently provides:
+# - MySQL replica / toolsdb accounts
+#
+class labstore::account_services {
+
+ require_package('python3-yaml', 'python3-ldap3')
+
+ include passwords::ldap::labs
+ include passwords::mysql::labsdb
+
+ $creds = {
+ 'ldap' => {
+ 'host' => 'ldap-eqiad.wikimedia.org',
+ 'username' => 'cn=proxyagent,ou=profile,dc=wikimedia,dc=org',
+ 'password' => $::passwords::ldap::labs::proxypass,
+ },
+ 'mysql' => {
+ 'hosts' => [
+ 'labsdb1001.eqiad.wmnet',
+ 'labsdb1002.eqiad.wmnet',
+ 'labsdb1003.eqiad.wmnet',
+ 'labsdb1005.eqiad.wmnet',
+ ],
+ 'username' => $::passwords::mysql::labsdb::user,
+ 'password' => $::passwords::mysql::labsdb::password,
+ }
+ }
+
+ file { '/etc/create-dbusers.yaml':
+ content => ordered_json($creds),
+ owner => 'root',
+ group => 'root',
+ mode => '0444',
+ }
+
+ file { '/usr/local/sbin/create-dbusers':
+ source => 'puppet:///modules/labstore/create-dbusers',
+ owner => 'root',
+ group => 'root',
+ mode => '0555',
+ }
+
+ base::service_unit { 'create-dbusers':
+ systemd => true,
+ declare_service => false,
+ }
+}
diff --git a/modules/labstore/manifests/fileserver.pp
b/modules/labstore/manifests/fileserver.pp
index 32e5cd0..55104dd 100644
--- a/modules/labstore/manifests/fileserver.pp
+++ b/modules/labstore/manifests/fileserver.pp
@@ -71,4 +71,5 @@
}
include ::labstore::fileserver::exports
+ include ::labstore::account_services
}
diff --git a/modules/labstore/templates/initscripts/create-dbusers.systemd.erb
b/modules/labstore/templates/initscripts/create-dbusers.systemd.erb
new file mode 100644
index 0000000..78a9842
--- /dev/null
+++ b/modules/labstore/templates/initscripts/create-dbusers.systemd.erb
@@ -0,0 +1,6 @@
+[Unit]
+Description=DB Accounts and Grants createor
+
+[Service]
+ExecStart=/usr/local/sbin/create-dbusers --config /etc/create-dbusers.yaml
+Restart=no
--
To view, visit https://gerrit.wikimedia.org/r/223564
To unsubscribe, visit https://gerrit.wikimedia.org/r/settings
Gerrit-MessageType: merged
Gerrit-Change-Id: I90dc98401b89e769fa058943e3714e383dfe25ea
Gerrit-PatchSet: 8
Gerrit-Project: operations/puppet
Gerrit-Branch: production
Gerrit-Owner: Yuvipanda <yuvipa...@wikimedia.org>
Gerrit-Reviewer: Jcrespo <jcre...@wikimedia.org>
Gerrit-Reviewer: Yuvipanda <yuvipa...@wikimedia.org>
Gerrit-Reviewer: coren <mpellet...@wikimedia.org>
Gerrit-Reviewer: jenkins-bot <>
_______________________________________________
MediaWiki-commits mailing list
MediaWiki-commits@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-commits
