Bandwidthd database option
I've been working on setting up bandwidthd using the database option using nanobsd on an Alix running 2.0.3. This is just a test on my home network that I'm considering for a much higher traffic Alix where I have a convenient Ubuntu box that can host the postgresql database and php servers instead of burdening the Alix with it. The following are my notes to help me when I migrate and also provide some information in case the maintainers are able to add the database option to the bandwidthd configuration gui on pfsense. How do I make edits to the bandwidthd.conf file on the pfsense box persist a gui sync?
/usr/local/bandwidthd/etc/bandwidthd.conf on the pfsense install needs to be modified with the added lines describing the postgresql database connection and sensor_id. Also, all options from the existing GUI page should be unchecked or blank except for the Enable bandwidthd checkbox. Don't forget to restart bandwidthd to load any changes, and be patient as it can take a while for the data to start propagating into the database. I found it best to set up the postgresql server first using its own sensor before trying to connect pfSense to it.
It would be nice to add the following fields to the bandwidthd GUI configuration page, only the single checkbox for enabling the database option would need be visible before being enabled. I don't have the first clue how this gets translated to actual gui elements or back to the actual config file:
<field><fielddescr>Enable database option</fielddescr> <fieldname>enabledatabase</fieldname> <type>checkbox</type> <description>Check to enter postgresql credentials for dynamically creating graphs from an external database</description></field> <field><fielddescr>PostgreSQL User</fielddescr> <fieldname>postgresqluser</fieldname> <description>The user role to authenticate with on a postgresql server.</description> <type>input</type> <default_value>bandwidthdpgsql</default_value></field> <field><fielddescr>PostgreSQL Password</fielddescr> <fieldname>postgresqlpass</fieldname> <description>The password used to connect to a postgresql server.</description> <type>password</type></field> <field><fielddescr>Database Name</fielddescr> <fieldname>postgresqldbname</fieldname> <description>The name of a bandwidthd database hosted on a postgresql server.</description> <type>input</type> <default_value>bandwidthdpgsqldb</default_value></field> <field><fielddescr>Host</fielddescr> <fieldname>postgresqlhost</fieldname> <description>The postgresql hosting server for bandwidthd to connect to. Don't forget to add pfSense to pg_hba.conf on the host server.</description> <type>input</type></field> <field><fielddescr>Sensor Name</fielddescr> <fieldname>sensorid</fieldname> <description>A nice name for this sensor to display in the bandwidthd web interface.</description> <type>input</type> <default_value>pfsense</default_value></field>
The following is what needs to be added to the bandwidthd.conf file on pfsense in order to enable database access:
################################################### # Options for postgresql database logging. # Standard postgres connect string, just like php, see postgres docs for # details ##pgsql_connect_string "user = someuser dbname = mydb host = localhost" pgsql_connect_string "user = postgresql_user password = postgresql_pass dbname = postgresql_dbname host = postgresql_host" # Arbitrary sensor name, I recommend the sensors fully qualified domain # name #sensor_id "sensor1.localhost.localdomain" sensor_id "sensor_id"
On the postgresql server receiving pfsense data and also hosting the php website, the following helps me to remember a few important items. Make sure you've got the local "sensor" working before trying to connect the pfsense one to it. The following was on an install of Ubuntu 12.04.
tail -f /var/log/messages /var/log/syslog # is your friend, use it to see what issues you may have with the database connection such as user or database doesn't exist.
/etc/init.d/apache2 restart # handy for making sure the gui pages load properly
/etc/init.d/postgresql restart # handy for making sure modifications to the database config get loaded
/etc/init.d/bandwidthd restart # handy for making sure modifications to the bandwidhtd.config file get loaded
The pgadmin3 package made troubleshooting database issues such as user roles and such much easier with a gui as I had no prior postgresql experience.
Don't forget to edit /etc/postgresql/9.1/main/pg_hba.conf to enable exterior access from specific connections to the database.
/etc/bandwidthd/bandwidthd.conf # is the Ubuntu default config location for the bandwidthd-pgsql package install.
/etc/bandwidthd/debian-db.php # will provide database credentials to the php web
Don't forget to alias the php website for Apache2 to serve; on Ubuntu's install point the alias at /var/lib/bandwidthd/htdocs
I attempted to modify the config files for the bandwidthd packages on github here:
Go easy on me, this is my first time for everything; github, php, xml, etc. I really don't want to waste to much of some maintainers time so guidance as to how to test my changes before they have to see it would be helpful. There were a few questions that I was unable to figure out as follows:
How do I make the visibility of a group of fields contingent upon the state of a checkbox?
How can I pull the system's hostname like the one displayed in the System Information widget into a default value for a field?
How do I test my changes, do I follow these wiki instructions, can I start at step 4 or do I have to setup a complete server? It doesn't look like there's an easy way to pull from my own github page from the pfsense box as these instructions say not to use a full url. http://doc.pfsense.org/index.php/Creating_Your_Own_Package_Repository
How do I know when the changes are good enough to give back to the project?
How give the changes back to the project if they actually work? Looks like trying to follow this thread will get me started: http://forum.pfsense.org/index.php/topic,44686.msg232239.html
A colleague got this working the other day and submitted the changes, so there is a new version of bandwidthd package available now - 2.0.1_5 pkg v.0.1
We seem to be able to run with it generating local graphs and sending to the database, so no validation has been put in to restrict that. Although most people will only want to do one or the other.
Just a note regarding the operation, I just pulled
2.1-RC1 (i386) built on Wed Aug 21 01:29:33 EDT 2013 FreeBSD 8.3-RELEASE-p9after first removing the bandwidthd package out of my older 2.0.3 installation. After working out my RRD issues (something about the database not converting properly, fixed with reset of my old data) I went to the bandwidthd menu to set it up again. Very cool to see the new fields there for database logging! Anyway, unchecking the enable bandwidthd box and clicking save rendered the menu URL```
I was able to repeat the problem a couple of times. I don't think this is a big deal, I just prefer to enter all of my settings with the service disabled while I work out all the niggles. arri