Pggraph

README -- Mar 13, 2008 -- Aurynn Shaw -- ashaw@commandprompt.com

== At A Glance Installation ==

 * Use /var/www/python/_main/install.py. Consult this document for specifics.

 * symlink busy_dml_report.py into your new install dir.

 * Run the forwarder, /var/www/python/_main/forward.py /path/to/your/new/install

 * Check if you need to set up a bastion install

   * If you do, ssh to the bastion and run

     * screen

     * inside screen: ssh -L7750:localhost:5432 cmd@the_actual_db_server

     * ctrl-a ctrl-d, detach from screen. It keeps on truckin'.

 * Run python /var/www/pggraph/_main/bridge.py /path/to/your/new/install/config.ini

 * Wait until it returns. Now run it again.

 * check http://www.cmdalert.com/pggraph/your_install/

 * Make sure there's graphs.

 * Go edit the Apache config, copy and paste the existing format for PG-backed password auth.

   * The file is 001-something in /etc/apache2/sites-enabled

 * Using the existing format, dump and add the database users from the client 

   Trac to serve as the auth for pggraph.

 * Reload apache.

 * Add the cronjob, based on the other cronjobs for pggraph.

 * Fini.

== Introduction ==

If youre reading this document, congratulations! Youve managed to 

successfully install the server software for PGGraph! Youve done an amazing

thing, and you ought to be proud of yourself.

This document will be discussing the next step, setting up client systems for

pggraph, the configuration weirdness and gotchas that you need to know.

The specific sections in this document are as follows:

 * The Installer

 * Server preparation

 * Running it the first time

 * Cronjob

 * Cursing

== The Installer ==

Step one, setting up a client instance.

Given PGGraphs curious evolutionary path, the process of installing a client

was once much more difficult than it is now, and thus, I created the 

Installer.

The installer is run from the source directory of all PGGGraph scripts - in

the case of cmdalert.com, /var/www/pggraph/_main/

The software is executed thusly:

$ ./python install.py

It will then ask you a number of questions specifically relating to the 

install process.

For clarity, the installer is reproduced here to give you a basic walkthrough

on what values are expected and a basic concept of how PGGraph works.

{{{

    Installation directory?  || Requires absolute path: This is the exact path to the install. /var/www/pggraph/some_dir. || 

    Relative WWW path?  || Usually pggraph/prefix: pggraph/some_dir. Controls HTML generation. || 

    Full text title?  || Usually customer name: Page title, in the databse || 

    Now for some database configuration..

    Local pggraph database name? [pggraph]  || Don't change this, unless you're putting their data in a new DB || 

    Table prefix?  || some prefix, like aweber or vltlgx or something. To keep tables from colliding. || 

    Local database port? [5432]  || It's on 5432. || 

    Local pggraph host? usually localhost [localhost]  || It's always localhost. Except when it isn't. || 

    Local username? usually pggraph [pggraph]  || It's pggraph. || 

    Password? Can be blank [8sm4LA2]  || It's this password. || 

    Remote DB username?  || Username we connect to the remote DB with. cmd, postgres, or other. || 

    Remote DB password?  || As described || 

    Remote DB host? [localhost]  || Almost always localhost. || 

    Local port to bind to remote PG port? [7555]  || Always a non-5432 local port. || 

    Some pggraph settings..

    Shortest display interval, in minutes? [10]  || How frequently we're updating the database. || 

    Maximum number of images per page? [10]  || Suffers from an off-by-one error, so enter 1 more than you want to display. || 

    Delay before continuing PGGraph startup [7]  || Number of seconds after initializing a new tunnel via forward, that the script waits before attempting to connect. || 

    So far, so good. Now for the SSH configuration for the PG tunnel.

    SSH User?  || SSH user name. almost always cmd || 

    SSH Password?  || Deprecated in favour of || 

    Authentication keyfile? [/home/pggraph/.ssh/id_dsa]  || the default admin ssh key. Good almost everywhere. || 

    Remote server?  || remote server IP || 

    Port to SSH to? [22]  || Remote port to SSH to || 

    Bind to what port? [5432]  || Remote port to bind to - not always 5432, in cases of bastions. || 

    Bind to which remote address? [localhost]  || Again, not always, in case of bastions. || 

    Use a twin tunnel mechanism? Leave blank if you dont know currently buggy:  || Don't use this. It was a prior attempt at making bastions work. || 

        Address for 2nd server?  || Only shows up if you select twin tunnel. So don't. :) || 

}}}

Any question with a [] following it indicates that pressing enter will give 

you the default value.

Once you've answered all the installer's questions, it will go off and create

the appropriate directories, create symlinks for all the necessary scripts and

libraries, as well as write out the configuration data.

At this point, you're about halfway done.

