homeblogmodule of week inkling postgresql postgresql management

Module of the Week: inkling/postgresql – PostgreSQL Management

EDIT 10/24/12: The inkling/postresql module is now owned by Puppet Labs, and has been moved to puppetlabs/postgresql. You can contribute to the module on GitHub here.
Purpose Manage PostgreSQL servers, databases, and users
Module Previously inkling/postgresql, now puppetlabs/postgresql
Puppet Version 2.7+ & PE 2.0+
Platforms Tested on RHEL5, RHEL6, Debian6, Ubuntu 10.04
PostgreSQL is a powerful, high-performance, free, open-source relational database server. It hasn’t always enjoyed quite as much popularity as its cousin, MySQL; MySQL is enormously popular, as evidenced by its inclusion in the ubiquitous LAMP (Linux-Apache-MySQL-PHP) web development stack. However, these days there seems to be some increasing momentum behind PostgreSQL in many circles. At Puppet Labs, we are starting to use it more heavily—in fact, it’s a prerequisite for our new PuppetDB product. With that in mind, it seemed important for us to make sure that there was a Puppet module out that made PostgreSQL as easy to manage with Puppet as MySQL is. We searched around on the Puppet Forge to see if anyone had undertaken this yet, and found several useful Postgres modules—but it was important to us that the module API would be familiar to users of the puppetlabs/mysql module. We were particularly impressed with the functionality offered by the inkling/puppet-postgresql module, developed by Kenn Knowles of Inkling Systems, so we reached out to Kenn to see if he’d be amenable to us helping to refactor the module to leverage his existing functionality with an API similar to the puppetlabs/msyql module. He was, so, we did! So here’s why you should check out the new 0.2.0 release of the inkling/postgresql module:
  • Provides resource types and parameters that should be fairly familiar to users of the puppetlabs/mysql module
  • Allows you to manage PostgreSQL packages and services on several operating systems
  • Supports basic management of PostgreSQL databases and users
  • Optional support for managing firewall for Postgres ports on RedHat-based distros
  • Supports basic management of common security settings (in postgresql.conf and pg_hba.conf)
  • Works with versions of PostgreSQL from 8.1 through 9.1.

Installing the module

Complexity Easy
Installation Time 2 minutes
Installing the PostgreSQL module is as simple as using the Puppet module tool; available in Puppet 2.7.14+ and Puppet Enterprise 2.5+); also available as a rubygem:
$ puppet module install puppetlabs-postgresql
Preparing to install into /etc/puppet/modules ...
Downloading from http://forge.puppetlabs.com ...
Installing -- do not interrupt ...
└─┬ puppetlabs-postgresql (v1.0.0)
  ├── puppetlabs-firewall (v0.0.4)
  └── puppetlabs-stdlib (v2.4.0)
Alternatively, you can install the postgresql module manually:
$ cd /etc/puppet/modules/

$ wget http://forge.puppetlabs.com/system/releases/p/puppetlabs/puppetlabs-postgresql-1.0.0.tar.gz

$ tar zxvf puppetlabs-postgresql-1.0.0.tar.gz
$ mv puppetlabs-postgresql-1.0.0 postgresql
$ wget http://forge.puppetlabs.com/system/releases/p/puppetlabs/puppetlabs-stdlib-2.4.0.tar.gz
$ tar zxvf puppetlabs-stdlib-2.4.0.tar.gz
$ mv puppetlabs-stdlib-2.4.0 stdlib
$ wget http://forge.puppetlabs.com/system/releases/p/puppetlabs/puppetlabs-firewall-0.0.4.tar.gz
$ tar zxvf puppetlabs-firewall-0.0.4.tar.gz
$ mv puppetlabs-firewall-0.0.4 firewall
$ rm puppetlabs-postgresql-1.0.0.tar.gz puppetlabs-firewall-0.0.4.tar.gz puppetlabs-stdlib-2.4.0.tar.gz

Resource Overview

Here’s a quick overview of some of the classes defined by the module. We’ll dig in a little more deeply with examples and parameter info in a moment.

