mysqlISP Tutorial

ISP owners, managers, administrators and technical staff. Some sections require some basic SQL, BASH and *nix system administration skills.

Explain the use and scope of mysqlISP application.

In a nutshell mysqlISP assigns (and deploys across network of ISP servers) products to ISP clients (customers). These products consist of one or more sevices that similarly consist of one or more service parameters. End user level, Reseller level, Admin level and Root level record and operation permissions have been built in from the ground up (based on years of ism|2 experience.) Even ISP employees would have a hard time bypassing cgi security measures. The products are deployed for new/mod/del operations via an asynchronous job queue system and read by subsystem servers via mySQL tcp/ip connections (or if local much faster and secure mySQL socket.) Client side subsystem root command line operations done with crontab entries read the master mysqlISP job queue -this is very secure and much better than root permission httpd daemons...shudder. For the PRO ISP all this should be done with a second NIC card on all edge servers making up a private ISP management LAN. But mySQL GRANT command with passwd and user@hostip makes system pretty much secure, except for possible packet sniffers run on non switched hub local -unlikely unless server is hacked. Encrypted VPN, ssl tunnels etc can be used also for mySQL connections between servers.

unixservice.com commercial:
Non GPL software like unixservice.com's ism|4 (but at least very inexpensive -less than $100/Month- with sitewide license, install, monthly support hours, and yes it is opensource -installed on your server. Support is local to your region NA, SA, Europe etc.) can wrap mysqlISP for a non technical 100% customizable look and feel -skin template sets. ism|4 can also be used to automate billing of ISP clients (ACHDebits, credit card, email, checks, cash etc...electronically, periodically, one-time...etc.) Propietary closed source ISP software companies hate us believe me.

1. Plan ahead and map out all required services and resources that will be needed. Many mail MX? Distributed pop3/imap servers? How many webfarm servers needed? (One clone like below can handle 500 virtual sites easily.) Much better to go google.com route: Many cheap fast servers than few and expensive SUN iron. Linux level-7 switch boxes for load balancing, and automated failover system for server farm with at least 2 NICS in each? mySQL replication cluster? NAT internal ISP management LAN (Ex. 192.168.0.0/21 intranet.)
2. If 1. items above do not ring a bell don't worry: Single server network aplliance install is also possible and very easy.
3. Use wget (or other favorite tool) to download all software from openisp.net to a gz dir on the server that you will be using: Minimum: mysqlISP, and one mysqlISP service provider module like mysqlSendmail,mysqlApache or mysqlBind. These may or may not require additional software to run. Most will come on new Linux distros or will be on openisp.net site.
Ex. shell>mkdir gz; cd gz; wget http://openisp.net/mysqlISP/mysqlISP1.1.tar.gz; cd ..
4. gunzip -dc gz/mysqlISP1.1.tar.gz | tar xf - (replace mysqlISP1.1 for each tar.gz you need.)
5. cd mysqlISP; vi local.h; make; This will get you started and show if your system has all the mysql and other development libs and include headers you need.
6. You may want to clean up /etc/skel it usually has much more than you need for your basic user accounts
7. useradd -s /bin/nologin openisp
8. mkdir ~openisp/logs; mkdir ~openisp/run; mkdir ~openisp/htdocs; mkdir ~openisp/conf; mkdir ~openisp/cgi-bin; mkdir -p ~openisp/mysqlISP/data; mkdir -p ~openisp/mysqlBind/data...etc. chown -R openisp ~openisp; chgrp -R openisp ~openisp; chown mysql ~openisp/mysqlISP/data etc...(crontab scripts need mysql to be able to access the data dirs.)
9. Get and install Apache 1.3.27 mod_ssl -DO NOT USE Apache 2.0+ for production servers yet!- See www.mod_ssl.org for easy to follow instructions. (Expert hints: Make a new CA for internal use and server certs.)
10. Modify and take notes of all work being done in /etc/rc.d/rc.local as well as adding the startup for the nonstandard random port webadmin httpd made in step 8. Example files here:
webadmin_httpd.conf.txt this goes in the conf dir as httpd.conf. Edit accordingly! Hints: Make sure you add an index.html to the ~openisp/htdocs dir and you setup CORRECT access restrictions to the cgi-bin dirs.
rc.local.txt this is for your /etc/rc.d/rc.local. And start your webadmin httpd daemon by copying exactly the line from your rc.local file (make sure the boot will work also. Experts: You can also and should create an init.d script for this.)
11. Start and clean up mySQL with the mysql command line interface and clean up the db and user tables. Also set the root password as shown below and only allow access from the localhost socket.

shell>/etc/rc.d/init.d/mysqld start

(add this to the rc.local or better yet the rc3.d. Your distro may vary. See www.mysql.com)

shell>mysql -u root mysql;
mysql>DELETE FROM user WHERE user!='root';
mysql>DELETE FROM user WHERE host!='localhost';
mysql>DELETE FROM db;
mysql>GRANT ALL ON *.* TO root@localhost IDENTIFIED BY 'wsxedc';

(wsxedc=whatever your mysql root passwd will be take note of it.)

12. cd to your source code dir. Make sure mysql user can get to the mysqlISP/data dir: chown mysql data; chmod o+x ../ then run make install for the mysqlISP.cgi and service .cgi's. First export ISMROOT=~; export CGIDIR=~openisp/cgi-bin/ before make install. Then usually you run the cgi in commandline mode for initialization and even BIND, RADIUS and Sendmail dir setup and mysql database creation and loading of distributed table data. Running the cgi with no args will provide a menu.
13. Setup root crontab (crontab -e as su). See example file below
crontab.txt
14. Setup /etc/rc.d/rc3.d/S and K sym links. Turn off not needed items like the linux cups printer server etc...
15. Test and reboot several times.

