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.

For clarity, the || || characters have been added to delineate between my
comments and what will be shown by the installer.

{{{

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.