postgresql_table fails when multiple tables with the same name exist in different schemas

[rlaager]

SUMMARY

The postgresql_table command shows spurious change status (and attempts unnecessary ALTER TABLE...OWNER TO) when multiple schemas have tables of the same name (which is the table being operated on by the task).

This happens because the query to get the existing information does not properly limit by schema name for the pg_tables view. It only limits for the pg_namespace part of the query. As a result, this query returns multiple rows, one per schema containing a table of the specified name.

community.postgresql/plugins/modules/postgresql_table.py

Lines 300 to 305 in 1317e05

	query = ("SELECT t.tableowner, t.tablespace, c.reloptions "
	"FROM pg_tables AS t "
	"INNER JOIN pg_class AS c ON c.relname = t.tablename "
	"INNER JOIN pg_namespace AS n ON c.relnamespace = n.oid "
	"WHERE t.tablename = %(tblname)s "
	"AND n.nspname = %(schema)s")

The fix is trivial (adjust to your line wrapping, etc. taste):

--- postgresql_table.py	2025-03-27 11:09:50.844701249 -0500
+++ postgresql_table.py	2025-03-27 11:10:13.736899379 -0500
@@ -1,6 +1,6 @@
         query = ("SELECT t.tableowner, t.tablespace, c.reloptions "
                  "FROM pg_tables AS t "
                  "INNER JOIN pg_class AS c ON  c.relname = t.tablename "
-                 "INNER JOIN pg_namespace AS n ON c.relnamespace = n.oid "
+                 "INNER JOIN pg_namespace AS n ON t.schemaname = n.nspname AND c.relnamespace = n.oid "
                  "WHERE t.tablename = %(tblname)s "
                  "AND n.nspname = %(schema)s")

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_table

ANSIBLE VERSION

ansible [core 2.17.1]
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/srv/ansible/modules']
  ansible python module location = /srv/ansible/.venv/lib/python3.12/site-packages/ansible
  ansible collection location = /srv/ansible/.venv/share/ansible/collections:/home/cskappel/.ansible/collections:/usr/share/ansible/collections:/usr/local/share/ansible/collections
  executable location = /srv/ansible/.venv/bin/ansible
  python version = 3.12.3 (main, Jan 17 2025, 18:03:48) [GCC 13.3.0] (/srv/ansible/.venv/bin/python)
  jinja version = 3.1.4
  libyaml = True

COLLECTION VERSION

# /srv/ansible/.venv/share/ansible/collections/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 3.4.0  

CONFIGURATION