The first step (as of Mar 17, 2008), is to symlink busy_dml_report.py into the

freshly created directory. It's a recent addition and is not yet supported by

the installer script.

== Server Preparation ==

=== No Bastion ===

The next step is, the remote server information needs to be verified. This is 

always going to be in the customers' Trac wiki, and easily found.

To test the tunneling setup:

{{{

   $ python /var/www/pggraph/forward.py /var/www/new_server

   Initializing tunnel..

   $ psql -d template1 -U cmd -h localhost -p <the port you gave it>

}}}

If you connect, success! You can move on to the next phase in the installation.

If you don't, you can use the error message you got in helping you track down

why. The most common errors I've had are specific to pg_hba settings being

incorrect, and the cmd user not existing.

=== Bastions ===

A fact of life, some of our customers don't trust people on the internet. It's

hard to not blame them for that, but it does make our life a lot harder when

it comes to running pggraph for them.

To set up a bastion host for pggraph, you're going to need to do a little bit

of trickery.

The specific process I've followed is:

 * Set up forward.py as normal on cmdalert.

 * ssh into the bastion server

 * Using screen, run

   {{{

       ssh -L7750:localhost:5432 cmd@the_actual_db_server

   }}}

 * Detach from screen, leaving the ssh tunnel open.

 * Now that you're back on the bastion server, see if you can telnet to port 

   7750. 

 * If you can, disconnect and attempt to connect via psql from cmdalert.

   * Retrace your steps in the event that you can't connect.

   * Most error messages are going to be caused by incorrect settings in

     pg_hba.conf.

You will need to check on your bastion-side tunnel from time to time, as 

they are significantly more fragile than the standard pggraph installation,

and require manual intervention to recreate.

== Running PGGraph ==

At this point, you're close to finished the PGGraph installation. The next 

step is to run the data-collecting script and test for proper graph 

generation.

This is fairly easily done, simply

{{{

python ../_main/bridge.py ./config.ini

}}}

from the newly installed PGGraph client path.

This will attempt to connect across the tunnel and collect the data that is

used to build the graphs.

At this point, you should open a browser window to the newly created path (as

mentioned in the installer), usually http://cmdalert.com/pggraph/your_client/

The first graph is a good indicator whether or not data is being correctly

collected, and its presence shows that you can move on to the next step.

If it fails to display, first you should re-run the data collector, and test 

the graph again. A lot of the architecture of PGGraph is based around deltas

of dataset - (dataset - 1 time unit), so only a single database entry may 

cause unintended failure.

If that fails, verify that your connection chain is intact. Failing that, feel

free to email me and ask for help.

== Cron ==

In order for PGGraph to continue to collect data about its client systems, the

data collector needs to be run on a regular basis. Thus, comes the cronjob.

Adding the cronjob is simple: as the pggraph user, add a line similar to the 

following to your crontab, and you're done.

{{{

    */10 * * * * /usr/bin/python /var/www/pggraph/_main/bridge.py /var/www/pggraph/your_install_dir/config.ini

    or

    5,15,25,35,45,55 * * * * /usr/bin/python /var/www/pggraph/_main/bridge.py /var/www/pggraph/your_install_dir/config.ini

}}}

Write out the crontab, and pggraph is now collecting data on a regular cycle.

== Apache ==

As pggraph contains a lot of very detailed information on the structure

of the clients' database, it's generally a bad idea for anyone who can type in

a URL to be able to access it.

From that, we come to require an authentication system to keep fingers and 

eyeballs from where they don't belong.

As we're using a Pg-backed authentication system in Apache, and as it's 

already fully set up, I won't go into any detail regarding installation or

configuration issues.

To set up the directory-specific authentication, you should copy and paste

the following configuration and modify it as needed to fit the new PGGraph

installation.

The file that needs to be altered is /etc/apache2/sites-enabled/default-ssl

Please back up this file prior to modification.

{{{

<Location /pggraph/your_install>

        AuthName "Pggraph authentication for: Your Installation"

        AuthType Basic

        Auth_PG_host localhost

        Auth_PG_port 5432

        Auth_PG_user pggraph

        Auth_PG_pwd 8sm4LA2

        Auth_PG_authoritative on

        Auth_PG_database pggraph_users

        Auth_PG_pwd_table client_users

        Auth_PG_hash_type MD5

        Auth_PG_uid_field user_name

        Auth_PG_pwd_field user_passwd

        Auth_PG_log_table client_access_log

        Auth_PG_log_uname_field login

        Auth_PG_log_date_field date

        Auth_PG_log_uri_field   request

        Auth_PG_log_addrs_field  ip_address

        AuthBasicAuthoritative Off

        Require valid-user

</Location>

}}}

Reload Apache via /etc/init.d/apache2 reload, and your new 

pggraph installation is now completed.