I have a database table where I need to insert a row for each host has been assigned a particular puppet model. Lets call this puppet model foo. The db table is called foo_hosts has three columns: id, hostname, and date_added. I would like to some how have my foo module execute SQL ...

insert into foo_hosts (hostname) values ( $hostname );

... if there is not already a row in the table for the host.

  • Why not use Puppet's reporting system? Or even Nagios? You probably need to monitor your hosts anyway, so Nagios could tell you more, and tell you which hosts exist in your infrastructure. – François Beausoleil Dec 11 '13 at 2:19
  • The tasks of keeping track of hosts that are 'foo' host is a contrived example. The real reason I need to me able to manipulate a DB table is actually more complex and I didn't want to bore everyone with it. – Red Cricket Dec 11 '13 at 19:03

You will need a way to access your database from the command line. You do not specify which database engine you're using, so I will presume PostgreSQL, as this is what I am most familiar with. I know SQLite and MySQL both have a command line client which allows execution of queries.

First, I would start with this in a module:

  command => "/usr/local/bin/update-database ${hostname}",
  require => File['/usr/local/bin/update-database'],

  source => 'puppet:///modules/your-module/update-database',
  mode   => 0755,

And the update-database script could be:


# add credentials to access the database server to psql's command line options
/bin/echo <<-EOF | /usr/bin/psql --file=- --no-psqlrc --quiet
  INSERT INTO foo_hosts(hostname)
      SELECT hostname FROM foo_hosts;

Note that this SQL query is vulnerable: if an attacker gains control of Puppet, he can execute any arbitrary command against your database server. Of course, if the attacker gains control of Puppet, you have even more serious problems.

  • Puppet does a great job with things like packages and user accounts, but too bad puppet does have better functionality when it comes to DBs. It would be nice to have that higher level of abstraction for the various RDBMS. Something like table { foo_hosts: ensure_rows => [ 'blah', ... primary_key => 'id', ... – Red Cricket Dec 11 '13 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.