ANSIBLE_NOCOWS(/etc/ansible/ansible.cfg) = True
CACHE_PLUGIN(/etc/ansible/ansible.cfg) = jsonfile
CACHE_PLUGIN_CONNECTION(/etc/ansible/ansible.cfg) = /var/cache/ansible/facts
CACHE_PLUGIN_TIMEOUT(/etc/ansible/ansible.cfg) = 129600
COLLECTIONS_PATHS(/etc/ansible/ansible.cfg) = ['/srv/ansible/.venv/share/ansible/collections', '/home/cskappel/.ansible/collection>
CONFIG_FILE() = /etc/ansible/ansible.cfg
DEFAULT_ACTION_PLUGIN_PATH(/etc/ansible/ansible.cfg) = ['/usr/share/ansible/plugins/action', '/srv/ansible/plugins/action']
DEFAULT_BECOME(/etc/ansible/ansible.cfg) = True
DEFAULT_BECOME_USER(/etc/ansible/ansible.cfg) = root
DEFAULT_CALLBACK_PLUGIN_PATH(/etc/ansible/ansible.cfg) = ['/usr/share/ansible/plugins/callback', '/srv/ansible/plugins/callback']
DEFAULT_FORCE_HANDLERS(/etc/ansible/ansible.cfg) = True
DEFAULT_GATHERING(/etc/ansible/ansible.cfg) = smart
DEFAULT_HOST_LIST(/etc/ansible/ansible.cfg) = ['/srv/ansible/inventories']
DEFAULT_LOG_PATH(/etc/ansible/ansible.cfg) = /var/log/ansible/ansible.log
DEFAULT_MODULE_PATH(/etc/ansible/ansible.cfg) = ['/srv/ansible/modules']
DEFAULT_REMOTE_USER(/etc/ansible/ansible.cfg) = ansible
DEFAULT_ROLES_PATH(/etc/ansible/ansible.cfg) = ['/srv/ansible/roles']
DEFAULT_STDOUT_CALLBACK(/etc/ansible/ansible.cfg) = wiktel
DEFAULT_STRATEGY(/etc/ansible/ansible.cfg) = host_pinned
DEFAULT_TIMEOUT(/etc/ansible/ansible.cfg) = 60
DEFAULT_VAULT_PASSWORD_FILE(/etc/ansible/ansible.cfg) = /etc/ansible/vault_password
DIFF_ALWAYS(/etc/ansible/ansible.cfg) = True
DISPLAY_SKIPPED_HOSTS(/etc/ansible/ansible.cfg) = False
INJECT_FACTS_AS_VARS(/etc/ansible/ansible.cfg) = False
INVENTORY_ENABLED(/etc/ansible/ansible.cfg) = ['yaml']
PARAMIKO_HOST_KEY_AUTO_ADD(/etc/ansible/ansible.cfg) = True

OS / ENVIRONMENT

The target OS is Debian 12 with PostgreSQL 15.

STEPS TO REPRODUCE

1. Use the postgresql_table (e.g. in a loop) to create multiple tables of the same name in different schemas.
2. Run the task again.

In our case, the actual task looks like this, where pbx_instances is a dictionary in the inventory where the keys are strings, e.g. office and krls-pbx2 and pbx_freeswitch_database_username is freeswitch.

- name: create voicemail prefs table
  become: yes
  become_user: postgres
  community.postgresql.postgresql_table:
    db: freeswitch
    name: >-
      {{ pbx_freeswitch_database_username }}_{{ item|replace('-', '_') }}.voicemail_prefs
    columns:
      - username                  varchar
      - domain                    varchar
      - name_path                 varchar
      - greeting_path             varchar
      - password                  varchar
    owner:
      "{{ pbx_freeswitch_database_username }}_{{ item|replace('-', '_') }}"
  # This will fail if PostgreSQL has not been installed, etc.
  ignore_errors: "{{ ansible_check_mode }}"
  loop: "{{ pbx_instances.keys() }}"
  loop_control:
    label:
      "{{ pbx_freeswitch_database_username }}_{{ item|replace('-', '_') }}"

If we eliminate the variables for simplicity, and add tasks to create the pre-requisite users/roles and schemas, we get this:

- name: create freeswitch database users
  become: yes
  become_user: postgres
  community.postgresql.postgresql_user:
    name: "freeswitch_{{ item }}"
    password: "PASSWORD"
  loop:
    - office
    - krls_pbx2
- name: create freeswitch database schemas
  become: yes
  become_user: postgres
  community.postgresql.postgresql_schema:
    db: freeswitch
    name: "freeswitch_{{ item }}"
    owner: "freeswitch_{{ item }}"
  loop:
    - office
    - krls_pbx2
- name: create voicemail prefs table
  become: yes
  become_user: postgres
  community.postgresql.postgresql_table:
    db: freeswitch
    name: "freeswitch_{{ item }}.voicemail_prefs"
    columns:
      - username                  varchar
      - domain                    varchar
      - name_path                 varchar
      - greeting_path             varchar
      - password                  varchar
    owner: "freeswitch_{{ item }}"
  loop:
    - office
    - krls_pbx2

EXPECTED RESULTS

The task should not try to do anything (i.e. not try to run any SQL) and should not show a changed status.

ACTUAL RESULTS

On the second run, for all but one of the schemas (whichever one the pg_table query lists first):

• The task tries to run:

ALTER TABLE "schema_name"."table_name" OWNER TO "owner_name"

• As the table's owner is already the desired value, this results in no actual changes to the state of the database.
• The task shows a changed status.

changed: [krls1.sip.wiktel.com] => (item=office) => changed=true
  ansible_loop_var: item
  invocation:
    module_args:
      ca_cert: null
      cascade: false
      columns:
      - username                  varchar
      - domain                    varchar
      - name_path                 varchar
      - greeting_path             varchar                                                                                                - password                  varchar                                                                                                connect_params: {}
      db: freeswitch
      including: null
      like: null
      login_host: ''
      login_password: ''
      login_unix_socket: ''
      login_user: postgres
      name: freeswitch_office.voicemail_prefs
      owner: freeswitch_office
      port: 5432
      rename: null
      session_role: null
      ssl_cert: null
      ssl_key: null
      ssl_mode: prefer
      state: present
      storage_params: null
      table: freeswitch_office.voicemail_prefs
      tablespace: null
      truncate: false
      trust_input: true
      unlogged: false
  item: office
  owner: freeswitch_krls_pbx2
  queries:
  - ALTER TABLE "freeswitch_office"."voicemail_prefs" OWNER TO "freeswitch_office"
  state: present
  storage_params: []
  table: freeswitch_office.voicemail_prefs
  tablespace: ''

Note how it thinks the owner is freeswitch_krls_pbx2, when it's actually already freeswitch_office.

I did not actually test this reproducer in an empty database. Depending on the order of the rows in PostgreSQL's internal tables, it's possible this will reproduce in the opposite way (i.e. "office" will show no change but "krls_pbx2" will show a change.)

[hunleyd]

thanks for the report @rlaager and the PR!