How to Puppet Foreman Mysql or Postgresql on RHEL6

This is not mandatory as puppet and Foreman will default to files and sqlite respectively. Meanwhile for production use cases more scalable databases are recommended.

Also PuppetLabs recommends to go with PuppetDB meanwhile Foreman doesn't use it so we'll pass on that.

Two different databases to chose from could be use for Puppet ENC and Foreman:

• Mysql
• Postgresql

Mysql

Let's get the DBMS and active the service by default:

yum install -y mysql-server
chkconfig mysqld on
service mysqld start

Then we initialise the mysql database:

MYSQL_ADMIN_PASSWD='mysql'
/usr/bin/mysqladmin -u root password "${MYSQL_ADMIN_PASSWD}"
/usr/bin/mysqladmin -u root -h $(hostname) password "${MYSQL_ADMIN_PASSWD}"

Puppet database

We need to create a Puppet database and grant permission to it's user, “puppet”:

The following command will do that for us.

Note
Change the MYSQL_PUPPET_PASSWD variable to assign the password of your choice.

Note
The command will prompt for the MYSQL_ROOT_PASSWD we set-up earlier.

MYSQL_PUPPET_PASSWD='puppet'
echo "create database puppet; GRANT ALL PRIVILEGES ON puppet.* TO puppet@localhost IDENTIFIED BY '$MYSQL_PUPPET_PASSWD'; commit;" | mysql -u root -p

Finally we adjust the /etc/puppet/puppet.conf file for mysql.

Note
We reuse here the MYSQL_PUPPET_PASSWD assigned before.

augtool -s set /files/etc/puppet/puppet.conf/master/storeconfigs true
augtool -s set /files/etc/puppet/puppet.conf/master/dbadapter mysql
augtool -s set /files/etc/puppet/puppet.conf/master/dbname puppet
augtool -s set /files/etc/puppet/puppet.conf/master/dbuser puppet
augtool -s set /files/etc/puppet/puppet.conf/master/dbpassword $MYSQL_PUPPET_PASSWD
augtool -s set /files/etc/puppet/puppet.conf/master/dbserver localhost
augtool -s set /files/etc/puppet/puppet.conf/master/dbsocket /var/lib/mysql/mysql.sock

Foreman Setup

First off we need the mysql gems for foreman:

yum -y install foreman-mysql*

We need to configure foreman to make good use of our Mysql Puppet database.

Modify the /etc/foreman/database.yml file so the production section looks like this:

production:
  adapter: mysql2
  database: puppet
  username: puppet
  password: puppet
  host: localhost
  socket: "/var/lib/mysql/mysql.sock"

And then foreman to populate the database:

cd /usr/share/foreman && RAILS_ENV=production rake db:migrate

Mysql Optimisation

For optimisation, the following which is optional, should be done only once puppet database has been created and populated.

Run the following create index command, you'll be prompted for the MYSQL_PUPPET_PASSWD password specified earlier:

echo “create index exported_restype_title on resources (exported, restype, title(50));” | mysql -u root -p -D puppet

Postgresql

Get the DBMS, initialise the database and start service:

yum install -y postgresql-server
service postgresql initdb
service postgresql start
chkconfig postgresql on

Changing Postgresql authentication method (Optional)

The default ident authentication scheme requires to be logged with corresponding user. We prefer to use md5 auth scheme:

• Assign a password to default admin user postgres

sudo -u postgres psql template1
template1=# alter user postgres with encrypted password 'password';

• Change /var/lib/pgsql/data/postgresql.conf with:

password_encryption = on

• Change /var/lib/pgsql/data/pg_hba.conf with:

# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

• Reload postgresql

service postgresql reload

Puppet database

We now create puppet database, the postgres password setup above will be prompted for:

POSTGRES_PUPPET_PASSWD='puppet'
echo "create database puppet; create user puppet with unencrypted password 'puppet'; grant all privileges on database puppet to puppet;" | psql -U  postgres template1

You can check login into puppet base:

psql -U puppet puppet

Finally we adjust the /etc/puppet/puppet.conf file to use this puppet database:

Note
We reuse here the POSTGRES_PUPPET_PASSWD assigned before.

augtool -s set /files/etc/puppet/puppet.conf/master/storeconfigs true
augtool -s set /files/etc/puppet/puppet.conf/master/dbadapter postgresql
augtool -s set /files/etc/puppet/puppet.conf/master/dbname puppet
augtool -s set /files/etc/puppet/puppet.conf/master/dbuser puppet
augtool -s set /files/etc/puppet/puppet.conf/master/dbpassword $POSTGRES_PUPPET_PASSWD
augtool -s set /files/etc/puppet/puppet.conf/master/dbserver localhost

Foreman database

yum install -y foreman-postgresql

Edit your config/database.yml and modify:

production:
  adapter: postgresql
  database: puppet
  username: puppet
  password: puppet
  host: localhost

And then foreman to populate the database:

cd /usr/share/foreman && RAILS_ENV=production rake db:migrate