Nova Resource:Account-creation-assistance

From Wikitech
Jump to navigation Jump to search
Project Name account-creation-assistance

Account Creation Assistance tool


A test environment for the Account Creation Assistance project on the English Wikipedia


To aid new users to create accounts (adding a human eye to the process when AntiSpoof or TitleBlacklist is hit so we can override if necessary). See WP:ACC for more information.

Anticipated traffic level

10-100 hits per day

Anticipated time span


Project status

currently running

Contact address

Willing to take contributors or not

not willing

Subject area narrow or broad

narrow Live server is accounts-appserver4. Database server is accounts-db3.



This is the production server.

Puppet configuration
  • Apache/webserver::php5-mysql

Install packages php5-curl php5-mcrypt php5-gd php5-cli

Tool setup

cd /var/www/
git clone .
git submodule init
git submodule sync
sudo chown -R www-data /var/www

Get the configuration file from stwalkerster.


This is a new stretch instance. As much as possible, I'm trying to avoid touching the default configuration files.

sudo apt-get install mariadb-server percona-toolkit

As such, customised mariadb configuration is in /etc/mysql/mariadb.conf.d/60-acc-config.cnf

 # Network
 bind-address            = 

 # Replication
 server_id               = 4
 log_bin                 = /var/log/mysql/mysql-bin.log
 # Query logging
 slow_query_log_file     = /var/log/mysql/mysql-slow.log
 slow_query_log          = 1
 long_query_time         = 2
 # InnoDB tuning
 innodb_buffer_pool_size = 1536M
 innodb_log_file_size    = 512M
 # Tablespace tuning
 innodb_file_per_table   = on

MySQL configuration

Drop anonymous accounts
DROP USER ''@localhost;
DROP USER ''@'accounts-db2';
Assign passwords to the root accounts
SET PASSWORD FOR 'root'@'::1' = PASSWORD('****');
SET PASSWORD FOR 'root'@'' = PASSWORD('****');
SET PASSWORD FOR 'root'@'accounts-db2' = PASSWORD('****');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('****');
Create user accounts for users
CREATE USER 'stwalkerster'@'%' IDENTIFIED BY '****';
CREATE USER 'fastlizard4'@'%' IDENTIFIED BY '****';
CREATE USER 'deltaquad'@'%' IDENTIFIED BY '****';
Setup permissions for users
GRANT ALL ON *.* TO 'stwalkerster'@'%' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'fastlizard4'@'%' WITH GRANT OPTION;
GRANT ALL ON *.* TO 'deltaquad'@'%' WITH GRANT OPTION;
Create database, user, and grants for production use
CREATE USER 'production'@'%' IDENTIFIED BY '****';
GRANT SELECT, UPDATE, INSERT, DELETE, SHOW VIEW ON production.* TO 'production'@'%';
Create database, user, and grants for development use
GRANT ALL ON `sandbox%`.* TO 'dev'@'%';

When importing views from the database dumps, be careful with SQL SECURITY DEFINER and DEFINER = 'acc'@'' in the view definitions. This needs to be re-written on import.

create or replace
VIEW `user` AS select`production`.`acc_user`.`user_id` AS `id`,`production`.`acc_user`.`user_name`AS `username`,`production`.`acc_user`.`user_email` AS`email`,`production`.`acc_user`.`user_pass` AS`password`,`production`.`acc_user`.`user_level` AS`status`,`production`.`acc_user`.`user_onwikiname` AS`onwikiname`,`production`.`acc_user`.`user_welcome_sig` AS`welcome_sig`,`production`.`acc_user`.`user_lastactive` AS`lastactive`,`production`.`acc_user`.`user_forcelogout` AS`forcelogout`,`production`.`acc_user`.`user_secure` AS`secure`,`production`.`acc_user`.`user_checkuser` AS`checkuser`,`production`.`acc_user`.`user_identified` AS`identified`,`production`.`acc_user`.`user_welcome_templateid` AS`welcome_template`,`production`.`acc_user`.`user_abortpref` AS`abortpref`,`production`.`acc_user`.`user_confirmationdiff` AS`confirmationdiff`,`production`.`acc_user`.`user_emailsig` AS `emailsig` from`acc_user`;
create or replace
VIEW `createdusers` AS select distinct `production`.`acc_pend`.`pend_name` AS`pend_name` from (`acc_log` join `acc_pend`on((`production`.`acc_pend`.`pend_id` = `production`.`acc_log`.`log_pend`)))where (`production`.`acc_log`.`log_action` = 'Closed 1') order by`production`.`acc_pend`.`pend_name`;
VIEW `closes` AS selectconcat('Closed ',`production`.`acc_emails`.`mail_id`) AS `CONCAT("Closed",mail_id)`,`production`.`acc_emails`.`mail_desc` AS `mail_desc` from`acc_emails` where (`production`.`acc_emails`.`mail_type` = 'Message') unionselect 'Closed 0' AS `Closed 0`,'Dropped' AS `Dropped` union select 'Closedcustom' AS `Closed custom`,'Closed custom' AS `Closed custom` union select'Closed custom-n' AS `Closed custom-n`,'Closed custom - Not created' AS `Closedcustom - Not created` union select 'Closed custom-y' AS `Closedcustom-y`,'Closed custom - Created' AS `Closed custom - Created`;
VIEW `ban` AS select `production`.`acc_ban`.`ban_id` AS`id`,`production`.`acc_ban`.`ban_type` AS`type`,`production`.`acc_ban`.`ban_target` AS`target`,`production`.`acc_ban`.`ban_user` AS`user`,`production`.`acc_ban`.`ban_reason` AS `reason`,`production`.`acc_ban`.`ban_date`AS `date`,`production`.`acc_ban`.`ban_duration` AS`duration`,`production`.`acc_ban`.`ban_active` AS `active` from `acc_ban`;