postgresql class

This class can be used to manage the basic postgresql client packages (which include the psql command line tool and other utilities).

postgresql::server class

This class is used for managing the actual PostgreSQL server. It manages both the packages and the service.

postgresql::database, postgresql::database_user, postgresql::database_grant classes

These are some fairly low-level classes that you can use if you need fine-grained control over a postgres database instance, user, or roles. In many cases, you won’t need to use these thanks to our higher-level wrapper class: postgresql::db.

postgresql::db class

As mentioned above, this is a high-level wrapper class that allows you to create a database, user, and the appropriate roles/permissions all in one shot. Hopefully this will cover most typical use cases!

postgresql_password puppet function

This function can be used to generate a postgres encrypted password. You can call it from your production manifests if you don’t mind them containing the clear text versions of your passwords, or you can call it from the command line and then copy and paste the encrypted password into your manifest:
$ puppet apply --execute 'notify { "test": message => postgresql_password("username", "password") }'

postgres_default_version fact

The module provides a Facter fact that can be used to determine what the default version of postgres is for your operating system / distribution. Depending on the distribution, it might be 8.1, 8.4, 9.1, or possibly another version. This can be useful in a few cases, like when building path strings for the postgres directories.

Testing the module

There are two types of tests distributed with the module. The first set is the “traditional” Puppet manifest-style smoke tests. You can use these to experiment with the module on a virtual machine or other test environment, via puppet apply. You should see the following files in the tests directory:
  • init.pp: just installs the postgres client packages
  • server.pp: installs the postgres server packages and starts the service; configures the service to accept connections from remote machines, and sets the password for the postgres database user account to ‘postgres’.
  • postgresql_database.pp: creates a few sample databases with different character sets. Does not create any users for the databases.
  • postgresql_database_user.pp: creates a few sample users.
  • postgresql_database_grant.pp: shows an example of granting a privilege on a database to a certain user/role.
  • postgresql_db.pp: creates several test databases, and creates database user accounts with full privileges for each of them.
In addition to these manifest-based smoke tests, there are some ruby rspec tests in the spec directory. Kenn did something pretty unique and powerful with these tests—they run against a VirtualBox VM, so they are actually testing the live application of the module on a real, running system. You’ll need the vagrant gem and VirtualBox installed in order to run these tests. Then, just run rspec spec from the root directory of the module. Right now, these tests are only run against one operating system (Ubuntu 10.04), but we’d like to expand it to cover several others.

Configuring the module

