My attempt at a Zenoss / PostgreSQL how-to

I have been quite dismayed for the last day or so, on how to configure Zenoss to query a PostgreSQL database.  There just isn't any documentation on any of the ZenPacks (they're all written by the same dude: Egor Puzanov).  Additionally:

- I'm new to Zenoss.
- I'm new to PostgreSQL.
- ODBC is for Windows.

The complete lack of documentation hurts (just in case you're listening, Zenoss).  Finally, after taking a break from my desk and going to lunch, I hit this "gem" on my iPhone: http://community.zenoss.org/thread/11091.  Again, written by Egor.  It's a start, even if it's still a tad undercooked, so I thought maybe writing this up as I went along would help make things more clear.

Let's start with how Zenoss is installed:

lsb_release -d

Description:    Ubuntu 10.04.2 LTS

cat /etc/apt/sources.list.d/zenoss

deb http://dev.zenoss.org/deb main stable

I am currently running Zenoss 3.0.3 though as I write this I will upgrade to 3.1.0 by running "apt-get upgrade."  To learn how to install Zenoss using the stack, see http://community.zenoss.org/docs/DOC-2633

1: Install basic software by running "apt-get install unixodbc odbc-postgresql"
2: Create two "/etc/odbc*.ini" files.  At this stage, I'm a little hazy on all of the sources I used to get me through this step.  Sorry dudes, you deserve credit.

NOTE: you *can* skip steps 2b through 5, if you wish.  The Postgres ODBC plugin will read the zPgSqlConnectionString, zPgSqlUsername and zPgSqlPassword settings anyway, as it maps your installed databases.  I found these steps to be instrumental in understanding how all this works.

2a: /etc/odbcinst.ini contains information about which drivers are installed.  Create it by running odbcinst -i -d -f /usr/share/psqlodbc/odbcinst.ini.template.  The resulting file will look like this:

[PostgreSQL ANSI]
Description = PostgreSQL ODBC driver (ANSI version)
Driver = /usr/lib/odbc/psqlodbca.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
Debug = 0
CommLog = 1
UsageCount = 1

[PostgreSQL Unicode]
Description = PostgreSQL ODBC driver (Unicode version)
Driver = /usr/lib/odbc/psqlodbcw.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
Debug = 0
CommLog = 1
UsageCount = 1

2b: /etc/odbc.ini contains system-wide DSNs.  Note that the driver name matches the "PostgreSQL ANSI" section in /etc/odbcinst.ini:

[DBQA]
Description = PostgreSQL
Driver = PostgreSQL ANSI
Trace = No
TraceFile = /var/log/psqlodbc-qa.log
Database = qa
Servername = db1.qa.company.com
UserName = pgquery
Password =
Port = 5432
ReadOnly = Yes
RowVersioning = No
ShowSystemTables = Yes
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
SSLmode = require
UsageCount = 1

This article helps a tad bit:  http://www.debian-administration.org/articles/496. 

3: TIME OUT!  Use SSL!

Because my apps run "in the cloud," I need to maintain a long distance relationship with my database servers.  Because I do not want to haul cleartext database traffic over the wire, I'll enable SSL.

http://www.postgresql.org/docs/8.4/static/ssl-tcp.html

How far you go in enabling SSL (verifying client certificates, etc.) is up to you.  In my case, I am content with using a plain username/password as long as my logins and data transfers are encrypted, because I am filtering packets based on source IP address and I have my allowed sources locked down to individual IPs.

In short, I ran (as the postgres user):

cd /mnt/postgres/data
openssl req -new -text -out server.req
openssl rsa -in privkey.pem -out server.key
openssl req -x509 -in server.req -text -key server.key -out server.crt
chmod og-rwx server.key
rm privkey.pem server.req

The server.key and server.crt files should be owned by the postgres user and readable by postgres only.


4: Create my "pgquery" role.

Again, I'm new to PostgreSQL.  Commands like "grant all privileges on *.* to pgquery@x" don't work.  But these commands did:

- log into postgres from localhost, as the "postgres" user.  Just run "psql."  This makes me cringe and I will tighten it down someday soon.
- \du shows all roles.  So does "select * from pg_roles";
- create a user by issuing the create role pgquery superuser login password 'password'; SQL statement.
- create a blank database for the pgquery user by running create database pgquery encoding 'UTF8';

See http://www.postgresql.org/docs/8.4/static/sql-createrole.html.

NOTE here: you must create a superuser role.  I tried it without superuser privileges and the SQL query that the Postgres ODBC plugin runs didn't work, as it didn't have access to the pg_authid view.

5. Once postgres is restarted and I have a new role, it's time to open up port 5432 to my monitoring station and connect using SSL.  I ran a packet sniff during the exchange to verify that my data is encrypted and that I see a certificate flying by.  I did. I tested by running:

isql DBQA pgquery (password)

Then I issued the "select * from pg_stat_activity;" SQL query.

The pg_stat tables are very helpful, and they are documented at http://www.postgresql.org/docs/8.4/static/monitoring-stats.html.

As a final note, see documentation about the pg_hba.conf file, which you can tweak to set up various levels of trust to different hosts.  An important thing to remember is that you can set different parameters based on whether you're using SSL.

http://www.postgresql.org/docs/8.4/static/auth-pg-hba-conf.html

I have a feeling that the database I inherited has had its pg_hba.conf file mangled horribly by my predecessor, so I will tackle this later.  Let's move on.

6: Recall that we are following http://community.zenoss.org/thread/11091), and we are now back on track.  According to this doc, I should be able to run:

#!/bin/bash

if [ $ZENHOME ]; then

  for i in ZenPacks.community.deviceAdvDetail-2.7.egg \
           ZenPacks.community.RDBMS-2.2.egg \
           ZenPacks.community.SQLDataSource-1.6.egg \
           ZenPacks.community.ZenODBC-3.2.egg \
           ZenPacks.community.PgSQLMon_ODBC-2.2.egg; do
    echo "######################################################" >> /tmp/whatwentwrong
    echo "installing $i" >> /tmp/whatwentwrong
    echo "######################################################" >> /tmp/whatwentwrong
    $ZENHOME/bin/zenpack -v1 --install $i >> /tmp/whatwentwrong 2>&1
    echo >> /tmp/whatwentwrong
    zopectl restart
  done

fi

You can use the output in /tmp/whatwentwrong if you need to ask someone in the Zenoss communities for help (and hey Zenoss guys, it would be VERY nice to be able to throw a wget line into that shell script).

7: Now it's time for the moment of truth, eh?  Log into your Zenoss web interface, then select your database server.  Open the Configuration properties and set your zPgSqlPassword, zPgSqlUsername and zPgSqlConnection String.  My string, in its entirety, is "Driver={PostgreSQL ANSI};Port=5432;SSLMode=require".  Don't forget to save your changes.

8: Assign the community.odbc.PgSqlDatabaseMap modeler plugin to your database server.  Click on the "Modeler Plugins" category.  Click on the greyed out "Add Fields" item in the middle of the right-hand side, then drag it over.  Again, don't forget to save your changes.

9: Model your device (click on the little wheel thing on the bottom left-hand corner of your browser window).  I had issues a few times with the modeling part.  I was finally able to deduce that the modeler plugin was connecting to my database just fine, but it had problems running this SQL query:

SELECT d.datname as dbname, u.rolname as contact, 'Ver.'||current_setting('server_version') as version, current_setting('block_size'
)::float as blocksize, t.spcname as setdbsrvinst, d.datallowconn::int as allowconn, CASE d.datistemplate WHEN True THEN 'PgSqlTemplate' ELSE 'PgSqlDatabase' END as type, pg_database_size(d.datname)::float as totalblocks FROM pg_database d, pg_authid u, pg_tablespace t WHERE d.datdba=u.oid AND d.dattablespace=t.oid;

If you have issues, keep trying to connect with isql and hammering away with that query until it works.  When all of this comes together, you will see your databases in the software category for your device in Zenoss.