Nova Resource:Account-creation-assistance/Documentation

Account Creation Assistance tool

Purpose

[[Purpose::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.]]

Willing to take contributors or not

not willing

Subject area narrow or broad

narrow

Live server is accounts-appserver2. Database server is accounts-db2.

Setup

accounts-appserver2

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 https://github.com/enwikipedia-acc/waca.git .
git submodule init
git submodule sync
sudo chown -R www-data /var/www

Get the configuration file from stwalkerster.

accounts-db2

This is the database server.

Puppet configuration

• Database/role::labs-mysql-server
• Mysql/mysql_server_bind_address = 0.0.0.0

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'@'127.0.0.1' = 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 DATABASE production;
CREATE USER 'production'@'%' IDENTIFIED BY '****';
GRANT SELECT, UPDATE, INSERT, DELETE, SHOW VIEW ON production.* TO 'production'@'%';

Create database, user, and grants for development use

CREATE DATABASE sandbox;
CREATE USER 'dev'@'%' IDENTIFIED BY '****';
GRANT ALL ON `sandbox%`.* TO 'dev'@'%';

Views

When importing views from the database dumps, be careful with SQL SECURITY DEFINER and DEFINER = 'acc'@'%.toolserver.org' 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`;

CREATE OR REPLACE
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`;

CREATE OR REPLACE
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`;

Datacentre migration

On accounts-db1

mysqldump -A > dbdata.sql
bzip2 dbdata.sql
scp dbdata.sql.bz2 accounts-db2.eqiad.wmflabs:dbdata.sql.bz2

On accounts-db2

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 config.local.inc.php
• XFF through NovaProxy, or get an SSL cert. Done
• Crontab:

#
#      iptable
0 0 * * * qcronsub -N iptable /home/project/a/c/c/acc/public_html/sge/sge-iptable.sh
#
#      statsemail
0 0 * * * /sge/GE/bin/sol-amd64/qcronsub -N statsemail /home/project/a/c/c/acc/public_html/sge/sge-statsemail.sh
#
#      dataclear
0 0 * * * /sge/GE/bin/sol-amd64/qcronsub -N dataclear /home/project/a/c/c/acc/public_html/sge/sge-dataclear.sh
#
#      backup
0 0 * * * /sge/GE/bin/sol-amd64/qcronsub -N backup /home/project/a/c/c/acc/public_html/sge/sge-backup.sh
#
#      backup-monthly
0 0 1 * * /sge/GE/bin/sol-amd64/qcronsub -N backup-monthly /home/project/a/c/c/acc/public_html/sge/sge-backup-monthly.sh

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 yourusername@bastion.wmflabs.org
• 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 yourusername@bastion.wmflabs.org 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.