Complexity Easy
Installation Time 10 minutes
The main configuration you’ll need to do will be around the postgresql::server class. The module provides reasonable defaults for all of the parameters, so your usage might be as simple as:
include postgresql::server
However, this will set Postgres up with fairly restrictive permissions with respect to who can connect and from where. Here’s another example that illustrates how you might set it up if you wanted it to be less restrictive:
class { 'postgresql::server':
    config_hash => {
        'ip_mask_deny_postgres_user' => '',
        'ip_mask_allow_all_users' => '',
        'listen_addresses' => '*',
        'manage_redhat_firewall' => true,
        'postgres_password' => 'postgres',
That all makes perfect sense, right? No? OK, well I suppose maybe it could use a tiny bit of explanation. Here are the options that you can set in the “config_hash” parameter:
Config Setting Default value Purpose
postgres_password undef By default, the “super user” account in the postgres database is a user called ‘postgres’, and this account does not have a password. If you provide this setting, the module will set the password for the ‘postgres’ user to your specified value.
listen_addresses localhost By default, the postgres server will only accept connections from localhost. If you’d like to be able to connect to postgres from remote machines, you can override this setting. A value of ‘*’ will tell postgres to accept connections from any remote machine. Alternately, you can specify a comma-separated list of hostnames or IP addresses. (For more info, have a look at the postgresql.conf file from your system’s postgres package).
manage_redhat_firewall false Many RedHat-based distros ship with a fairly restrictive firewall configuration which will block the port that postgres tries to listen on. If you’d like for the puppet module to open this port for you (using the puppetlabs-firewall module), change this value to true.
ip_mask_allow_all_users By default, postgres does not allow any database user accounts to connect via TCP from remote machines. If you’d like to allow them to, you can override this setting. You might set it to “” to allow database users to connect from any remote machine, or “” to allow connections from any machine on your local 192.168 subnet.
ip_mask_deny_postgres_user Sometimes it can be useful to block the superuser account from remote connections if you are allowing other database users to connect remotely. This lessens your security risk. Set this setting to an IP and mask for which you want to deny connections by the postgres superuser account. So, e.g., the default value of “” will match any remote IP and deny access, so the postgres user won’t be able to connect remotely at all. Conversely, a value of “” would not match any remote IP, and thus the deny rule will not be applied and the postgres user will be allowed to connect.
pg_hba_conf_path If for some reason your system stores the postgres pg_hba.conf file in a non-standard location, you can override the path here.
postgresql_conf_path If for some reason your system stores the postgres postgresql.conf file in a non-standard location, you can override the path here.
Whew! Hopefully you won’t need to use all of those settings, but they should provide you with a decent amount of control over your security settings if you need it. After you’ve got your manifest set up the way you want it, you can test out your settings from the command line with some commands like these:
$ psql -h localhost -U postgres
$ psql -h my.postgres.server -U
If you get an error message from these commands, it means that your permissions are set in a way that restricts access from where you’re trying to connect. That might be a good thing or a bad thing, depending on your goals.

Example usage

Alright, let’s get on to the good stuff. What good is a database server without a database on it? Chances are, if you’re installing postgres on one of your machines, it’s because you have an application that needs a postgres database. So let’s tell Puppet to take care of that, too! Here’s an example of how we might use the postgres puppet module to set up a database for PuppetDB (this assumes you’ve already got the postgresql::server set up to your liking in your manifest, as discussed above):
postgresql::db{ 'puppetdb':
  user          => 'puppetdb',
  password      => 'puppetdb',
  grant         => 'all',
And.... we’re done. You can pat yourself on the back and head home early for the day. :) The snippet above will create a database called ‘puppetdb’ on your postgres server. Then it will create a database user account called ‘puppetdb’ with a password of ‘puppetdb’, and grant it ALL available privileges on the puppetdb database. Now you just need to plunk these database name / username / password values into your PuppetDB config files, and you are good to go. If you need more fine-grained control, there’s plenty more under the hood. We’ve tried to document it fairly well in the manifest/**/*.pp and test/**/*.pp files, so check them out. Also, feel free to ping some one on the Puppet-user’s mailing list or the #puppet channel on IRC if you need help.

Grand Plans for The Future

This is a very early release of this module. We think it has most of the functionality that most users will need, but there’s still a lot more we’d like to do. A few highlights:
    • Improve firewall support and make it work on debian-based distros
    • Continue to work towards a common API with the puppetlabs-mysql module
    • At some point, maybe even create a simple “relational database” resource type that has both a mysql provider and a postgres provider. This new type would leverage the existing mysql/postgres modules to allow you to switch between databases with just a one-line edit to your manifests.
    • Port a lot of the current Postgres functionality over to Ruby instead of using Exec’s. This release was mostly about putting together a decent end-user API that felt similar to the one from the mysql module; now that we’ve got that, we should be able to port some of the implementation over to Ruby without breaking backwards compatibility. This will give us a good deal more control over the behavior and allow us to provide even more functionality.
If you’re interested in contributing, we’re excited to collaborate! You can check out the git repository and submit code, come talk to us on the #puppet channel on IRC (freenode), or send Ryan an e-mail. We look forward to hearing from you.


Well, we hope you enjoyed this brief tour of the inkling/postgresql puppet module. If you need to set up and manage a postgres database for your applications, it should make your job a breeze. Please let us know if you find it to be missing any features that you need, or if you have any other suggestions on how it could be improved. Thanks once again to Kenn Knowles and Inkling Systems for the great work on this module! Learn More: