Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

1 Answer

up vote 3 down vote accepted

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:

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

file{'/usr/local/bin/update-database':
  source => 'puppet:///modules/your-module/update-database',
  mode   => 0755,
}

And the update-database script could be:

#!/bin/bash
$HOSTNAME=${1?Missing HOSTNAME}

# 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}'
    EXCEPT
      SELECT hostname FROM foo_hosts;
EOF

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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