postgresql_info show command with protected keywords

[Sighvat]

SUMMARY

Show command in postgresql_info is not protected by brackets, when a parameter contain a key word (like analyze), it raises an SQL syntax error.

ISSUE TYPE

• Bug Report

COMPONENT NAME

Module postgresql_info (it sould be in this line here)

ANSIBLE VERSION

  config file = /home/iac/git/deploy-postgres/ansible.cfg
  configured module search path = ['/home/iac/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.9/dist-packages/ansible
  ansible collection location = /home/iac/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/local/bin/ansible
  python version = 3.9.2 (default, Feb 28 2021, 17:03:44) [GCC 10.2.1 20210110]
  jinja version = 3.1.2
  libyaml = True

COLLECTION VERSION

community.postgresql 2.1.5

CONFIGURATION

DEFAULT_LOAD_CALLBACK_PLUGINS(env: ANSIBLE_LOAD_CALLBACK_PLUGINS) = True
DEFAULT_STDOUT_CALLBACK(/home/iac/git/deploy-postgres/ansible.cfg) = debug
HOST_KEY_CHECKING(/home/iac/git/deploy-postgres/ansible.cfg) = False
RETRY_FILES_ENABLED(/home/iac/git/deploy-postgres/ansible.cfg) = False

OS / ENVIRONMENT

OS Ubuntu 20.04.4 LTS
Server PostgreSQL 13.7 (Ubuntu 13.7-1.pgdg20.04+1)
With the extension postgresql-13-partman/focal-pgdg,now 4.6.2-1.pgdg20.04+1 amd64

STEPS TO REPRODUCE

In order to reproduce this bug you need to have shared_preload_libraries='pg_partman_bgw' on postgresql.conf file.
This will add pg_partman_bgw.analyze parametter to the database (among many others)

And then run postgresql_info module (with postgreSQL running on default port and socket directory)

- name: Get detailed informations of PostgreSQL
  community.postgresql.postgresql_info:
  become_user: postgres
  become: yes

EXPECTED RESULTS

Module succed with all information about the instance of PostgreSQL

ACTUAL RESULTS

fatal: [target]: FAILED! => {
    "changed": false
}

MSG:

Cannot execute SQL 'SHOW pg_partman_bgw.analyze': syntax error at or near "analyze"
LINE 1: SHOW pg_partman_bgw.analyze

OTHERS INFOMATIONS

SHOW pg_partman_bgw.analyze command raise an error but protected with brackets like that SHOW "pg_partman_bgw.analyze" doesn't, because the "analyse" keyword is protected.

I changed return self.__exec_sql("SHOW %s" % setting)[0][0] into return self.__exec_sql('SHOW "%s"' % setting)[0][0] in here and it doesn't raise any error upon execution, i think it could resolve the issue

[hunleyd]

thank you for the report and proposed solution @Jhiliano ! We'll look into this soon.