Plugin Idea: postgresql_inventory

Hello Everyone, I’m Gebz (First post on the Ansible forums).

I’ve recently started working on a custom plugin for pulling dynamic inventory data from a postgresql table (since I didn’t find any that exist to my knowledge), and was looking for feedback regarding whether or not this feature would be useful to anyone else other than me.

You can checkout an initial version of how such a plugin would look like, but keep in mind the design choice is opinionated (and could be wrong, which is why I would value your feedback)

Would love to hear your thoughts

Thanks for posting this, and bringing the discussion from the PR into the forum.

I’d like to see a few more real-world use cases where people think that the plugin is useful.

For implementation details:

  • Groups need to be a separate table, and linked with a 1:n table to hosts
  • Do away with the SQL query, define a static set of tables and hardcode the necessary queries
  • More test cases
  • Possibly real-world test (as in: install a PG database and run tests against it)
2 Likes

Why would I forego the custom query if for example i have some other metadata columns in the hosts table I want to filter by? (for example by business service, distro, etc..), my thinking was to have the order of the columns in the select be mapped to what the plugin would expect. I do agree however that splitting groups into a separate table makes sense.

What do you think?

That custom filter could be part of the module, not part of a query string.

From a security perspective: how do you guarantee that your query string - which might be created on the fly, and possibly with user input - does not include any SQL injections. At the Ansible DSL level you have no functionality to quote values or identifiers, or build a prepared query with parameters.

Moving this into a well-defined filter optional parameter let you do the query building in Python, with all the proper functionality around it.

1 Like

I’ve made like 6 versions of this, a simple one that uses it’s own schema is simple, the issue I normally encountered was that it had to adapt to pre existing data in postgresql/mysql/etc.