akumria / puppet-postgresql

Manage and install Postgresql databases and users

Puppet module for postgresql

Basic usage

To install the client software

class {'postgresql':  }

To specify a particular version

class {'postgresql':
    version => '9.1',
}

To install the server

class {'postgresql::server': }

By default, the system-wide locale is assumed to be en_US.UTF-8. If the locale is not installed or available, you can specify an alternative:

class { 'postgresql::server':
    locale => 'es_ES.UTF-8',
}

Again, a particular version

class {'postgresql::server':
	version => '9.1',
}

Listen on a specific post / IP address

class {'postgresql::server':
	listen => ['192.168.0.1', ],
	port   => 5432,
}

To allow a remote host to connect to the server, now that you are listening on the Internet.

class {'postgresql::server':
    listen => ['192.168.0.1', ],
    port   => 5432,
    acl    => ['host all all 192.168.0.2/32 md5', ],
}

Refer to the pg_hba.conf docs for the specifics of what each possible ACL field can be set to. In some situations you made need to insert the ACL in front of any existing one. The variable preacl exists for this purpose.

To create a database owned by a user

postgresql::db { 'myuser':
    password => 'mypassword',
}

This will create myuser and then create a database called myuser which will owned by myuser. You can override the default locale and encoding and, if required, specify a different owner. For example:

postgresql::db { 'mydatabase':
    owner    => 'myuser',
    password => 'mypassword',
    locale   => 'en_AU.UTF-8',
    encoding => 'C',
}

A common scenario is installing PostGIS, and then having a database created with those features enabled. Postgresql does this with the concept of database templates. By default template0 is used as that allows us to override the locale and encoding if required. To create a database from a different template:

postgresql::db { 'mynextdb':
    owner    => 'anotheruser',
    password => 'anotherpass',
    template => 'template_postgis',
}

Read on, if your specific setup does not fall within this (admittedly simple) framework.

Create a user

This creates a role in the database cluster, by default the user is able to login and will inherit the permissions of any groups it is a member of.

pg_user {'pguser':
	ensure   => present,
	password => 'pgpassword',
}

To create users without passwords (such as for peer/ident authentication), simply leave off the 'password' key.

You can also modify other attributes like whether the user can create databases (createdb), create other roles (createrole) or is the superuser (superuser).

For example:

pg_user {'mighty_pguser':
    ensure     => present,
    password   => 'themightyone',
    createdb   => true,
    createrole => true,
}

Create a database

This creates a database and adds a dependancy relationship to the user

pg_database {'pgdb':
	ensure   => present,
	owner    => 'pguser',
	require  => Pg_user['pguser'],
}

The default is UTF-8 and en_US.UTF-8 for English. If required, you can also specify both the locale and encoding of a database.

As well, you can specify the base template

pg_database {'pgdb':
	ensure   => present,
	owner    => 'pguser',
	encoding => 'UTF8',
	locale   => 'de_DE.UTF-8',
	require  => Pg_user['pguser'],
	template => 'template1',
}

SSL Notes

SSL support is disabled by default. If you enable it, ensure that you have the appropriate server and root certificates, keys and revocations files already in place otherwise Postgresql will not start.

Additionally note that only Postgresql 9.2 (and later) allow you to configure the SSL files that the server will look for. Prior versions have hardcoded defaults.

Notes

This module will not (yet) update either the user or database once they have been initially created. i.e. changing the login permission of a user does not work. Nor does changing the locale of an existing database.

With Puppet version less than 3.0, you must enable pluginsync, so that the custom types are sent to each client.

Contributors

• Anand Kumria (@akumria)
• Federico Maggi
• Joe Topjian
• Stephan Hochdörfer
• Marcello Barnaba
• Ramón Cahenzli
• Bert Hajee
• Patrick Hemmer
• Marcello Barnaba

Copyright and License

Copyright 2012 Linuxpeak Pty Ltd.

This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see http://www.gnu.org/licenses/.

puppet-postgresql's Issues

String.each doesn't work anymore in Ruby 1.9

In ruby 1.9 the String doesn't have the method each. Resulting in the message

