5 1. [Module Description - What does the module do?](#module-description)
6 2. [Setup - The basics of getting started with postgresql module](#setup)
7 * [What postgresql affects](#what-postgresql-affects)
8 * [Getting started with postgresql](#getting-started-with-postgresql)
9 3. [Usage - Configuration options and additional functionality](#usage)
10 * [Configure a server](#configure-a-server)
11 * [Create a database](#create-a-database)
12 * [Manage users, roles, and permissions](#manage-users-roles-and-permissions)
13 * [Manage ownership of DB objects](#manage-ownership-of-db-objects)
14 * [Override defaults](#override-defaults)
15 * [Create an access rule for pg_hba.conf](#create-an-access-rule-for-pg_hbaconf)
16 * [Create user name maps for pg_ident.conf](#create-user-name-maps-for-pg_identconf)
17 * [Validate connectivity](#validate-connectivity)
18 4. [Reference - An under-the-hood peek at what the module is doing and how](#reference)
20 * [Defined Types](#defined-types)
22 * [Functions](#functions)
23 5. [Limitations - OS compatibility, etc.](#limitations)
24 6. [Development - Guide for contributing to the module](#development)
25 * [Contributors - List of module contributors](#contributors)
27 8. [Contributors - List of module contributors](#contributors)
31 The postgresql module allows you to manage PostgreSQL databases with Puppet.
33 PostgreSQL is a high-performance, free, open-source relational database server. The postgresql module allows you to manage packages, services, databases, users, and common security settings in PostgreSQL.
37 ### What postgresql affects
39 * Package, service, and configuration files for PostgreSQL
41 * IP and mask (optional)
43 ### Getting started with postgresql
45 To configure a basic default PostgreSQL server, declare the `postgresql::server` class.
48 class { 'postgresql::server':
54 ### Configure a server
56 For default settings, declare the `postgresql::server` class as above. To customize PostgreSQL server settings, specify the [parameters](#postgresqlserver) you want to change:
59 class { 'postgresql::server':
60 ip_mask_deny_postgres_user => '0.0.0.0/32',
61 ip_mask_allow_all_users => '0.0.0.0/0',
62 ipv4acls => ['hostssl all johndoe 192.168.0.0/24 cert'],
63 postgres_password => 'TPSrep0rt!',
67 After configuration, test your settings from the command line:
70 psql -h localhost -U postgres
71 psql -h my.postgres.server -U
74 If you get an error message from these commands, your permission settings restrict access from the location you're trying to connect from. Depending on whether you want to allow connections from that location, you might need to adjust your permissions.
76 For more details about server configuration parameters, consult the [PostgreSQL Runtime Configuration documentation](http://www.postgresql.org/docs/current/static/runtime-config.html).
80 You can set up a variety of PostgreSQL databases with the `postgresql::server::db` defined type. For instance, to set up a database for PuppetDB:
83 class { 'postgresql::server':
86 postgresql::server::db { 'mydatabasename':
87 user => 'mydatabaseuser',
88 password => postgresql_password('mydatabaseuser', 'mypassword'),
92 ### Manage users, roles, and permissions
94 To manage users, roles, and permissions:
97 class { 'postgresql::server':
100 postgresql::server::role { 'marmot':
101 password_hash => postgresql_password('marmot', 'mypasswd'),
104 postgresql::server::database_grant { 'test1':
110 postgresql::server::table_grant { 'my_table of test2':
118 This example grants **all** privileges on the test1 database and on the `my_table` table of the test2 database to the specified user or group. After the values are added into the PuppetDB config file, this database would be ready for use.
120 ### Manage ownership of DB objects
122 To change the ownership of all objects within a database using REASSIGN OWNED:
125 postgresql::server::reassign_owned_by { 'new owner is meerkat':
127 old_owner => 'marmot',
128 new_owner => 'meerkat',
132 This would run the PostgreSQL statement 'REASSIGN OWNED' to update to ownership of all tables, sequences, functions and views currently owned by the role 'marmot' to be owned by the role 'meerkat' instead.
134 This applies to objects within the nominated database, 'test_db' only.
136 For Postgresql >= 9.3, the ownership of the database is also updated.
138 ### Override defaults
140 The `postgresql::globals` class allows you to configure the main settings for this module globally, so that other classes and defined resources can use them. By itself, it does nothing.
142 For example, to overwrite the default `locale` and `encoding` for all classes, use the following:
145 class { 'postgresql::globals':
147 locale => 'en_US.UTF-8',
150 class { 'postgresql::server':
154 To use a specific version of the PostgreSQL package:
157 class { 'postgresql::globals':
158 manage_package_repo => true,
162 class { 'postgresql::server':
166 ### Manage remote users, roles, and permissions
168 Remote SQL objects are managed using the same Puppet resources as local SQL objects, along with a [`connect_settings`](#connect_settings) hash. This provides control over how Puppet connects to the remote Postgres instances and which version is used for generating SQL commands.
170 The `connect_settings` hash can contain environment variables to control Postgres client connections, such as 'PGHOST', 'PGPORT', 'PGPASSWORD', and 'PGSSLKEY'. See the [PostgreSQL Environment Variables](http://www.postgresql.org/docs/9.4/static/libpq-envars.html) documentation for a complete list of variables.
172 Additionally, you can specify the target database version with the special value of 'DBVERSION'. If the `connect_settings` hash is omitted or empty, then Puppet connects to the local PostgreSQL instance.
174 You can provide a `connect_settings` hash for each of the Puppet resources, or you can set a default `connect_settings` hash in `postgresql::globals`. Configuring `connect_settings` per resource allows SQL objects to be created on multiple databases by multiple users.
177 $connection_settings_super2 = {
178 'PGUSER' => 'super2',
179 'PGPASSWORD' => 'foobar2',
180 'PGHOST' => '127.0.0.1',
182 'PGDATABASE' => 'postgres',
185 include postgresql::server
187 # Connect with no special settings, i.e domain sockets, user postgres
188 postgresql::server::role { 'super2':
189 password_hash => 'foobar2',
192 connect_settings => {},
195 # Now using this new user connect via TCP
196 postgresql::server::database { 'db1':
197 connect_settings => $connection_settings_super2,
198 require => Postgresql::Server::Role['super2'],
202 ### Create an access rule for pg_hba.conf
204 To create an access rule for `pg_hba.conf`:
207 postgresql::server::pg_hba_rule { 'allow application network to access app database':
208 description => 'Open up PostgreSQL for access from 200.1.2.0/24',
212 address => '200.1.2.0/24',
213 auth_method => 'md5',
217 This would create a ruleset in `pg_hba.conf` similar to:
220 # Rule Name: allow application network to access app database
221 # Description: Open up PostgreSQL for access from 200.1.2.0/24
223 host app app 200.1.2.0/24 md5
226 By default, `pg_hba_rule` requires that you include `postgresql::server`. However, you can override that behavior by setting target and postgresql_version when declaring your rule. That might look like the following:
229 postgresql::server::pg_hba_rule { 'allow application network to access app database':
230 description => 'Open up postgresql for access from 200.1.2.0/24',
234 address => '200.1.2.0/24',
235 auth_method => 'md5',
236 target => '/path/to/pg_hba.conf',
237 postgresql_version => '9.4',
241 ### Create user name maps for pg_ident.conf
243 To create a user name map for the pg_ident.conf:
246 postgresql::server::pg_ident_rule { 'Map the SSL certificate of the backup server as a replication user':
247 map_name => 'sslrepli',
248 system_username => 'repli1.example.com',
249 database_username => 'replication',
253 This would create a user name map in `pg_ident.conf` similar to:
256 #Rule Name: Map the SSL certificate of the backup server as a replication user
259 sslrepli repli1.example.com replication
262 ### Create recovery configuration
264 To create the recovery configuration file (`recovery.conf`):
267 postgresql::server::recovery { 'Create a recovery.conf file with the following defined parameters':
268 restore_command => 'cp /mnt/server/archivedir/%f %p',
269 archive_cleanup_command => undef,
270 recovery_end_command => undef,
271 recovery_target_name => 'daily backup 2015-01-26',
272 recovery_target_time => '2015-02-08 22:39:00 EST',
273 recovery_target_xid => undef,
274 recovery_target_inclusive => true,
275 recovery_target => 'immediate',
276 recovery_target_timeline => 'latest',
277 pause_at_recovery_target => true,
278 standby_mode => 'on',
279 primary_conninfo => 'host=localhost port=5432',
280 primary_slot_name => undef,
281 trigger_file => undef,
282 recovery_min_apply_delay => 0,
286 The above creates this `recovery.conf` config file:
289 restore_command = 'cp /mnt/server/archivedir/%f %p'
290 recovery_target_name = 'daily backup 2015-01-26'
291 recovery_target_time = '2015-02-08 22:39:00 EST'
292 recovery_target_inclusive = true
293 recovery_target = 'immediate'
294 recovery_target_timeline = 'latest'
295 pause_at_recovery_target = true
297 primary_conninfo = 'host=localhost port=5432'
298 recovery_min_apply_delay = 0
301 Only the specified parameters are recognized in the template. The `recovery.conf` is only be created if at least one parameter is set **and** [manage_recovery_conf](#manage_recovery_conf) is set to true.
303 ### Validate connectivity
305 To validate client connections to a remote PostgreSQL database before starting dependent tasks, use the `postgresql_conn_validator` resource. You can use this on any node where the PostgreSQL client software is installed. It is often chained to other tasks such as starting an application server or performing a database migration.
310 postgresql_conn_validator { 'validate my postgres connection':
311 host => 'my.postgres.host',
312 db_username => 'mydbuser',
313 db_password => 'mydbpassword',
314 db_name => 'mydbname',
316 exec { 'rake db:migrate':
317 cwd => '/opt/myrubyapp',
323 The postgresql module comes with many options for configuring the server. While you are unlikely to use all of the settings below, they provide a decent amount of control over your security settings.
327 * [postgresql::client](#postgresqlclient)
328 * [postgresql::globals](#postgresqlglobals)
329 * [postgresql::lib::devel](#postgresqllibdevel)
330 * [postgresql::lib::java](#postgresqllibjava)
331 * [postgresql::lib::perl](#postgresqllibperl)
332 * [postgresql::lib::python](#postgresqllibpython)
333 * [postgresql::server](#postgresqlserver)
334 * [postgresql::server::plperl](#postgresqlserverplperl)
335 * [postgresql::server::contrib](#postgresqlservercontrib)
336 * [postgresql::server::postgis](#postgresqlserverpostgis)
340 * [postgresql::server::config_entry](#postgresqlserverconfig_entry)
341 * [postgresql::server::database](#postgresqlserverdatabase)
342 * [postgresql::server::database_grant](#postgresqlserverdatabase_grant)
343 * [postgresql::server::db](#postgresqlserverdb)
344 * [postgresql::server::extension](#postgresqlserverextension)
345 * [postgresql::server::grant](#postgresqlservergrant)
346 * [postgresql::server::grant_role](#postgresqlservergrant_role)
347 * [postgresql::server::pg_hba_rule](#postgresqlserverpg_hba_rule)
348 * [postgresql::server::pg_ident_rule](#postgresqlserverpg_ident_rule)
349 * [postgresql::server::reassign_owned_by](#postgresqlserverreassign_owned_by)
350 * [postgresql::server::recovery](#postgresqlserverrecovery)
351 * [postgresql::server::role](#postgresqlserverrole)
352 * [postgresql::server::schema](#postgresqlserverschema)
353 * [postgresql::server::table_grant](#postgresqlservertable_grant)
354 * [postgresql::server::tablespace](#postgresqlservertablespace)
358 * [postgresql_psql](#custom-resource-postgresql_psql)
359 * [postgresql_replication_slot](#custom-resource-postgresql_replication_slot)
360 * [postgresql_conf](#custom-resource-postgresql_conf)
361 * [postgresql_conn_validator](#custom-resource-postgresql_conn_validator)
365 * [postgresql_password](#function-postgresql_password)
366 * [postgresql_acls_to_resources_hash](#function-postgresql_acls_to_resources_hashacl_array-id-order_offset)
370 #### postgresql::client
372 Installs PostgreSQL client software. Set the following parameters if you have a custom version you would like to install.
374 >**Note:** Make sure to add any necessary yum or apt repositories if specifying a custom version.
376 ##### `package_ensure`
378 Whether the PostgreSQL client package resource should be present.
380 Valid values: 'present', 'absent'.
382 Default value: 'present'.
386 Sets the name of the PostgreSQL client package.
388 Default value: 'file'.
390 #### postgresql::lib::docs
392 Installs PostgreSQL bindings for Postgres-Docs. Set the following parameters if you have a custom version you would like to install.
394 **Note:** Make sure to add any necessary yum or apt repositories if specifying a custom version.
398 Specifies the name of the PostgreSQL docs package.
400 ##### `package_ensure`
402 Whether the PostgreSQL docs package resource should be present.
404 Valid values: 'present', 'absent'.
406 Default value: 'present'.
408 #### postgresql::globals
410 **Note:** Most server-specific defaults should be overridden in the `postgresql::server` class. This class should be used only if you are using a non-standard OS, or if you are changing elements that can only be changed here, such as `version` or `manage_package_repo`.
414 Overrides the default PostgreSQL binaries directory for the target platform.
416 Default value: OS dependent.
418 ##### `client_package_name`
420 Overrides the default PostgreSQL client package name.
422 Default value: OS dependent.
426 Overrides the default PostgreSQL configuration directory for the target platform.
428 Default value: OS dependent.
430 ##### `contrib_package_name`
432 Overrides the default PostgreSQL contrib package name.
434 Default value: OS dependent.
436 ##### `createdb_path`
438 **Deprecated.** Path to the `createdb` command.
440 Default value: '${bindir}/createdb'.
444 Overrides the default PostgreSQL data directory for the target platform.
446 Default value: OS dependent.
448 **Note:** Changing the datadir after installation causes the server to come to a full stop before making the change. For Red Hat systems, the data directory must be labeled appropriately for SELinux. On Ubuntu, you must explicitly set `needs_initdb = true` to allow Puppet to initialize the database in the new datadir (`needs_initdb` defaults to true on other systems).
450 **Warning:** If datadir is changed from the default, Puppet does not manage purging of the original data directory, which causes it to fail if the data directory is changed back to the original.
452 ##### `default_database`
454 Specifies the name of the default database to connect with.
456 Default value: 'postgres' (for most systems).
458 ##### `devel_package_name`
460 Overrides the default PostgreSQL devel package name.
462 Default value: OS dependent.
464 ##### `docs_package_name`
468 Overrides the default PostgreSQL docs package name.
470 Default value: OS dependent.
474 Sets the default encoding for all databases created with this module. On certain operating systems, this is also used during the `template1` initialization, so it becomes a default outside of the module as well.
476 Default value: Dependent on the operating system's default encoding.
480 Overrides the default postgres user group to be used for related files in the file system.
482 Default value: 'postgres'.
486 Path to the `initdb` command.
488 ##### `java_package_name`
490 Overrides the default PostgreSQL java package name.
492 Default value: OS dependent.
496 Sets the default database locale for all databases created with this module. On certain operating systems, this is also used during the `template1` initialization, so it becomes a default outside of the module as well.
498 Default value: `undef`, which is effectively 'C'.
500 **On Debian, you'll need to ensure that the 'locales-all' package is installed for full functionality of PostgreSQL.**
502 ##### `data_checksums`
504 Optional boolean to turn on data checksums during `initdb`.
506 Default value: `undef`, which is the same as `false`.
510 Sets the default timezone of the postgresql server. The postgresql built-in default is taking the systems timezone information.
514 Overrides the default PostgreSQL log directory.
516 Default value: initdb's default path.
518 ##### `manage_package_repo`
520 Sets up official PostgreSQL repositories on your host if set to `true`.
522 Default value: `false`.
524 ##### `module_workdir`
526 Specifies working directory under which the psql command should be executed. May need to specify if '/tmp' is on volume mounted with noexec option.
528 Default value: '/tmp'.
532 Explicitly calls the initdb operation after the server package is installed and before the PostgreSQL service is started.
534 Default value: OS dependent.
536 ##### `perl_package_name`
538 Overrides the default PostgreSQL Perl package name.
540 Default value: OS dependent.
542 ##### `pg_hba_conf_defaults`
544 Disables the defaults supplied with the module for `pg_hba.conf` if set to `false`. This is useful if you want to override the defaults. Be sure that your changes align with the rest of the module, as some access is required to perform some operations, such as basic `psql` operations.
546 Default value: The globals value set in `postgresql::globals::manage_pg_hba_conf` which defaults to `true`.
548 ##### `pg_hba_conf_path`
550 Specifies the path to your `pg_hba.conf` file.
552 Default value: '${confdir}/pg_hba.conf'.
554 ##### `pg_ident_conf_path`
556 Specifies the path to your `pg_ident.conf` file.
558 Default value: '${confdir}/pg_ident.conf'.
560 ##### `plperl_package_name`
562 Overrides the default PostgreSQL PL/Perl package name.
564 Default value: OS dependent.
566 ##### `plpython_package_name`
568 Overrides the default PostgreSQL PL/Python package name.
570 Default value: OS dependent.
572 ##### `postgis_version`
574 Defines the version of PostGIS to install, if you install PostGIS.
576 Default value: The lowest available with the version of PostgreSQL to be installed.
578 ##### `postgresql_conf_path`
580 Sets the path to your `postgresql.conf` file.
582 Default value: '${confdir}/postgresql.conf'.
586 Sets the path to the `psql` command.
588 ##### `python_package_name`
590 Overrides the default PostgreSQL Python package name.
592 Default value: OS dependent.
594 ##### `recovery_conf_path`
596 Path to your `recovery.conf` file.
600 Sets the proxy option for the official PostgreSQL yum-repositories only. This is useful if your server is behind a corporate firewall and needs to use proxy servers for outside connectivity.
602 Debian is currently not supported.
606 Sets the baseurl for the PostgreSQL repository. Useful if you host your own mirror of the repository.
608 Default value: The official PostgreSQL repository.
610 ##### `server_package_name`
612 Overrides the default PostgreSQL server package name.
614 Default value: OS dependent.
618 Overrides the default PostgreSQL service name.
620 Default value: OS dependent.
622 ##### `service_provider`
624 Overrides the default PostgreSQL service provider.
626 Default value: OS dependent.
628 ##### `service_status`
630 Overrides the default status check command for your PostgreSQL service.
632 Default value: OS dependent.
636 Overrides the default PostgreSQL super user and owner of PostgreSQL related files in the file system.
638 Default value: 'postgres'.
642 The version of PostgreSQL to install and manage.
644 Default value: OS system default.
648 Overrides the default PostgreSQL xlog directory.
650 Default value: initdb's default path.
652 #### postgresql::lib::devel
654 Installs the packages containing the development libraries for PostgreSQL and symlinks `pg_config` into `/usr/bin` (if not in `/usr/bin` or `/usr/local/bin`).
656 ##### `link_pg_config`
658 If the bin directory used by the PostgreSQL page is not `/usr/bin` or `/usr/local/bin`, symlinks `pg_config` from the package's bin dir into `usr/bin` (not applicable to Debian systems). Set to `false` to disable this behavior.
660 Valid values: `true`, `false`.
662 Default value: `true`.
664 ##### `package_ensure`
666 Overrides the 'ensure' parameter during package installation.
668 Default value: 'present'.
672 Overrides the default package name for the distribution you are installing to.
674 Default value: 'postgresql-devel' or 'postgresql<version>-devel' depending on your distro.
676 #### postgresql::lib::java
678 Installs PostgreSQL bindings for Java (JDBC). Set the following parameters if you have a custom version you would like to install.
680 **Note:** Make sure to add any necessary yum or apt repositories if specifying a custom version.
682 ##### `package_ensure`
684 Specifies whether the package is present.
686 Valid values: 'present', 'absent'.
688 Default value: 'present'.
692 Specifies the name of the PostgreSQL java package.
694 #### postgresql::lib::perl
696 Installs the PostgreSQL Perl libraries.
698 ##### `package_ensure`
700 Specifies whether the package is present.
702 Valid values: 'present', 'absent'.
704 Default value: 'present'.
708 Specifies the name of the PostgreSQL perl package to install.
710 #### postgresql::server::plpython
712 Installs the PL/Python procedural language for PostgreSQL.
716 Specifies the name of the postgresql PL/Python package.
718 ##### `package_ensure`
720 Specifies whether the package is present.
722 Valid values: 'present', 'absent'.
724 Default value: 'present'.
726 #### postgresql::lib::python
728 Installs PostgreSQL Python libraries.
730 ##### `package_ensure`
732 Specifies whether the package is present.
734 Valid values: 'present', 'absent'.
736 Default value: 'present'.
740 The name of the PostgreSQL Python package.
742 #### postgresql::server
744 ##### `createdb_path`
746 **Deprecated.** Specifies the path to the `createdb` command.
748 Default value: '${bindir}/createdb'.
750 ##### `default_database`
752 Specifies the name of the default database to connect with. On most systems this is 'postgres'.
754 ##### `default_connect_settings`
756 Specifies a hash of environment variables used when connecting to a remote server. Becomes the default for other defined-types. i.e. `postgresql::server::role`
760 Sets the default encoding for all databases created with this module. On certain operating systems this is also used during the `template1` initialization, so it becomes a default outside of the module as well.
762 Default value: `undef`.
766 Overrides the default postgres user group to be used for related files in the file system.
768 Default value: OS dependent default.
772 Specifies the path to the `initdb` command.
774 Default value: '${bindir}/initdb'.
778 Lists strings for access control for connection method, users, databases, IPv4 addresses;
780 see [PostgreSQL documentation](http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html) on `pg_hba.conf` for information.
784 Lists strings for access control for connection method, users, databases, IPv6 addresses.
786 see [PostgreSQL documentation](http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html) on `pg_hba.conf` for information.
788 ##### `ip_mask_allow_all_users`
790 Overrides PostgreSQL defaults for remote connections. By default, PostgreSQL does not allow database user accounts to connect via TCP from remote machines. If you'd like to allow this, you can override this setting.
792 Set to '0.0.0.0/0' to allow database users to connect from any remote machine, or '192.168.0.0/1' to allow connections from any machine on your local '192.168' subnet.
794 Default value: '127.0.0.1/32'.
796 ##### `ip_mask_deny_postgres_user`
798 Specifies the IP mask from which remote connections should be denied for the postgres superuser.
800 Default value: '0.0.0.0/0', which denies any remote connection.
804 Sets the default database locale for all databases created with this module. On certain operating systems this is used during the `template1` initialization as well, so it becomes a default outside of the module.
806 Default value: `undef`, which is effectively 'C'.
808 **On Debian, you must ensure that the 'locales-all' package is installed for full functionality of PostgreSQL.**
810 ##### `manage_pg_hba_conf`
812 Whether to manage the `pg_hba.conf`.
814 If set to `true`, Puppet overwrites this file.
816 If set to `false`, Puppet does not modify the file.
818 Valid values: `true`, `false`.
820 Default value: `true`
822 ##### `manage_pg_ident_conf`
824 Overwrites the pg_ident.conf file.
826 If set to `true`, Puppet overwrites the file.
828 If set to `false`, Puppet does not modify the file.
830 Valid values: `true`, `false`.
832 Default value: `true`.
834 ##### `manage_recovery_conf`
836 Specifies whether or not manage the `recovery.conf`.
838 If set to `true`, Puppet overwrites this file.
840 Valid values: `true`, `false`.
842 Default value: `false`.
846 Explicitly calls the `initdb` operation after server package is installed, and before the PostgreSQL service is started.
848 Default value: OS dependent.
850 ##### `package_ensure`
852 Passes a value through to the `package` resource when creating the server instance.
854 Default value: `undef`.
858 Specifies the name of the package to use for installing the server software.
860 Default value: OS dependent.
862 ##### `pg_hba_conf_defaults`
864 If `false`, disables the defaults supplied with the module for `pg_hba.conf`. This is useful if you disagree with the defaults and wish to override them yourself. Be sure that your changes of course align with the rest of the module, as some access is required to perform basic `psql` operations for example.
866 ##### `pg_hba_conf_path`
868 Specifies the path to your `pg_hba.conf` file.
870 ##### `pg_ident_conf_path`
872 Specifies the path to your `pg_ident.conf` file.
874 Default value: '${confdir}/pg_ident.conf'.
876 ##### `plperl_package_name`
878 Sets the default package name for the PL/Perl extension.
880 Default value: OS dependent.
882 ##### `plpython_package_name`
884 Sets the default package name for the PL/Python extension.
886 Default value: OS dependent.
890 Specifies the port for the PostgreSQL server to listen on. **Note:** The same port number is used for all IP addresses the server listens on. Also, for Red Hat systems and early Debian systems, changing the port causes the server to come to a full stop before being able to make the change.
892 Default value: 5432. Meaning the Postgres server listens on TCP port 5432.
894 ##### `postgres_password`
896 Sets the password for the postgres user to your specified value. By default, this setting uses the superuser account in the Postgres database, with a user called `postgres` and no password.
898 Default value: `undef`.
900 ##### `postgresql_conf_path`
902 Specifies the path to your `postgresql.conf` file.
904 Default value: '${confdir}/postgresql.conf'.
908 Specifies the path to the `psql` command.
910 Default value: OS dependent.
912 ##### `service_manage`
914 Defines whether or not Puppet should manage the service.
916 Default value: `true`.
920 Overrides the default PostgreSQL service name.
922 Default value: OS dependent.
924 ##### `service_provider`
926 Overrides the default PostgreSQL service provider.
928 Default value: `undef`.
930 ##### `service_reload`
932 Overrides the default reload command for your PostgreSQL service.
934 Default value: OS dependent.
936 ##### `service_restart_on_change`
938 Overrides the default behavior to restart your PostgreSQL service when a config entry has been changed that requires a service restart to become active.
940 Default value: `true`.
942 ##### `service_status`
944 Overrides the default status check command for your PostgreSQL service.
946 Default value: OS dependent.
950 Overrides the default PostgreSQL super user and owner of PostgreSQL related files in the file system.
952 Default value: 'postgres'.
954 #### postgresql::server::contrib
956 Installs the PostgreSQL contrib package.
958 ##### `package_ensure`
960 Sets the ensure parameter passed on to PostgreSQL contrib package resource.
964 The name of the PostgreSQL contrib package.
966 #### postgresql::server::plperl
968 Installs the PL/Perl procedural language for postgresql.
970 ##### `package_ensure`
972 The ensure parameter passed on to PostgreSQL PL/Perl package resource.
976 The name of the PostgreSQL PL/Perl package.
978 #### postgresql::server::postgis
980 Installs the PostgreSQL postgis packages.
984 #### postgresql::server::config_entry
986 Modifies your `postgresql.conf` configuration file.
988 Each resource maps to a line inside the file, for example:
991 postgresql::server::config_entry { 'check_function_bodies':
998 Removes an entry if set to 'absent'.
1000 Valid values: 'present', 'absent'.
1002 Default value: 'present'.
1006 Defines the value for the setting.
1008 #### postgresql::server::db
1010 Creates a local database, user, and assigns necessary permissions.
1014 Defines a comment to be stored about the database using the PostgreSQL COMMENT command.
1016 ##### `connect_settings`
1018 Specifies a hash of environment variables used when connecting to a remote server.
1020 Default value: Connects to the local Postgres instance.
1024 Sets the name of the database to be created.
1026 Default value: the namevar.
1030 Overrides the character set during creation of the database.
1032 Default value: The default defined during installation.
1036 Specifies the permissions to grant during creation.
1038 Default value: 'ALL'.
1042 Specifies that the database is a template, if set to `true`.
1044 Default value: `false`.
1048 Overrides the locale during creation of the database.
1050 Default value: The default defined during installation.
1054 Sets a user as the owner of the database.
1056 Default value: '$user' variable set in `postgresql::server` or `postgresql::globals`.
1060 **Required** Sets the password for the created user.
1064 Defines the name of the tablespace to allocate the created database to.
1066 Default value: PostgreSQL default.
1070 Specifies the name of the template database from which to build this database.
1072 Defaults value: `template0`.
1076 User to create and assign access to the database upon creation. Mandatory.
1078 #### postgresql::server::database
1080 Creates a database with no users and no permissions.
1084 Sets the name of the database.
1086 Defaults value: The namevar.
1090 Overrides the character set during creation of the database.
1092 Default value: The default defined during installation.
1096 Defines the database as a template if set to `true`.
1098 Default value: `false`.
1102 Overrides the locale during creation of the database.
1104 Default value: The default defined during installation.
1108 Sets name of the database owner.
1110 Default value: The '$user' variable set in `postgresql::server` or `postgresql::globals`.
1114 Sets tablespace for where to create this database.
1116 Default value: The default defined during installation.
1120 Specifies the name of the template database from which to build this database.
1122 Default value: 'template0'.
1124 #### postgresql::server::database_grant
1126 Manages grant-based access privileges for users, wrapping the `postgresql::server::database_grant` for database specific permissions. Consult the [PostgreSQL documentation for `grant`](http://www.postgresql.org/docs/current/static/sql-grant.html) for more information.
1128 #### `connect_settings`
1130 Specifies a hash of environment variables used when connecting to a remote server.
1132 Default value: Connects to the local Postgres instance.
1136 Specifies the database to which you are granting access.
1140 Specifies comma-separated list of privileges to grant.
1142 Valid options: 'ALL', 'CREATE', 'CONNECT', 'TEMPORARY', 'TEMP'.
1146 Defines the database to execute the grant against.
1148 **This should not ordinarily be changed from the default**
1150 Default value: 'postgres'.
1154 Specifies the OS user for running `psql`.
1156 Default value: The default user for the module, usually 'postgres'.
1160 Specifies the role or user whom you are granting access to.
1162 #### postgresql::server::extension
1164 Manages a PostgreSQL extension.
1168 Specifies the database on which to activate the extension.
1172 Specifies whether to activate or deactivate the extension.
1174 Valid options: 'present' or 'absent'.
1178 Specifies the extension to activate. If left blank, uses the name of the resource.
1180 ##### `package_name`
1182 Specifies a package to install prior to activating the extension.
1184 ##### `package_ensure`
1186 Overrides default package deletion behavior.
1188 By default, the package specified with `package_name` is installed when the extension is activated and removed when the extension is deactivated. To override this behavior, set the `ensure` value for the package.
1190 #### postgresql::server::grant
1192 Manages grant-based access privileges for roles. See [PostgreSQL documentation for `grant`](http://www.postgresql.org/docs/current/static/sql-grant.html) for more information.
1196 Specifies the database to which you are granting access.
1200 Specifies the type of object to which you are granting privileges.
1202 Valid options: 'DATABASE', 'SCHEMA', 'SEQUENCE', 'ALL SEQUENCES IN SCHEMA', 'TABLE' or 'ALL TABLES IN SCHEMA'.
1206 Specifies name of `object_type` to which to grant access.
1210 Port to use when connecting.
1212 Default value: `undef`, which generally defaults to port 5432 depending on your PostgreSQL packaging.
1216 Specifies the privilege to grant.
1218 Valid options: 'ALL', 'ALL PRIVILEGES' or 'object_type' dependent string.
1222 Specifies the database to execute the grant against.
1224 **This should not ordinarily be changed from the default**
1226 Default value: 'postgres'.
1230 Sets the OS user to run `psql`.
1232 Default value: the default user for the module, usually 'postgres'.
1236 Specifies the role or user whom you are granting access to.
1238 #### postgresql::server::grant_role
1240 Allows you to assign a role to a (group) role. See [PostgreSQL documentation for `Role Membership`](http://www.postgresql.org/docs/current/static/role-membership.html) for more information.
1244 Specifies the group role to which you are assigning a role.
1248 Specifies the role you want to assign to a group. If left blank, uses the name of the resource.
1252 Specifies whether to grant or revoke the membership.
1254 Valid options: 'present' or 'absent'.
1256 Default value: 'present'.
1260 Port to use when connecting.
1262 Default value: `undef`, which generally defaults to port 5432 depending on your PostgreSQL packaging.
1266 Specifies the database to execute the grant against.
1268 **This should not ordinarily be changed from the default**
1270 Default value: 'postgres'.
1274 Sets the OS user to run `psql`.
1276 Default value: the default user for the module, usually `postgres`.
1278 ##### `connect_settings`
1280 Specifies a hash of environment variables used when connecting to a remote server.
1282 Default value: Connects to the local Postgres instance.
1284 #### postgresql::server::pg_hba_rule
1286 Allows you to create an access rule for `pg_hba.conf`. For more details see the [usage example](#create-an-access-rule-for-pghba.conf) and the [PostgreSQL documentation](http://www.postgresql.org/docs/current/static/auth-pg-hba-conf.html).
1290 Sets a CIDR based address for this rule matching when the type is not 'local'.
1294 Provides the method that is used for authentication for the connection that this rule matches. Described further in the PostgreSQL `pg_hba.conf` documentation.
1298 For certain `auth_method` settings there are extra options that can be passed. Consult the PostgreSQL `pg_hba.conf` documentation for further details.
1302 Sets a comma-separated list of databases that this rule matches.
1306 Defines a longer description for this rule, if required. This description is placed in the comments above the rule in `pg_hba.conf`.
1308 Default value: 'none'.
1310 Specifies a way to uniquely identify this resource, but functionally does nothing.
1314 Sets an order for placing the rule in `pg_hba.conf`.
1318 #### `postgresql_version`
1320 Manages `pg_hba.conf` without managing the entire PostgreSQL instance.
1322 Default value: the version set in `postgresql::server`.
1326 Provides the target for the rule, and is generally an internal only property.
1328 **Use with caution.**
1332 Sets the type of rule.
1334 Valid options: 'local', 'host', 'hostssl' or 'hostnossl'.
1338 Sets a comma-separated list of users that this rule matches.
1341 #### postgresql::server::pg_ident_rule
1343 Allows you to create user name maps for `pg_ident.conf`. For more details see the [usage example](#create-user-name-maps-for-pgidentconf) above and the [PostgreSQL documentation](http://www.postgresql.org/docs/current/static/auth-username-maps.html).
1345 ##### `database_username`
1347 Specifies the user name of the database user. The `system_username` is mapped to this user name.
1351 Sets a longer description for this rule if required. This description is placed in the comments above the rule in `pg_ident.conf`.
1353 Default value: 'none'.
1357 Sets the name of the user map that is used to refer to this mapping in `pg_hba.conf`.
1361 Defines an order for placing the mapping in `pg_ident.conf`.
1365 ##### `system_username`
1367 Specifies the operating system user name (the user name used to connect to the database).
1371 Provides the target for the rule and is generally an internal only property.
1373 **Use with caution.**
1375 #### postgresql::server::reassign_owned_by
1377 Runs the PostgreSQL command 'REASSIGN OWNED' on a database, to transfer the ownership of existing objects between database roles
1381 Specifies the database to which the 'REASSIGN OWNED' will be applied
1385 Specifies the role or user who is the current owner of the objects in the specified db
1389 Specifies the role or user who will be the new owner of these objects
1393 Specifies the OS user for running `psql`.
1395 Default value: The default user for the module, usually 'postgres'.
1399 Port to use when connecting.
1401 Default value: `undef`, which generally defaults to port 5432 depending on your PostgreSQL packaging.
1403 ##### `connect_settings`
1405 Specifies a hash of environment variables used when connecting to a remote server.
1407 Default value: Connects to the local Postgres instance.
1409 #### postgresql::server::recovery
1411 Allows you to create the content for `recovery.conf`. For more details see the [usage example](#create-recovery-configuration) and the [PostgreSQL documentation](http://www.postgresql.org/docs/current/static/recovery-config.html).
1413 Every parameter value is a string set in the template except `recovery_target_inclusive`, `pause_at_recovery_target`, `standby_mode` and `recovery_min_apply_delay`.
1415 A detailed description of all listed parameters can be found in the [PostgreSQL documentation](http://www.postgresql.org/docs/current/static/recovery-config.html).
1417 The parameters are grouped into these three sections:
1419 ##### [Archive Recovery Parameters](http://www.postgresql.org/docs/current/static/archive-recovery-settings.html)
1422 * `archive_cleanup_command`
1423 * `recovery_end_command`
1425 ##### [Recovery Target Settings](http://www.postgresql.org/docs/current/static/recovery-target-settings.html)
1426 * `recovery_target_name`
1427 * `recovery_target_time`
1428 * `recovery_target_xid`
1429 * `recovery_target_inclusive`
1431 * `recovery_target_timeline`
1432 * `pause_at_recovery_target`
1434 ##### [Standby Server Settings](http://www.postgresql.org/docs/current/static/standby-settings.html)
1435 * `standby_mode`: Can be specified with the string ('on'/'off'), or by using a Boolean value (`true`/`false`).
1436 * `primary_conninfo`
1437 * `primary_slot_name`
1439 * `recovery_min_apply_delay`
1442 Provides the target for the rule, and is generally an internal only property.
1444 **Use with caution.**
1446 #### postgresql::server::role
1447 Creates a role or user in PostgreSQL.
1449 ##### `connection_limit`
1450 Specifies how many concurrent connections the role can make.
1452 Default value: '-1', meaning no limit.
1454 ##### `connect_settings`
1455 Specifies a hash of environment variables used when connecting to a remote server.
1457 Default value: Connects to the local Postgres instance.
1460 Specifies whether to grant the ability to create new databases with this role.
1462 Default value: `false`.
1465 Specifies whether to grant the ability to create new roles with this role.
1467 Default value: `false`.
1470 Specifies whether to grant inherit capability for the new role.
1472 Default value: `true`.
1475 Specifies whether to grant login capability for the new role.
1477 Default value: `true`.
1479 ##### `password_hash`
1480 Sets the hash to use during password creation. If the password is not already pre-encrypted in a format that PostgreSQL supports, use the `postgresql_password` function to provide an MD5 hash here, for example:
1482 ##### `update_password`
1483 If set to true, updates the password on changes. Set this to false to not modify the role's password after creation.
1486 postgresql::server::role { 'myusername':
1487 password_hash => postgresql_password('myusername', 'mypassword'),
1493 Provides provides replication capabilities for this role if set to `true`.
1495 Default value: `false`.
1499 Specifies whether to grant super user capability for the new role.
1501 Default value: `false`.
1505 Defines the username of the role to create.
1507 Default value: the namevar.
1509 #### postgresql::server::schema
1513 ##### `connect_settings`
1515 Specifies a hash of environment variables used when connecting to a remote server.
1517 Default value: Connects to the local Postgres instance.
1523 Sets the name of the database in which to create this schema.
1527 Sets the default owner of the schema.
1531 Sets the name of the schema.
1533 Default value: the namevar.
1535 #### postgresql::server::table_grant
1537 Manages grant-based access privileges for users. Consult the PostgreSQL documentation for `grant` for more information.
1539 ##### `connect_settings`
1541 Specifies a hash of environment variables used when connecting to a remote server.
1543 Default value: Connects to the local Postgres instance.
1547 Specifies which database the table is in.
1551 Specifies comma-separated list of privileges to grant. Valid options: 'ALL', 'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER'.
1555 Specifies the database to execute the grant against.
1557 This should not ordinarily be changed from the default.
1559 Default value: 'postgres'.
1563 Specifies the OS user for running `psql`.
1565 Default value: The default user for the module, usually 'postgres'.
1569 Specifies the role or user to whom you are granting access.
1573 Specifies the table to which you are granting access.
1575 #### postgresql::server::tablespace
1577 Creates a tablespace. If necessary, also creates the location and assigns the same permissions as the PostgreSQL server.
1579 ##### `connect_settings`
1581 Specifies a hash of environment variables used when connecting to a remote server.
1583 Default value: Connects to the local Postgres instance.
1587 Specifies the path to locate this tablespace.
1591 Specifies the default owner of the tablespace.
1595 Specifies the name of the tablespace.
1597 Default value: the namevar.
1601 #### postgresql_psql
1603 Enables Puppet to run psql statements.
1609 Specifies the SQL command to execute via psql.
1613 Specifies the working directory under which the psql command should be executed.
1615 Default value: '/tmp'.
1619 Specifies the name of the database to execute the SQL command against.
1623 Specifies any additional environment variables you want to set for a SQL command. Multiple environment variables should be specified as an array.
1627 Sets an arbitrary tag for your own reference; the name of the message. This is the namevar.
1631 Sets an optional SQL command to execute prior to the main command. This is generally intended to be used for idempotency, to check for the existence of an object in the database to determine whether or not the main SQL command needs to be executed at all.
1635 Specifies the port of the database server to execute the SQL command against.
1639 Specifies the system user group account under which the psql command should be executed.
1641 Default value: 'postgres'.
1645 Specifies the path to psql executable.
1647 Default value: 'psql'.
1651 Specifies the system user account under which the psql command should be executed.
1653 Default value: 'postgres'.
1657 Specifies whether to execute the SQL only if there is a notify or subscribe event.
1659 Valid values: `true`, `false`.
1661 Default value: `false`.
1665 Defines the schema search path to use when executing the SQL command.
1669 The inverse of `onlyif`.
1671 #### postgresql_conf
1673 Allows Puppet to manage `postgresql.conf` parameters.
1677 Specifies the PostgreSQL parameter name to manage.
1679 This is the namevar.
1683 Specifies the path to `postgresql.conf`.
1685 Default value: '/etc/postgresql.conf'.
1689 Specifies the value to set for this parameter.
1691 #### postgresql_replication_slot
1693 Allows you to create and destroy replication slots to register warm standby replication on a PostgreSQL master server.
1697 Specifies the name of the slot to create. Must be a valid replication slot name.
1699 This is the namevar.
1705 Specifies the action to create or destroy named slot.
1707 Valid values: 'present', 'absent'.
1709 Default value: 'present'.
1711 #### postgresql_conn_validator
1713 Validate the connection to a local or remote PostgreSQL database using this type.
1715 ##### `connect_settings`
1717 Specifies a hash of environment variables used when connecting to a remote server. This is an alternative to providing individual parameters (`host`, etc). If provided, the individual parameters take precedence.
1723 Specifies the name of the database you wish to test.
1729 Specifies the password to connect with. Can be left blank if `.pgpass` is being used, otherwise not recommended.
1735 Specifies the username to connect with.
1739 When using a Unix socket and ident auth, this is the user you are running as.
1743 This is the command run against the target database to verify connectivity.
1745 Default value: 'SELECT 1'
1749 Sets the hostname of the database you wish to test.
1751 Default value: '', which generally uses the designated local Unix socket.
1753 **If the host is remote you must provide a username.**
1757 Defines the port to use when connecting.
1763 Specifies the user to run the `psql` command as. This is important when trying to connect to a database locally using Unix sockets and `ident` authentication. Not needed for remote testing.
1767 Sets the number of seconds to sleep for before trying again after a failure.
1771 Sets the number of attempts after failure before giving up and failing the resource.
1775 #### postgresql_password
1777 Generates a PostgreSQL encrypted password, use `postgresql_password`. Call it from the command line and then copy and paste the encrypted password into your manifest:
1780 puppet apply --execute 'notify { 'test': message => postgresql_password('username', 'password') }'
1783 Alternatively, you can call this from your production manifests, but the manifests will then contain a clear text version of your passwords.
1785 #### postgresql_acls_to_resources_hash(acl_array, id, order_offset)
1787 This internal function converts a list of `pg_hba.conf` based ACLs (passed in as an array of strings) to a format compatible with the `postgresql::pg_hba_rule` resource.
1789 **This function should only be used internally by the module**.
1793 Works with versions of PostgreSQL from 8.1 through 9.5.
1795 Currently, the postgresql module is tested on the following operating systems:
1797 * Debian 6.x, 7.x, 8.x.
1798 * CentOS 5.x, 6.x, and 7.x.
1799 * Ubuntu 10.04 and 12.04, 14.04.
1801 Other systems might be compatible, but are not being actively tested.
1803 ### Apt module support
1805 While this module supports both 1.x and 2.x versions of the 'puppetlabs-apt' module, it does not support 'puppetlabs-apt' 2.0.0 or 2.0.1.
1809 PostGIS is currently considered an unsupported feature, as it doesn't work on all platforms correctly.
1811 ### All versions of RHEL/CentOS
1813 If you have SELinux enabled you must add any custom ports you use to the `postgresql_port_t` context. You can do this as follows:
1816 semanage port -a -t postgresql_port_t -p tcp $customport
1821 Puppet Labs modules on the Puppet Forge are open projects, and community contributions are essential for keeping them great. We can’t access the huge number of platforms and myriad hardware, software, and deployment configurations that Puppet is intended to serve. We want to keep it as easy as possible to contribute changes so that our modules work in your environment. There are a few guidelines that we need contributors to follow so that we can have a chance of keeping on top of things. For more information, see our [module contribution guide](https://docs.puppetlabs.com/forge/contributing.html).
1825 There are two types of tests distributed with this module. Unit tests with `rspec-puppet` and system tests using `rspec-system`.
1827 For unit testing, make sure you have:
1832 Install the necessary gems:
1835 bundle install --path=vendor
1838 And then run the unit tests:
1841 bundle exec rake spec
1844 The unit tests are run in Travis-CI as well. If you want to see the results of your own tests, register the service hook through Travis-CI via the accounts section for your GitHub clone of this project.
1846 To run the system tests, make sure you also have:
1849 * VirtualBox > 4.2.10
1851 Then run the tests using:
1854 bundle exec rspec spec/acceptance
1857 To run the tests on different operating systems, see the sets available in `.nodeset.yml` and run the specific set with the following syntax:
1860 RSPEC_SET=debian-607-x64 bundle exec rspec spec/acceptance
1865 View the full list of contributors on [Github](https://github.com/puppetlabs/puppetlabs-postgresql/graphs/contributors).