Fetching contributors…
Cannot retrieve contributors at this time
247 lines (168 sloc) 11.7 KB

postgresql cookbook

Build Status Cookbook Version

Installs and configures PostgreSQL as a client or a server.



  • Amazon Linux
  • Debian 7+
  • Ubuntu 14.04+
  • Red Hat/CentOS/Scientific 6+
  • Fedora

PostgreSQL version

We follow the currently supported versions listed on

The earliest supported version is currently:

  • 9.3 (9.3.19)


  • Chef 12.14+


  • openssl
  • build-essential

Deprecation notice for pg_hba.conf

Updating the pg_hba configuration can now be done with the postgresql_access resource which is documented below. There is a backward-compatible migration left in the server_conf recipe, but it will be removed in the next major release.

For each of the node['postgresql']['pg_hba'] hashes, you want to make a corresponding postgresql_access resource like the following example:

# What used to be this:
default['postgresql']['pg_hba'] = {
  type: 'local',
  db: 'all',
  user: 'postgres',
  addr: nil,
  method: 'ident'

# Is now this:
postgresql_access 'local_postgres_superuser' do
  access_type 'local'
  access_db 'all'
  access_user 'postgres'
  access_addr nil
  access_method 'ident'

Note: The default notification for the new postgresql_access resource is now :reload which is the recommended method of notifying PostgreSQL of access changes without requiring a full database restart. Before, the access template would defer to the notification method specified by node['postgresql']['server']['config_change_notify']


The following attributes are set based on the platform, see the attributes/default.rb file for default values.

  • node['postgresql']['version'] - version of postgresql to manage
  • node['postgresql']['dir'] - home directory of where postgresql data and configuration lives.
  • node['postgresql']['client']['packages'] - An array of package names that should be installed on "client" systems.
  • node['postgresql']['server']['packages'] - An array of package names that should be installed on "server" systems.
  • node['postgresql']['server']['config_change_notify'] - Type of notification triggered when a config file changes.
  • node['postgresql']['contrib']['packages'] - An array of package names that could be installed on "server" systems for useful sysadmin tools.
  • node['postgresql']['enable_pgdg_apt'] - Whether to enable the apt repo by the PostgreSQL Global Development Group, which contains newer versions of PostgreSQL.
  • node['postgresql']['enable_pgdg_yum'] - Whether to enable the yum repo by the PostgreSQL Global Development Group, which contains newer versions of PostgreSQL.
  • node['postgresql']['initdb_locale'] - Sets the default locale for the database cluster. If this attribute is not specified, the locale is inherited from the environment that initdb runs in. Sometimes you must have a system locale that is not what you want for your database cluster, and this attribute addresses that scenario. Valid only for EL-family distros (RedHat/Centos/etc.).

The following attributes are generated in recipe[postgresql::server].


The postgresql.conf file is dynamically generated from attributes. Each key in node['postgresql']['config'] is a postgresql configuration directive, and will be rendered in the config file. For example, the attribute:

node['postgresql']['config']['listen_addresses'] = 'localhost'

Will result in the following line in the postgresql.conf file:

listen_addresses = 'localhost'

The attributes file contains default values for Debian and RHEL platform families (per the node['platform_family']). These defaults have disparity between the platforms because they were originally extracted from the postgresql.conf files in the previous version of this cookbook, which differed in their default config. The resulting configuration files will be the same as before, but the content will be dynamically rendered from the attributes. The helpful commentary will no longer be present. You should consult the PostgreSQL documentation for specific configuration details.

For values that are "on" or "off", they should be specified as literal true or false. String values will be used with single quotes. Any configuration option set to the literal nil will be skipped entirely. All other values (e.g., numeric literals) will be used as is. So for example:

node.default['postgresql']['config']['logging_collector'] = true
node.default['postgresql']['config']['datestyle'] = 'iso, mdy'
node.default['postgresql']['config']['ident_file'] = nil
node.default['postgresql']['config']['port'] = 5432

Will result in the following config lines:

logging_collector = 'on'
datestyle = 'iso,mdy'
port = 5432

(no line printed for ident_file as it is nil)

Note that the unix_socket_directory configuration was renamed to unix_socket_directories in Postgres 9.3 so make sure to use the node['postgresql']['unix_socket_directories'] attribute instead of node['postgresql']['unix_socket_directory'].



This resource manages postgresql extensions with a given database to ease installation/removal. It uses the name of the resource in the format database/extension to determine the database and extention to install.


  • create - (default) Creates an extension in a given database
  • drop - Drops an extension from the database


Name Types Description Default Required?
database String Name of the database to install the extention into Name of resource yes
extention String Name of the extention to install the database Name of resource yes


To install the adminpack extension:

# Add the contrib package in Ubuntu/Debian
package 'postgresql-contrib-9.6'

# Install adminpack extension
postgresql_extension 'postgres/adminpack'


This resource uses the accumulator pattern to build up the pg_hba.conf file via chef resources instead of piling on a mountain of chef attributes to make this cookbook more reusable. It directly mirrors the configuration options of the postgres hba file in the resource and by default notifies the server with a reload to avoid a full restart, causing a potential outage of service. To revoke access, simply remove the resource and the access change won't be computed into the final pg_hba.conf


  • grant - (default) Creates an access line inside of pg_hba.conf


Name Types Description Default Required?
name String Name of the access resource, this is left as a comment inside the pg_hba config Resource name yes
source String The cookbook template filename if you want to use your own custom template 'pg_hba.conf.erb' yes
cookbook String The cookbook to look in for the template source 'postgresql' yes
comment String, nil A comment to leave above the entry in pg_hba nil no
access_type String The type of access, e.g. local or host 'local' yes
access_db String The database to access. Can use 'all' for all databases 'all' yes
access_user String The user accessing the database. Can use 'all' for any user 'all' yes
access_addr String, nil The address(es) allowed access. Can be nil if method ident is used since it is local then nil yes
access_method String Authentication method to use 'ident' yes
notification Symbol How to notify Postgres of the access change. :reload yes


To grant access to the postgresql user with ident authentication:

postgresql_access 'local_postgres_superuser' do
  comment 'Local postgres superuser access'
  access_type 'local'
  access_db 'all'
  access_user 'postgres'
  access_addr nil
  access_method 'ident'

This generates the following line in the pg_hba.conf:

# Local postgres superuser access
local   all             postgres                                ident

Note: The template by default generates a local access for Unix domain sockets only to support running the SQL execute resources. In Postgres version 9.1 and higher, the method is 'peer' instead of 'ident' which is identical. It looks like this:

# "local" is for Unix domain socket connections only
local   all             all                                     peer



Includes the client recipe.


Installs the packages defined in the node['postgresql']['client']['packages'] attribute.


Includes the server_debian or server_redhat recipe to get the appropriate server packages installed and service managed. Also manages the configuration for the server:

  • generates a strong default password (via openssl) for postgres
  • sets the password for postgres
  • manages the postgresql.conf file.
  • manages the pg_hba.conf file.


On systems that need to connect to a PostgreSQL database, add to a run list recipe[postgresql] or recipe[postgresql::client].

On systems that should be PostgreSQL servers, use recipe[postgresql::server] on a run list. This recipe does set a password for the postgres user. If you're using chef server, if the attribute node['postgresql']['password']['postgres'] is not found, the recipe generates a random password and performs a (TODO: This is broken, as it disables the password.) If you're using chef-solo, you'll need to set the attribute node['postgresql']['password']['postgres'] in your node's json_attribs file or in a role.

On Debian family systems, SSL will be enabled, as the packages on Debian/Ubuntu also generate the SSL certificates. If you use another platform and wish to use SSL in postgresql, then generate your SSL certificates and distribute them in your own cookbook, and set the node['postgresql']['config']['ssl'] attribute to true in your role/cookboook/node.

On server systems, the postgres server is restarted when a configuration file changes. This can be changed to reload only by setting the following attribute:

node['postgresql']['server']['config_change_notify'] = :reload


Copyright 2010-2017, Chef Software, Inc.

Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
See the License for the specific language governing permissions and
limitations under the License.