This is an example and maybe overkill.
1. You need a fast server: 1Ghz-CPU+, Athlon XP or Pentium 4 class at least. With min 512 Mb RAM. With at least 2 identical reliable 7200 rpm hard disks (burn in test with bonnie, stress, dd etc.)
2. You need a nice (GNU-)Linux distribution with everything installed (ext2 filesystem seems to us at this time much better for ISP's than ext3. But check this out further.) Bind 9, Sendmail, Procmail, Spamassasin, all the development tools (gcc, make etc.) Redhat 9.0 works fine, but I'm sure other distros are just as good or better. Make recover floppy leave in floppy drive half-in. If you have the money use a CD-R burner instead of normal CDROM and use for hacker proof backups.
3. Firewall MAX at install. You don't want ANYBODY getting in to your server during install and post install.
4. Convert to headless server with emergency boot and backup drive: Edit /etc/inittab for level 3 boot. Adjust Bios for no keyboard. Reboot and power fail test many times. Use dd to bitcopy boot drive to your second drive (see file below.) Test booting from safety backup drive by just swapping drive cables (scsi id etc.)
clone.txt
5. Check all /etc/sysconfig files. Add virtual IP ranges. Set server name. Edit /etc/hosts.allow and deny. Edit /etc/ssh/sshd_config and DO NOT ALLOW root ssh login, Add sshd_config line: AllowUsers jss,jts,jds (Joe Somebody Secure and all other remote admins) add jss to /etc/ftpusers. Never use jss for pop3, ftp, etc. Add jss to /etc/sudoers for easy root account access with out knowing the root passwd same for all admins. Turn off firewall or tweak (experts only.) If expert setup com2 for out-of-band access to server from other in-band or dial-up device (great for colocation facilities.)
6. Use virtual IP's for all services (this allows fast seamless migration -or emergency failover- in most cases. Ex. mail.isp.net should not use the same IP as the server, same goes for Bind/DNS named, pop3, sendmail etc.) Plan ahead use mysqlIPM for allocation and control of your IP space. Take note of all MAC HDAddr codes of all your ethernet ports (Will be in mysqlIPM shortly.)

After you have done the INSTALL procedure (make, make install, /cgi-bin/mysqlISP.cgi Initialize mysqlrootpasswd.) You should be able to login the mysqlISP.cgi webconsole as "Root" with the default passwd as set in mainfunc.h
You should then proceed to change the special tClient.uClient=1 "Root" user password. You can always change this via mysql if you forget it by pasting in the standard shadow salt+DES3 encrypted password you know the clear text to.
You should then proceed to create another root level (12) authorized (via hidden table tAuthorize) tClient. This is the user you would usually work as, when root level operations are needed (not very often.)

Next you should create a reseller level tClient and authorized user. This should be your own ISP master user. All new end clients and their products and end services, as well as billing onluy products, should be created with this account.
How is this done? Easy...start at tClient, click on [New], fill in the cLabel with the login name, and any other information -in the corresponding fields- you may wish to have handy or that would be required for accounting, service and auditing purposes. (As you may guess by now, this mySQL centric industrial interface has the SQL columns as form input names -for faster developer work. And every variable has a lower case prefix followed by a Capital significant name. This is for easy and safer progamming: Example cLabel, c for char, usually a char string. u for unsigned, m for money etc...If you want a friendlier interface make one, or get unixservice.com to help you.) Then [Create New Record], you will continue to be in [New] mode for faster multiple new tClient sessions. This time however click on [Modify], then [Authorize], select the user level from the drop down and enter the password, then finally [Confirm Authorize]. After creating your own root level user you should logout and login as that user to create the reseller level user. Notes uPayment should be 0 or --- for these special users of your own since they will never be billed. This applies to all level users, even free end users. Usually reseller and customer level users authorized or not (usually you will not authorize and end user to use this webconsole) will have a uPayment selected. This will set their default billing cycle and other basic billing settings.

You can now optionally create any non root but staff admin level users, that will basically be able to do anything a root level user can do, but usually can't delete things done by the special "Root" user (tClient.uClient=1) and also can't mess with special system wide configuration files, change other root level users passwords or finally change user levels.

Coming...

See accounting.html

cAddMX1
cAddMX2
cExtmysqlApacheDbIp
cExtmysqlApacheDbLogin
cExtmysqlApacheDbName
cExtmysqlApacheDbPwd
cExtmysqlBindDbIp
cExtmysqlBindDbLogin
cExtmysqlBindDbName
cExtmysqlBindDbPwd
cExtmysqlRadiusDbIp
cExtmysqlRadiusDbLogin
cExtmysqlRadiusDbName
cExtmysqlRadiusDbPwd
cExtmysqlSendmailDbIp
cExtmysqlSendmailDbLogin
cExtmysqlSendmailDbName
cExtmysqlSendmailDbPwd
cFromEmailAddr
cInvoiceBaseDir
cInvoiceBccEmailAddr
cMySQLRootPwd
cuPaidCreditProduct
cuPermCreditProduct
cuTempCreditProduct
UpdateInfo

More on these soon. Or read actual tConfiguration cComments