Could not evaluate: undefined method `each' for "1\n":String

Working with ACL

Hi,
is it possible to disable or overwrite some rule in Postgresql ACL?

Let me explain. I've created puppet-file with following lines

class { 'postgresql::server':
    locale => 'ru_RU.UTF-8',
    version => '9.1',
    listen => ['localhost', ],
    port   => 5432,
    acl   => ['local all all md5', ],
}

but on attempt to connect locally to db i saw following

vagrant@app:/etc/puppet$ psql -U db_user -W
Password for user db_user: 
psql: FATAL:  Peer authentication failed for user "db_user"

Article (http://blog.deliciousrobots.com/2011/12/13/get-postgres-working-on-ubuntu-or-linux-mint/) given me a hint to use acl like in my config, but required line

local all all md5

was added after default line

local   all             all                                     ident

and this rule overrides my config lines, placed below this line.

I see following ways to solve this problem

• Use only defined in puppet-config configuration data (reset default config)
• Perform search for possible conflicting lines and comment it (too hard to do imho)
• Add puppet-config lines at top of the pg_hba file

Or maybe you have any other idea?

Allow template to be specified when creating a database

At the moment, the template is hard-coded to be template0:
https://github.com/akumria/puppet-postgresql/blob/master/lib/puppet/provider/pg_database/debian_postgresql.rb#L11

Could this be altered so that an alternative template can be passed?

Puppet module contains Vim Swap files

The Puppet module on http://forge.puppetlabs.com/akumria/postgresql/1.1.0.tar.gz contains the following Vim swap files:

• ./manifests/.db.pp.swp
• ./manifests/.server.pp.swp
• ./lib/puppet/type/.pg_database.rb.swp
• ./lib/puppet/provider/pg_database/.debian_postgresql.rb.swp

module version error at puppetlabs?

I installed your module from puppetlabs and it is advertised as (v1.1.1)
$ puppet install module akumria-postgresql

However your git repo claims v1.1.1 implementes the $preacl features which you do not get from the puppetlabs downloaded version.

Can you fix this misalignment?

Invalid configuration option when using Postgresql 9.3.5

Error from puppet logs:
LOG: unrecognized configuration parameter "unix_socket_directory" in file "/etc/postgresql/9.3/main/postgresql.conf" line 71

When I compare to my locally installed postgresql.conf file, it looks like that's now supposed to be unix_socket_directories ( as of 9.3 ), with a comma separated list of directories as the value.

I think it might be possible to have the config file use a conditional to use one or the other depending on the version of postgresql being installed. Would you like me to fork and see if I can create a solution, and PR it?

pg_user need require package['postgres']

# postgresql
class { 'postgresql::server':
  version => '9.1',
}
pg_user { $postgresql['user']:
  ensure   => present,
  password => $postgresql['password'],
  createdb   => true,
  createrole => true
}

error:

err: /Stage[main]//Pg_user[root]: Could not evaluate: Execution of '/bin/su - postgres -c psql --quiet -A -t -c "select 1 from pg_roles where rolname = 'root';"' returned 1: Unknown id: postgres

When i first execute the puppet script. I got this error message.

And the second gonna be ok.

So, must ensure pg_user is after postgres::server installed.

Maybe should add require => package['postgres'] to fix it.

Unknown id: postgres

Here is a screenshot of my error: http://d.pr/i/KeWs

err: /Stage[main]//Pg_user[pyrocms]: Could not evaluate: Execution of '/bin/su - postgres -c psql --quiet -A -t -c "select 1 from pg_roles where rolname = 'pyrocms';"' returned 1: Unknown id: postgres

notice: /Stage[main]//Pg_database[pyrocms]: Dependency Pg_user[pyrocms] has failures: true
warning: /Stage[main]//Pg_database[pyrocms]: Skipping because of failed dependencies
notice: Finished catalog run in 0.51 seconds

Any ideas whats up here?

Declaring users is silently ignored

We declared this user in our manifest:

pg_user {'jenkins':
name => 'jenkins',
password => 'jenkins',
ensure => 'present',
createdb => 'true',
createrole => 'true',
superuser => 'true',
}

But the role is never created. Running with --debug shows nothing about creating PostgreSQL users, but the rest of the PostgreSQL instructions (ensuring the server is up, rewriting pg_hba.conf etc.) works.

How could we debug this issue further?

How to get template0 and template1 created with UTF-8 encoding

Am having a few problems getting the default encoding set correctly. After installation, the template databases are created with encoding::

$ sudo -u postgres psql --list
                                     List of databases
    Name     |    Owner    | Encoding  |   Collate   |    Ctype    |   Access privileges   
-------------+-------------+-----------+-------------+-------------+-----------------------
 postgres    | postgres    | SQL_ASCII | C           | C           | 
 template0   | postgres    | SQL_ASCII | C           | C           | =c/postgres          +
             |             |           |             |             | postgres=CTc/postgres
 template1   | postgres    | SQL_ASCII | C           | C           | =c/postgres          +
             |             |           |             |             | postgres=CTc/postgres

As far as I can tell, the default encoding is taken from your locale. However, this doesn't seem to be working in my case as my locale settings are all UTF-8::

$ locale
LANG=en_GB.utf8
LANGUAGE=
LC_CTYPE="en_GB.utf8"
LC_NUMERIC="en_GB.utf8"
LC_TIME="en_GB.utf8"
LC_COLLATE="en_GB.utf8"
LC_MONETARY="en_GB.utf8"
LC_MESSAGES="en_GB.utf8"
LC_PAPER="en_GB.utf8"
LC_NAME="en_GB.utf8"
LC_ADDRESS="en_GB.utf8"
LC_TELEPHONE="en_GB.utf8"
LC_MEASUREMENT="en_GB.utf8"
LC_IDENTIFICATION="en_GB.utf8"
LC_ALL=

Strangely when I run apt-get install postgresq-9.1 manually, the database are created correctly. Any idea what the problem is?

Creating users or databases doesn't work

My config is as follows:

class {'postgresql': }
class {'postgresql::server':
  listen => ['localhost'],
}
pg_user { 'foo':
  ensure   => present,
  password => 'foo',
}
pg_database { 'foo':
  ensure  => present,
  owner   => 'foo',
  require => Pg_user['foo'],
}

The postgresql server and client are installed as expected, but the user and database are not created. I'm unsure how to debug this.

postgresql using puppet

Hi,

I have cloned this into my /etc/puppet/modules/puppet-postgresql and i have changed to postgresql and my site.pp is under /etc/puppet/manifests/site.pp it has

node default {
class {'postgresql':
version => '9.1',
}
}
I am getting the follwoing error when i run puppet agent -t from my puppet agent

Request you to look into this as i am trying hard to determine what causing this error because when i tried it in redhat it said unsupported platform and now i am trying it in ubuntu