Home / server / Questions / 560516
Accepted
Red Cricket
Asked: 2013-12-11 13:51:14 +0800 CST

How to insert row into RDBMS table from puppet module?

  • 772

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.

puppet

1 Answers

  1. Best Answer

    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.

    • 4