Nova Resource:Account-creation-assistance

From Wikitech
Jump to navigation Jump to search
Project Name account-creation-assistance
Details,
admins/members
openstack-browser
Monitoring

Account Creation Assistance tool

Description

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

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.

Anticipated traffic level

10-100 hits per day

Anticipated time span

indefinite

Project status

currently running

Contact address

accounts-enwiki-l@lists.wikimedia.org

Willing to take contributors or not

not willing

Subject area narrow or broad

narrow


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

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

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

 [mysqld]
 # Network
 bind-address            = 0.0.0.0 

 # 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
 log_queries_not_using_indexes
 
 # 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'@'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`;

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 config.local.inc.php
  • 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/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.

Edit documentation

Server admin log

2019-06-19

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

2019-01-31

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

September 20

  • 14:00 andrewbogott: moving accounts-mwoauth to virt1005

January 15

  • 08:23 andrewbogott: rebooted accounts-puppetmaster

November 1

  • 06:33 FastLizard4: running ... (more)