Some table names are being displayed truncated.

[felipenazario]

SUMMARY

I am querying all the table name of a database and I notice that some names are being displayed truncated, but when I query using the command psql or via python using psycopg2-binary (version 2.9.10) the names are all right.

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.postgresql.postgresql_query

ANSIBLE VERSION

ansible [core 2.17.5]
  config file = /home/xpto/workspace/ansible/linux/ansible.cfg
  configured module search path = ['/home/xpto/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/xpto/workspace/.pyenv_for_3.13.0/lib/python3.13/site-packages/ansible
  ansible collection location = /opt/ansible/collections:/home/xpto/workspace/ansible/linux/collections
  executable location = /home/xpto/workspace/.pyenv_for_3.13.0/bin/ansible
  python version = 3.13.0 (main, Oct  9 2024, 11:03:32) [GCC 7.5.0] (/home/xpto/workspace/.pyenv_for_3.13.0/bin/python)
  jinja version = 3.1.4
  libyaml = False

COLLECTION VERSION

Collection                               Version
---------------------------------------- -------
ansible.eda                              2.2.0
ansible.netcommon                        7.1.0
ansible.posix                            1.6.1
ansible.utils                            5.1.2
ansible.windows                          2.5.0
chocolatey.chocolatey                    1.5.3
community.crypto                         2.22.2
community.digitalocean                   1.27.0
community.docker                         3.13.1
community.general                        9.5.0
community.library_inventory_filtering_v1 1.0.1
community.mysql                          3.10.3
community.postgresql                     3.7.0
community.windows                        2.3.0
containers.podman                        1.16.1
cyberark.conjur                          1.3.0
dellemc.os6                              1.0.7
dellemc.os9                              1.0.4
digitalocean.cloud                       0.6.0
kubernetes.core                          5.0.0
oracle.oci                               5.3.0

CONFIGURATION

CALLBACKS_ENABLED(/home/xpto/workspace/ansible/linux/ansible.cfg) = ['ansible.posix.timer', 'ansible.posix.profile_roles', 'ansible.posix.profile_tasks', 'ansible.builtin.oneline']
COLLECTIONS_PATHS(/home/xpto/workspace/ansible/linux/ansible.cfg) = ['/opt/ansible/collections', '/home/xpto/workspace/ansible/linux/collections']
CONFIG_FILE() = /home/xpto/workspace/ansible/linux/ansible.cfg
DEFAULT_FORKS(/home/xpto/workspace/ansible/linux/ansible.cfg) = 10
DEFAULT_HOST_LIST(/home/xpto/workspace/ansible/linux/ansible.cfg) = ['/home/xpto/workspace/ansible/linux/inventories/vagrant/hosts.ini']
DEFAULT_PRIVATE_ROLE_VARS(/home/xpto/workspace/ansible/linux/ansible.cfg) = True
DEFAULT_ROLES_PATH(/home/xpto/workspace/ansible/linux/ansible.cfg) = ['/opt/ansible/roles', '/home/xpto/workspace/ansible/linux/roles']
DEFAULT_TIMEOUT(/home/xpto/workspace/ansible/linux/ansible.cfg) = 15
DEFAULT_VAULT_IDENTITY_LIST(/home/xpto/workspace/ansible/linux/ansible.cfg) = ['prod@keys/ansible_vault/prod.sec']
DEPRECATION_WARNINGS(/home/xpto/workspace/ansible/linux/ansible.cfg) = True
HOST_KEY_CHECKING(/home/xpto/workspace/ansible/linux/ansible.cfg) = False
INTERPRETER_PYTHON(/home/xpto/workspace/ansible/linux/ansible.cfg) = auto

OS / ENVIRONMENT

uname -a

Linux localhost.localdomain 5.3.18-150300.59.106-default #1 SMP Mon Dec 12 13:16:24 UTC 2022 (774239c) x86_64 x86_64 x86_64 GNU/Linux

STEPS TO REPRODUCE

Create the PostgreSQL using Docker for a quick and isolated test.

docker run \
--name postgresql_16 \
-p 5432:5432 \
-v postgresql_16.4.x_data:/var/lib/postgresql/data \
-e POSTGRES_DB=postgres \
-e POSTGRES_USER=postgres \
-e POSTGRES_PASSWORD=123 \
--restart=unless-stopped \
-d \
postgres:16.4-alpine3.20

Connect to the container and then to the PostgreSQL.

docker container exec -it postgresql_16 /bin/bash
su postgres -
psql

Create the database and exit.

CREATE DATABASE form
    WITH  OWNER = postgres  ENCODING = 'UTF8'  LC_COLLATE = 'en_US.utf8'  LC_CTYPE = 'en_US.utf8'  TABLESPACE = pg_default  CONNECTION LIMIT = -1;

quit;

Connect to the form database.

psql -d form

Create the tables.

CREATE TABLE public.lime_survey_xpto (
    id serial NOT NULL,
    CONSTRAINT lime_survey_xpto_pkey PRIMARY KEY (id)
) TABLESPACE pg_default;

CREATE TABLE public.lime_survey_258123 (
    id serial NOT NULL,
    CONSTRAINT lime_survey_258123_pkey PRIMARY KEY (id)
) TABLESPACE pg_default;

CREATE TABLE public.lime_old_survey_358156_20230628123112 (
    id serial NOT NULL,
    CONSTRAINT lime_survey_358156_pkey PRIMARY KEY (id)
) TABLESPACE pg_default;

CREATE TABLE public.lime_old_survey_911213_20220916112346 (
    id serial NOT NULL,
    CONSTRAINT lime_survey_911213_pkey PRIMARY KEY (id)
) TABLESPACE pg_default;

Query the form database.

SELECT
  CONCAT(
    current_database(),
    '.',
    table_schema,
    '.',
    table_name
  ) as query
FROM
  information_schema.tables
WHERE
  table_type = 'BASE TABLE'
  AND table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY
  1;

EXPECTED RESULTS

The correct output will be the complete name of the tables.

                       query
---------------------------------------------------
 form.public.lime_old_survey_358156_20230628123112
 form.public.lime_old_survey_911213_20220916112346
 form.public.lime_survey_258123
 form.public.lime_survey_xpto
(4 rows)

ACTUAL RESULTS

Query the same SQL using Ansible.

# ansible-playbook pb_teste_localhost.yml
---
- name: Tests at localhost.
  hosts: localhost
  become: false
  gather_facts: false

  tasks:
    - name: Query
      community.postgresql.postgresql_query:
        login_db: form
        login_host: localhost
        login_user: postgres
        login_password: 123
        login_port: 5432
        encoding: UTF-8
        query: |
          SELECT
            CONCAT(
              current_database(),
              '.',
              table_schema,
              '.',
              table_name
            ) as query
          FROM
            information_schema.tables
          WHERE
            table_type = 'BASE TABLE'
            AND table_schema NOT IN ('information_schema', 'pg_catalog')
          ORDER BY
            1;
      register: result

    - name: "Show it."
      ansible.builtin.debug:
        msg: "{{ result.query_all_results }}"

The output of the table names will be truncated, when it should not be.

PLAY [Tests at localhost.] ************************************************************************************************

TASK [Query] **************************************************************************************************************
Thursday 31 October 2024  15:49:23 -0300 (0:00:00.028)       0:00:00.028 ****** 
Thursday 31 October 2024  15:49:23 -0300 (0:00:00.025)       0:00:00.025 ****** 
ok: [my_machine]

TASK [Show it.] ***********************************************************************************************************
Thursday 31 October 2024  15:49:23 -0300 (0:00:00.712)       0:00:00.741 ****** 
Thursday 31 October 2024  15:49:23 -0300 (0:00:00.712)       0:00:00.738 ****** 
ok: [my_machine] => {
    "msg": [
        [
            {
                "query": "form.public.lime_old_survey_358156_20230628********112"
            },
            {
                "query": "form.public.lime_old_survey_911213_202209161********46"
            },
            {
                "query": "form.public.lime_survey_258********"
            },
            {
                "query": "form.public.lime_survey_xpto"
            }
        ]
    ]
}

PLAY RECAP ****************************************************************************************************************
my_machine              : ok=2    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

[felipenazario]

The code below is via python using psycopg2-binary (version 2.9.10) and the names are all right.

import psycopg2
from psycopg2 import sql

# Define the database connection parameters
# Change for the IP of the container
db_config = {
    'dbname': 'form',
    'user': 'postgres',
    'password': '123',
    'host': '172.17.0.2',
    'port': '5432'
}

# Function to execute a query and fetch results
def query_database(query):
    try:
        # Establish a connection to the database
        with psycopg2.connect(**db_config) as conn:
            # Create a cursor object
            with conn.cursor() as cursor:
                # Execute the query
                cursor.execute(sql.SQL(query))

                # Fetch all results
                results = cursor.fetchall()

                # Return the results
                return results

    except psycopg2.Error as e:
        print(f"An error occurred: {e}")
    finally:
        # The connection will be automatically closed after exiting the 'with' block
        pass

# Sample usage
query = """
SELECT
    CONCAT(
        current_database(),
        '.',
        table_schema,
        '.',
        table_name
    ) as query
FROM
    information_schema.tables
WHERE
    table_type = 'BASE TABLE'
    AND table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY
    1;
"""
results = query_database(query)

# Display results
for row in results:
    print(row)

The output.

('form.public.lime_old_survey_358156_20230628123112',)
('form.public.lime_old_survey_911213_20220916112346',)
('form.public.lime_survey_258123',)
('form.public.lime_survey_xpto',)

[Andersson007]

@felipenazario thanks for reporting the issue
i think it lies in ansible-core.
The module execution ends with passing data to exit_json() method of the AnsibleModule class to be returned to the user.
It can transform data under the hood along the way following its internal logic.
Maybe it's a result of also ansible.builtin.debug module.
Could you please remove ansible.builtin.debug: from your playbook, run it with -vvv and check if it returns the same?

[betanummeric]

Hi @felipenazario, it looks like the output is not truncated, but censored. The module attempts to avoid logging the login_password by replacing its occurrences in the output with ********. Your password 123 happens to occur within the table names, so it gets replaced.
Maybe the best solution is to change your password to something which definitely won't appear in the output.

I think the censoring only affects the Ansible log, the variable holds the unchanged result and can be used in other tasks (for example to write the result to some file).
I'm not sure if there is a good solution to prevent your issue.