DB server migration

On source server
mysqldump -A --dump-slave=1 > dbdata.sql
bzip2 dbdata.sql
scp dbdata.sql.bz2 accounts-db2.eqiad.wmflabs:dbdata.sql.bz2
On destination server
bunzip2 dbdata.sql.bz2
mysql -u root < dbdata.sql

sudo service mysql restart

To Do

  • Puppetise package installation on appserver1
  • Move docroot to /data/project or script the deploy of the tool.
  • Database backups
  • Session save path is currently /tmp
  • Configuration is currently done directly in
  • XFF through NovaProxy, or get an SSL cert. Yes Done
  • Crontab:
#      iptable
0 0 * * * qcronsub -N iptable /home/project/a/c/c/acc/public_html/sge/
#      statsemail
0 0 * * * /sge/GE/bin/sol-amd64/qcronsub -N statsemail /home/project/a/c/c/acc/public_html/sge/
#      dataclear
0 0 * * * /sge/GE/bin/sol-amd64/qcronsub -N dataclear /home/project/a/c/c/acc/public_html/sge/
#      backup
0 0 * * * /sge/GE/bin/sol-amd64/qcronsub -N backup /home/project/a/c/c/acc/public_html/sge/
#      backup-monthly
0 0 1 * * /sge/GE/bin/sol-amd64/qcronsub -N backup-monthly /home/project/a/c/c/acc/public_html/sge/

SSH instructions

Since the development system (accounts-application) is not public-facing, you must use SSH port forwarding to access it. Note that the instructions below assume that you've already loaded your private key for WMFLabs into ssh-agent or Pageant.

From Linux/Unix(-like) systems
  • ssh -AL 8080:accounts-application:80
  • Then from within Bastion:
    • ssh -A accounts-application
    • Remember to use the -A flag here, too, so you can git push directly from your development area into the GitHub repository.
From Windows using PuTTY
  1. Open PuTTY. Enter as the host name to connect to, port 22, connection type SSH.
  2. In the list on the left, open Connection > SSH > Auth
  3. Check Allow agent forwarding
  4. In the list on the left, now open Connection > SSH > Tunnels
  5. Under Add a new forwarded port, enter the following:
    • Source port: 8080
    • Destination: accounts-application:80
    • Select the radio buttons Local and IPv4
  6. Click the Add button, then Open to start the connection
  7. At the bastion bash prompt, type ssh -A accounts-application to SSH into the application server.

Following the directions above, you can now enter localhost:8080 into your browser to access the accounts-application server.

Edit documentation

Server admin log


  • 11:16 dcaro: removing custom mx hosts, as the global names are now resolvable again (T271322)


  • 14:53 dcaro: manually configured mx servers to use domain on project hiera (T271322)


  • 20:35 Izhidez: apache2 restarted
  • 20:33 Izhidez: apache2 shutdown to deal with spam (742 line DB change)


  • 12:05 arturo: VM instances accounts-appserver5, were stopped briefly due to issue in hypervisor (T215012)
  • 12:04 arturo: VM instances accounts-appserver5, were ... (more)