ansible-collections / community.postgresql

Manage PostgreSQL with Ansible

PostgreSQL collection for Ansible

This collection is a part of the Ansible package.

Our mission

At the community.postgresql Ansible collection project, our mission is to produce and maintain simple, flexible, and powerful open-source software tailored to automating PostgreSQL-related tasks.

We welcome members from all skill levels to participate actively in our open, inclusive, and vibrant community. Whether you are an expert or just beginning your journey with Ansible and PostgreSQL, you are encouraged to contribute, share insights, and collaborate with fellow enthusiasts.

We strive to make managing PostgreSQL deployments as effortless and efficient as possible with automation, enabling users to focus on their core objectives.

Code of Conduct

We follow the Ansible Code of Conduct in all our interactions within this project.

If you encounter abusive behavior violating the Ansible Code of Conduct, please refer to the policy violations section of the Code of Conduct for information on how to raise a complaint.

Communication

• Join the Ansible forum:

  • PostgreSQL Team: by joining the team you will automatically get subscribed to the posts tagged with postgresql.
  • Get Help: get help or help others.
  • Posts tagged with 'postgresql': leverage tags to narrow the scope.
  • Social Spaces: gather and interact with fellow enthusiasts.
  • News & Announcements: track project-wide announcements including social events.

• The Ansible Bullhorn newsletter: used to announce releases and important changes.

• Matrix: chat real-time about contributing/maintenance-related topics in the #postgresql:ansible.com room.

For more information about communication see the Ansible communication guide.

Contributing to this collection

The content of this collection is made by people just like you; a community of individuals collaborating on making the world better through developing automation software.

We are actively accepting new contributors and all types of contributions are very welcome.

You don't know how to start? Refer to our contribution guide!

We use the following guidelines:

• CONTRIBUTING.md
• Ansible Community Guide
• Ansible Development Guide
• Ansible Collection Development Guide

Collection maintenance

The current maintainers (contributors with write or higher access) are listed in the MAINTAINERS file. If you have questions or need help, feel free to mention them in the proposals.

To learn how to maintain / become a maintainer of this collection, refer to the Maintainer guidelines.

It is necessary for maintainers of this collection to be subscribed to:

• The collection itself (the Watch button -> All Activity in the upper right corner of the repository's homepage).
• The news-for-maintainers repository.

They also should be subscribed to Ansible's The Bullhorn newsletter.

Governance

We, the PostgreSQL working group, use the forum posts tagged with postgresql for general announcements and discussions.

The process of decision making in this collection is based on discussing and finding consensus among participants.

Every voice is important and every idea is valuable. If you have something on your mind, create an issue or dedicated forum discussion and let's discuss it!

External requirements

The PostgreSQL modules rely on the Psycopg PostgreSQL database adapter. Both versions Psycopg2 and Psycopg3 are supported. The minimum supported and tested versions of Psycopg are 2.5.1 and 3.1.8 respectively.

Releases Support Timeline

We maintain each major release version (1.x.y, 2.x.y, ...) for two years after the next major version is released.

Here is the table for the support timeline:

• 1.x.y: released 2020-11-17, EOL
• 2.x.y: released 2022-02-10, EOL
• 3.x.y: released 2023-06-09, current
• 4.x.y: to be released; not earlier than after Ansible 10 release (~May 2024)

Tested with ansible-core

Tested with the following ansible-core releases:

• 2.15
• 2.16
• 2.17
• current development version

Ansible-core versions before 2.12.0 are not supported. Our AZP CI includes testing with the following docker images / PostgreSQL versions:

Docker image	Psycopg version	PostgreSQL version
RHEL 8	2.7.5	10
Fedora 37	2.9.6	14
Fedora 38	2.9.6	15
Fedora 39	2.9.6	15
Ubuntu 20.04	2.8.6	15
Ubuntu 22.04	3.1.9	15

Included content

• Info modules:

  • postgresql_info
  • postgresql_ping
  • postgresql_user_obj_stat_info

• Basic modules:

  • postgresql_db
  • postgresql_ext
  • postgresql_lang
  • postgresql_pg_hba
  • postgresql_privs
  • postgresql_set
  • postgresql_schema
  • postgresql_tablespace
  • postgresql_query
  • postgresql_user

• Other modules:

  • postgresql_copy
  • postgresql_idx
  • postgresql_membership
  • postgresql_owner
  • postgresql_publication
  • postgresql_sequence
  • postgresql_slot
  • postgresql_subscription
  • postgresql_table

Using this collection

Installing the Collection from Ansible Galaxy

Before using the PostgreSQL collection, you need to install it with the Ansible Galaxy command-line tool:

ansible-galaxy collection install community.postgresql

You can include it in a requirements.yml file and install it via ansible-galaxy collection install -r requirements.yml, using the format:

---
collections:
  - name: community.postgresql

You can also download the tarball from Ansible Galaxy and install the collection manually wherever you need.

Note that if you install the collection from Ansible Galaxy with the command-line tool or tarball, it will not be upgraded automatically when you upgrade the Ansible package. To upgrade the collection to the latest available version, run the following command:

ansible-galaxy collection install community.postgresql --upgrade

You can also install a specific version of the collection, for example, if you need to downgrade when something is broken in the latest version (please report an issue in this repository). Use the following syntax:

ansible-galaxy collection install community.postgresql:==X.Y.Z

See Ansible Using collections for more details.

Release notes

See the changelog.

More information

• Ansible Collection overview
• Ansible User guide
• Ansible Developer guide
• Ansible Community code of conduct

Licensing

GNU General Public License v3.0 or later.

See LICENSE to see the full text.

community.postgresql's Issues

zero-length delimited error granting group to role using postgresql_privs module

SUMMARY

Receiving the following error when granting group access to a role using community.general.postgresql_privs module. I did not have any problems using postgresql_privs module with ansible 2.8 and 2.9 for the same purpose and same args.

"msg": "zero-length delimited identifier at or near """"\nLINE 1: GRANT ""REDACTED-ROLE"" TO "REDACTED-USER";\n ^\n"

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.general.postgresql_privs

ANSIBLE VERSION

ansible 2.10.4
  config file = None
  configured module search path = ['/REDACT/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /REDACT/ansible-venv/ansible10/lib/python3.8/site-packages/ansible
  executable location = /REDACT/ansible-venv/ansible10/bin/ansible
  python version = 3.8.5 (default, Jul 28 2020, 12:59:40) [GCC 9.3.0]

CONFIGURATION

"module_args": {
    "ca_cert": null,
    "database": "REDACTED",
    "fail_on_role": true,
    "grant_option": false,
    "host": "REDACTED",
    "login": "REDACTED",
    "login_host": "REDACTED",
    "login_password": "REDACTED",
    "login_unix_socket": "",
    "login_user": "REDACTED",
    "objs": "REDACTED",
    "password": "REDACTED",
    "port": REDACTED,
    "privs": null,
    "roles": "REDACTED",
    "schema": null,
    "session_role": null,
    "ssl_mode": "require",
    "state": "present",
    "target_roles": null,
    "trust_input": true,
    "type": "group",
    "unix_socket": ""
}

Note the user name has "_" character in it.

OS / ENVIRONMENT

Ubuntu Focal

STEPS TO REPRODUCE

EXPECTED RESULTS

ACTUAL RESULTS

community.postgresql.postgresql_set incorrectly quotes strings containing commas

just tested the latest release (1.2.0) since it was only released 15h ago, devel doesn't have any notable changes i can see which would affect this bug.

SUMMARY

when using postgresql_set with a string value that contains a comma, for some reason it puts extra doublequotes (") around the string, breaking it when it's included in postgresql.auto.conf:

password_encryption = 'scram-sha-256'
shared_preload_libraries = '"pg_stat_statements,repmgr"'

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.postgresql.postgresql_set

ANSIBLE VERSION

ansible 2.10.6
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/home/tessa/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/tessa/.local/lib/python3.9/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.9.2 (default, Feb 20 2021, 18:40:11) [GCC 10.2.0]

CONFIGURATION

OS / ENVIRONMENT

EndeavourOS (Arch variant) running Ansible from system ansible package, version 3.1.0.

STEPS TO REPRODUCE

in role defaults/main.yml:

postgres:
  config:
    password_encryption: scram-sha-256
    shared_preload_libraries: pg_stat_statements,repmgr

and in role tasks/main.yml

- name: configure postgresql
  community.postgresql.postgresql_set:
    name: "{{ item.key }}"
    value: "{{ item.value }}"
  become_user: postgres
  loop: "{{ postgres.config | dict2items }}"
  notify: restart postgres

EXPECTED RESULTS

keys with values that contain commas get correctly rendered out to postgresql and entered into the postgresql.auto.conf without extraneous quotes.

ACTUAL RESULTS

for some reason module renders values with commas with extra double quotes around them, breaking postgres config. I've verified that if I change the above config line to the following, it works correctly and doesn't inject extra quotes:

    shared_preload_libraries: repmgr

interestingly, ansible doesn't show the extra quotes in the command output, they only show up in the destination file itself:

TASK [postgres : configure postgresql] ********************************************
changed: [mgmt1] => (item={'key': 'password_encryption', 'value': 'scram-sha-256'})
changed: [mgmt1] => (item={'key': 'shared_preload_libraries', 'value': 'pg_stat_statements,repmgr'})
[...]
$ cat /var/lib/postgresql/12/main# cat postgresql.auto.conf
[...]
password_encryption = 'scram-sha-256'
shared_preload_libraries = '"pg_stat_statements,repmgr"'

general.postgresql_user reporting changes on every run

SUMMARY

First time using this module. I created database and user with privileges on that database

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.general.postgresql_user

ANSIBLE VERSION

ansible 2.9.13
  config file = /.../ansible.cfg
  configured module search path = [u'/.../.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python2.7/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 2.7.5 (default, Apr  2 2020, 13:16:51) [GCC 4.8.5 20150623 (Red Hat 4.8.5-39)]

CONFIGURATION

HOST_KEY_CHECKING(.../ansible.cfg) = False

OS / ENVIRONMENT

Amazon Linux 2

STEPS TO REPRODUCE

- name: Create stock user and grant access to stock database
  community.general.postgresql_user:
    login_host: xxx.eu-central-1.rds.amazonaws.com
    login_user: admin
    login_password: adminpassword
    db: stock
    name: stock
    password: userpassword
    encrypted: yes
    priv: ALL
    expires: infinity

EXPECTED RESULTS

After second run I expected status ok=1 changed=0

postgresql_db dump gives incorrect error 'password authentication failed'

SUMMARY

When no permission to write on target during dump, postgresql_db gives wrong error message "FATAL: password authentication failed for user". It works fine when become: yes

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_db:
   name: acme
   state: dump
   target: /mnt/postgres/backup/acme.sql.gz

ANSIBLE VERSION

ansible 2.9.13
  config file = /data/asif/ansible/ansible.cfg
  configured module search path = ['/home/acag-asif.iqbal/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.6/site-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 3.6.8 (default, Aug 13 2020, 07:46:32) [GCC 4.8.5 20150623 (Red Hat 4.8.5-39)]

CONFIGURATION

DEFAULT_CALLBACK_WHITELIST(/data/asif/ansible/ansible.cfg) = ['profile_roles', 'profile_tasks', 'timer', 'default']
DEFAULT_HOST_LIST(/data/asif/ansible/ansible.cfg) = ['/data/asif/ansible/hosts']
DEFAULT_ROLES_PATH(/data/asif/ansible/ansible.cfg) = ['/data/asif/ansible/roles']
DEFAULT_VAULT_PASSWORD_FILE(/data/asif/ansible/ansible.cfg) = /data/asif/ansible/my-vault-pass

OS / ENVIRONMENT

$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.9 (Maipo)

STEPS TO REPRODUCE

$ ansible-playbook --tags database playbooks/dev/jira_refresh.yml

- hosts: jira_dev
  gather_facts: no
  become: yes

  tasks:
   - name: Stop jira
     service: 
       name: jira
       state: stopped
     tags: stop


- hosts: localhost
  connection: local
  gather_facts: no
  tags: database

  vars:
    IAM_user: db-user-asif.iqbal
    IAM_profile: dbuser
    UPDATE_DB: no
    DB: testdb

  roles:
    - role: jira_refresh_db
      vars:
        RDSHOST: "{{ PGHOSTS.dev }}"


- hosts: jira_dev
  gather_facts: no
  become: yes
  tags: filesystem

  roles:
    - role: jira_refresh_filesystem

EXPECTED RESULTS

When I uncomment become: yes, the task runs fine.

- name: Backup the original database {{ DB }}
  postgresql_db:
    name: jira
    state: dump
    target: "{{ DB_BACKUP_BASE_DIR }}/jira/jira_orig.sql.gz"
    login_user: jirauser
    login_password: "{{ JIRA_DB_PASS }}"
    login_host: "{{ RDSHOST }}"
  #become: yes

ACTUAL RESULTS

TASK [jira_refresh_db : Backup the original database testdb] *******************************************************
task path: /data/asif/ansible/roles/jira_refresh_db/tasks/main.yml:26
Thursday 11 March 2021  20:13:10 +0000 (0:00:00.104)       0:00:01.780 ******** 
Thursday 11 March 2021  20:13:10 +0000 (0:00:00.104)       0:00:01.778 ******** 
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: acag-asif.iqbal
<127.0.0.1> EXEC /bin/sh -c 'echo ~acag-asif.iqbal && sleep 0'
<127.0.0.1> EXEC /bin/sh -c '( umask 77 && mkdir -p "` echo /home/acag-asif.iqbal/.ansible/tmp `"&& mkdir "` echo /home/acag-asif.iqbal/.ansible/tmp/ansible-tmp-1615493590.206678-29943-142257380365820 `" && echo ansible-tmp-1615493590.206678-29943-142257380365820="` echo /home/acag-asif.iqbal/.ansible/tmp/ansible-tmp-1615493590.206678-29943-142257380365820 `" ) && sleep 0'
Using module file /usr/local/lib/python3.6/site-packages/ansible/modules/database/postgresql/postgresql_db.py
<127.0.0.1> PUT /home/acag-asif.iqbal/.ansible/tmp/ansible-local-298981a9mp82i/tmpbibqzmsg TO /home/acag-asif.iqbal/.ansible/tmp/ansible-tmp-1615493590.206678-29943-142257380365820/AnsiballZ_postgresql_db.py
<127.0.0.1> EXEC /bin/sh -c 'chmod u+x /home/acag-asif.iqbal/.ansible/tmp/ansible-tmp-1615493590.206678-29943-142257380365820/ /home/acag-asif.iqbal/.ansible/tmp/ansible-tmp-1615493590.206678-29943-142257380365820/AnsiballZ_postgresql_db.py && sleep 0'
<127.0.0.1> EXEC /bin/sh -c '/usr/bin/python3 /home/acag-asif.iqbal/.ansible/tmp/ansible-tmp-1615493590.206678-29943-142257380365820/AnsiballZ_postgresql_db.py && sleep 0'
<127.0.0.1> EXEC /bin/sh -c 'rm -f -r /home/acag-asif.iqbal/.ansible/tmp/ansible-tmp-1615493590.206678-29943-142257380365820/ > /dev/null 2>&1 && sleep 0'
fatal: [localhost]: FAILED! => {
    "changed": false,
    "cmd": "/usr/bin/gzip </home/acag-asif.iqbal/.ansible/tmp/ansible-tmp-1615493590.206678-29943-142257380365820/pg_fifo > /mnt/postgres/backup/jira/jira_orig.sql.gz & /usr/bin/pg_dump jira --host=mstd-aurora-postgresauroracluste.rds.amazonaws.com --port=5432 --username=jirauser >/home/acag-asif.iqbal/.ansible/tmp/ansible-tmp-1615493590.206678-29943-142257380365820/pg_fifo",
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "conn_limit": "",
            "db": "jira",
            "encoding": "",
            "lc_collate": "",
            "lc_ctype": "",
            "login_host": "mstd-aurora-postgresauroracluster.rds.amazonaws.com",
            "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "login_unix_socket": "",
            "login_user": "jirauser",
            "maintenance_db": "postgres",
            "name": "jira",
            "owner": "",
            "port": 5432,
            "session_role": null,
            "ssl_mode": "prefer",
            "state": "dump",
            "tablespace": "",
            "target": "/mnt/postgres/backup/jira/jira_orig.sql.gz",
            "target_opts": "",
            "template": ""
        }
    },
    "msg": "/bin/sh: /mnt/postgres/backup/jira/jira_orig.sql.gz: Permission denied\npg_dump: [archiver (db)] connection to database \"jira\" failed: FATAL:  password authentication failed for user \"jirauser\"\nFATAL:  password authentication failed for user \"jirauser\"\n",
    "rc": 1,
    "stdout": "",
    "stdout_lines": []
}

Integration tests are temporarily disabled for PostgreSQL module with CentOS 8

Copied from ansible-collections/community.general#649
Initially reported by @Andersson007

SUMMARY

Integration tests are temporarily disabled for PostgreSQL module with CentOS 8 because of hangings during service stop/start.
ansible-collections/community.general#648

The following lilnes added to setup postgresql db role:

  6 - meta: end_play
  7   when: ansible_facts.distribution == 'CentOS' and ansible_facts.distribution_major_version == '8'

Happen when all bunch of tests (not only for postgres) are running.
Everything is ok when running the tests locally in docker using ansible-test.

What could be done:

1. try to use systemd instead of service
2. ...

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql integration tests

Need a rename option for postgresql_db

Rename a database

ISSUE TYPE

• rename database

COMPONENT NAME

• postgresql_db

ADDITIONAL INFORMATION

• Need to rename a database

  - name: rename a database
    postgresql_db:
        name: currentdb
        state: rename
        target: newdb

postgresql_query, Postgresql 13, select from pg_stat_statements -> MODULE FAILURE

From @IgorOhrimenko on Jan 25, 2021 12:53

SUMMARY

postgresql_query MODULE FAILURE when SELECT * FROM pg_stat_statements LIMIT 1

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_query

ANSIBLE VERSION

ansible 2.9.6
  config file = /home/igor/ansible-playbooks/ansible.cfg
  configured module search path = ['/home/igor/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3/dist-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.8.5 (default, Jul 28 2020, 12:59:40) [GCC 9.3.0]

CONFIGURATION

DEFAULT_GATHER_TIMEOUT(/home/igor/ansible-playbooks/ansible.cfg) = 20
DEFAULT_LOAD_CALLBACK_PLUGINS(/home/igor/ansible-playbooks/ansible.cfg) = True
DEFAULT_LOG_PATH(/home/igor/ansible-playbooks/ansible.cfg) = /tmp/ansible.log
DEFAULT_STDOUT_CALLBACK(/home/igor/ansible-playbooks/ansible.cfg) = debug
DEPRECATION_WARNINGS(/home/igor/ansible-playbooks/ansible.cfg) = False
HOST_KEY_CHECKING(/home/igor/ansible-playbooks/ansible.cfg) = False
INTERPRETER_PYTHON(/home/igor/ansible-playbooks/ansible.cfg) = auto_silent

OS / ENVIRONMENT

PostgreSQL 13

STEPS TO REPRODUCE

docker run -p 5432:5432 --name postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres -c shared_preload_libraries='pg_stat_statements'
docker exec postgres psql -U postgres -c 'CREATE EXTENSION IF NOT EXISTS pg_stat_statements'
ansible-playbook -i 'localhost,' pg-test.yaml -v

---
- hosts: localhost
  tasks:
    - name: Select query to db acme with positional arguments and non-default credentials
      community.general.postgresql_query:
        login_host: localhost
        db: postgres
        login_user: postgres
        login_password: mysecretpassword
        query: SELECT * FROM pg_stat_statements LIMIT 1

EXPECTED RESULTS

"statusmessage": "SELECT 1"

ACTUAL RESULTS

TASK [Select query to db acme with positional arguments and non-default credentials] ****************************************************************************************************************************
task path: /home/igor/ansible-playbooks/pg-test.yaml:4
<localhost> ESTABLISH SSH CONNECTION FOR USER: None
<localhost> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o ControlPath=/home/igor/.ansible/cp/8a5a4c6a60 localhost '/bin/sh -c '"'"'echo ~ && sleep 0'"'"''
<localhost> (0, b'/home/igor\n', b'')
<localhost> ESTABLISH SSH CONNECTION FOR USER: None
<localhost> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o ControlPath=/home/igor/.ansible/cp/8a5a4c6a60 localhost '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497 `" && echo ansible-tmp-1611578946.6924086-263976548695497="` echo /home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497 `" ) && sleep 0'"'"''
<localhost> (0, b'ansible-tmp-1611578946.6924086-263976548695497=/home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497\n', b'')
Using module file /home/igor/.ansible/collections/ansible_collections/community/general/plugins/modules/postgresql_query.py
<localhost> PUT /home/igor/.ansible/tmp/ansible-local-2725208vzi5rarc/tmp0qqko1c1 TO /home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py
<localhost> SSH: EXEC sftp -b - -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o ControlPath=/home/igor/.ansible/cp/8a5a4c6a60 '[localhost]'
<localhost> (0, b'sftp> put /home/igor/.ansible/tmp/ansible-local-2725208vzi5rarc/tmp0qqko1c1 /home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py\n', b'')
<localhost> ESTABLISH SSH CONNECTION FOR USER: None
<localhost> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o ControlPath=/home/igor/.ansible/cp/8a5a4c6a60 localhost '/bin/sh -c '"'"'chmod u+x /home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/ /home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py && sleep 0'"'"''
<localhost> (0, b'', b'')
<localhost> ESTABLISH SSH CONNECTION FOR USER: None
<localhost> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o ControlPath=/home/igor/.ansible/cp/8a5a4c6a60 -tt localhost '/bin/sh -c '"'"'/usr/bin/python3 /home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py && sleep 0'"'"''
<localhost> (1, b'Traceback (most recent call last):\r\n  File "/home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py", line 102, in <module>\r\n    _ansiballz_main()\r\n  File "/home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py", line 94, in _ansiballz_main\r\n    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\r\n  File "/home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py", line 40, in invoke_module\r\n    runpy.run_module(mod_name=\'ansible_collections.community.general.plugins.modules.postgresql_query\', init_globals=None, run_name=\'__main__\', alter_sys=True)\r\n  File "/usr/lib/python3.8/runpy.py", line 207, in run_module\r\n    return _run_module_code(code, init_globals, run_name, mod_spec)\r\n  File "/usr/lib/python3.8/runpy.py", line 97, in _run_module_code\r\n    _run_code(code, mod_globals, init_globals,\r\n  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code\r\n    exec(code, run_globals)\r\n  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_query.py", line 452, in <module>\r\n  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_query.py", line 448, in main\r\n  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2071, in exit_json\r\n  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2064, in _return_formatted\r\n  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 418, in remove_values\r\n  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 401, in _remove_values_conditions\r\nTypeError: Value of unknown type: <class \'decimal.Decimal\'>, 0\r\n', b'Shared connection to localhost closed.\r\n')
<localhost> Failed to connect to the host via ssh: Shared connection to localhost closed.
<localhost> ESTABLISH SSH CONNECTION FOR USER: None
<localhost> SSH: EXEC ssh -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o ControlPath=/home/igor/.ansible/cp/8a5a4c6a60 localhost '/bin/sh -c '"'"'rm -f -r /home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/ > /dev/null 2>&1 && sleep 0'"'"''
<localhost> (0, b'', b'')
The full traceback is:
Traceback (most recent call last):
  File "/home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py", line 102, in <module>
    _ansiballz_main()
  File "/home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py", line 94, in _ansiballz_main
    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)
  File "/home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py", line 40, in invoke_module
    runpy.run_module(mod_name='ansible_collections.community.general.plugins.modules.postgresql_query', init_globals=None, run_name='__main__', alter_sys=True)
  File "/usr/lib/python3.8/runpy.py", line 207, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/lib/python3.8/runpy.py", line 97, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_query.py", line 452, in <module>
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_query.py", line 448, in main
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2071, in exit_json
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2064, in _return_formatted
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 418, in remove_values
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 401, in _remove_values_conditions
TypeError: Value of unknown type: <class 'decimal.Decimal'>, 0
fatal: [localhost]: FAILED! => {
    "changed": false,
    "rc": 1
}

MSG:

MODULE FAILURE
See stdout/stderr for the exact error

MODULE_STDOUT:

Traceback (most recent call last):
  File "/home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py", line 102, in <module>
    _ansiballz_main()
  File "/home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py", line 94, in _ansiballz_main
    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)
  File "/home/igor/.ansible/tmp/ansible-tmp-1611578946.6924086-263976548695497/AnsiballZ_postgresql_query.py", line 40, in invoke_module
    runpy.run_module(mod_name='ansible_collections.community.general.plugins.modules.postgresql_query', init_globals=None, run_name='__main__', alter_sys=True)
  File "/usr/lib/python3.8/runpy.py", line 207, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/lib/python3.8/runpy.py", line 97, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_query.py", line 452, in <module>
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_query.py", line 448, in main
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2071, in exit_json
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2064, in _return_formatted
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 418, in remove_values
  File "/tmp/ansible_community.general.postgresql_query_payload_w1g2qy8k/ansible_community.general.postgresql_query_payload.zip/ansible/module_utils/basic.py", line 401, in _remove_values_conditions
TypeError: Value of unknown type: <class 'decimal.Decimal'>, 0

MODULE_STDERR:

Shared connection to localhost closed.

Copied from original issue: ansible/ansible#1676

postgresql_query has no option creates and no option removes

SUMMARY

the shell module (and some other generic modules) have an option creates: and an option removes:.

• creates: a filename, when it already exists, this step will not be run.
• removes: a filename, when it does not exist, this step will not be run.
  This helps to easily make using this module (somewhat) idempotent.
  We could add these options to postgresql_query too:
• creates: a query, when it returns records, this step will not be run.
• removes: a query, when it doesn't return records, this step will not be run.

ISSUE TYPE

• Feature Idea

COMPONENT NAME

postgresql_query

ADDITIONAL INFORMATION

I currently have a case where a script to create a user schema should only be run by Ansible, if it doesn't already exist.
We can automate this with a first step which checks and registers, but nicer would be to have an option that runs a query like:

select nspname from pg_namespace where nspname = 'my_schema';

, and only runs the script if it returns 0 rows.

If you agree, I will fix this and the other postgresql_query issues that are currently open...

postgresql_set in check mode fails on non-numeric values containing `B`

SUMMARY

When running in check mode, postgresql_set throws an exception if the value to set contained the letter B, or other binary units like KB, MB, but was not numeric otherwise.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_set

ANSIBLE VERSION

ansible 2.10.5
  config file = /home/k3rni/Source/ansible-bug-b/ansible.cfg
  configured module search path = ['/home/k3rni/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/k3rni/.asdf/installs/python/3.8.6/lib/python3.8/site-packages/ansible
  executable location = /home/k3rni/.asdf/installs/python/3.8.6/bin/ansible
  python version = 3.8.6 (default, Dec 17 2020, 13:42:12) [GCC 10.2.0]

CONFIGURATION

DEFAULT_STDOUT_CALLBACK(/home/k3rni/Source/ansible-bug-b/ansible.cfg) = debug

OS / ENVIRONMENT

N/A

STEPS TO REPRODUCE

Minimal playbook, assumes postgres is on localhost. Provide login_host etc. otherwise.

- hosts: localhost
  tasks:
    - community.general.postgresql_set:
        name: maintenance_work_mem
        value: 100MB

    - community.general.postgresql_set:
        name: archive_command
        value: '/usr/bin/touch %f' # NOTE: contains lowercase B, and not a very useful archive_command to set

Run in check mode:

ansible-playbook -i example.yml -C

EXPECTED RESULTS

Command completes successfully, reporting if the parameters need changing.

ACTUAL RESULTS

[WARNING]: No inventory was parsed, only implicit localhost is available
[WARNING]: provided hosts list is empty, only localhost is available. Note that the implicit localhost does not match 'all'

PLAY [localhost] ****************************************************************************************************************************************************************

TASK [Gathering Facts] **********************************************************************************************************************************************************
ok: [localhost]

TASK [community.general.postgresql_set] *****************************************************************************************************************************************
ok: [localhost]

TASK [community.general.postgresql_set] *****************************************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: ValueError: invalid literal for int() with base 10: ''
fatal: [localhost]: FAILED! => {
    "changed": false,
    "rc": 1
}

MSG:

MODULE FAILURE
See stdout/stderr for the exact error


MODULE_STDERR:

Traceback (most recent call last):
  File "/home/k3rni/.ansible/tmp/ansible-tmp-1611934980.1523125-10330-89490691781095/AnsiballZ_postgresql_set.py", line 102, in <module>
    _ansiballz_main()
  File "/home/k3rni/.ansible/tmp/ansible-tmp-1611934980.1523125-10330-89490691781095/AnsiballZ_postgresql_set.py", line 94, in _ansiballz_main
    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)
  File "/home/k3rni/.ansible/tmp/ansible-tmp-1611934980.1523125-10330-89490691781095/AnsiballZ_postgresql_set.py", line 40, in invoke_module
    runpy.run_module(mod_name='ansible_collections.community.general.plugins.modules.postgresql_set', init_globals=None, run_name='__main__', alter_sys=True)
  File "/home/k3rni/.asdf/installs/python/3.8.6/lib/python3.8/runpy.py", line 207, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/home/k3rni/.asdf/installs/python/3.8.6/lib/python3.8/runpy.py", line 97, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "/home/k3rni/.asdf/installs/python/3.8.6/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/tmp/ansible_community.general.postgresql_set_payload_owlfxker/ansible_community.general.postgresql_set_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_set.py", line 447, in <module>
  File "/tmp/ansible_community.general.postgresql_set_payload_owlfxker/ansible_community.general.postgresql_set_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_set.py", line 376, in main
  File "/tmp/ansible_community.general.postgresql_set_payload_owlfxker/ansible_community.general.postgresql_set_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_set.py", line 255, in pretty_to_bytes
ValueError: invalid literal for int() with base 10: ''

The problem is, as indicated by the traceback, in line 383 of the postgres_set module. In check mode, it passes the value into pretty_to_bytes without checking if it's actually a number. That function, in turn, finds the B in /usr/bin, and attempts to collect all digits, only to come up with none.

Wrong "changed state" with some value (postgresql_set)

SUMMARY

track_activity_query_size the parameter incorrectly processes the dimension of the value in bytes.
When I define track_activity_query_size in bytes (2048B) it is incorrectly defined in kilobytes (2kB),
and call changed state in step

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_set

ANSIBLE VERSION

ansible [core 2.11.2] 
  config file = /home/user/ansible/ansible.cfg
  configured module search path = ['/home/user/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/user/.local/lib/python3.8/site-packages/ansible
  ansible collection location = /home/user/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/user/.local/bin/ansible
  python version = 3.8.10 (default, Jun  2 2021, 10:49:15) [GCC 9.4.0]
  jinja version = 2.10.1
  libyaml = True

COLLECTION VERSION

$ ansible-galaxy collection list

# /home/user/.ansible/collections/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.3.0  

CONFIGURATION

$ ansible-config dump --only-changed
$

OS / ENVIRONMENT

$ cat /etc/os-release
NAME="Ubuntu"
VERSION="20.04.2 LTS (Focal Fossa)"
ID=ubuntu
PRETTY_NAME="Ubuntu 20.04.2 LTS"
VERSION_ID="20.04"
VERSION_CODENAME=focal
UBUNTU_CODENAME=focal

STEPS TO REPRODUCE

just run playbook

---
- hosts: localhost
  vars:

  tasks:
    - name: change config
      community.postgresql.postgresql_set: 
            name: track_activity_query_size
            value: 2048B
      become_user: postgres
      notify: postgresql__restart_service

  handlers:
    - name: postgresql__restart_service
      ansible.builtin.service:
        name: [email protected]
        state: restarted
      become: true

EXPECTED RESULTS

i expected right comparison with kB and B. Maybe it can reproduce for other parameters.
I think module doesn't must call change state for real equal values, because this makes the module useless (in this case).

ACTUAL RESULTS

changed: [localhost] => {
    "changed": true,
    "context": "postmaster",
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "db": null,
            "login_host": "",
            "login_password": "",
            "login_unix_socket": "",
            "login_user": "postgres",
            "name": "track_activity_query_size",
            "port": 5432,
            "reset": false,
            "session_role": null,
            "ssl_mode": "prefer",
            "trust_input": true,
            "value": "2048B"
        }
    },
    "name": "track_activity_query_size",
    "prev_val_pretty": "2kB",
    "restart_required": true,
    "value_pretty": "2048B"
}

postgresql_set failed to import psycopg2

SUMMARY

I'm trying to change wal_level to logical and listen_addresses to * using postgresql_set module. But when I run playbook this task fails. Previous task using postgresql_pg_hba module works correctly.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_set

ANSIBLE VERSION

  ansible [core 2.11.5]
  config file = /Users/m.titov/projects/example-postgres-replication/ansible.cfg
  configured module search path = ['/Users/m.titov/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/Cellar/ansible/4.6.0/libexec/lib/python3.9/site-packages/ansible
  ansible collection location = /Users/m.titov/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/local/bin/ansible
  python version = 3.9.7 (default, Sep  3 2021, 12:37:55) [Clang 12.0.5 (clang-1205.0.22.9)]
  jinja version = 3.0.1
  libyaml = True

COLLECTION VERSION

# /usr/local/Cellar/ansible/4.6.0/libexec/lib/python3.9/site-packages/ansible_collections
Collection        Version
----------------- -------
community.general 3.7.0

ansible-galaxy collection list community.postgresql

# /usr/local/Cellar/ansible/4.6.0/libexec/lib/python3.9/site-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.4.0

# /Users/m.titov/.ansible/collections/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.5.0

CONFIGURATION

DEFAULT_HOST_LIST(/Users/m.titov/projects/example-postgres-replication/ansible.cfg) = ['/Users/m.titov/projects/example-postgres-replication/hosts.ini']

ansible.cfg:
[defaults]
inventory = hosts.ini

OS / ENVIRONMENT

MacOS Big Sur v11.6

STEPS TO REPRODUCE

  tasks:
    - name: Allowing to accept remote connections in pg_hba file
      community.postgresql.postgresql_pg_hba:
        dest: /var/lib/pgsql/14/data/pg_hba.conf
        contype: host
        users: all
        source: 0.0.0.0/0
        method: md5
        databases: all

    - name: Allowing Postgresql remote TCP/IP connections
      community.postgresql.postgresql_set:
        name: listen_adresses
        value: '*'

    - name: Set wal level to logical
      community.postgresql.postgresql_set:
        name: wal_level
        value: logical

EXPECTED RESULTS

Expecting to get "ok" status on a task

ACTUAL RESULTS

The full traceback is:
WARNING: The below traceback may *not* be related to the actual failure.
  File "/tmp/ansible_community.postgresql.postgresql_set_payload_BU2tXD/ansible_community.postgresql.postgresql_set_payload.zip/ansible_collections/community/postgresql/plugins/modules/postgresql_set.py", line 169, in <module>
fatal: [centos]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "db": null,
            "login_host": "",
            "login_password": "",
            "login_unix_socket": "",
            "login_user": "postgres",
            "name": "listen_adresses",
            "port": 5432,
            "reset": false,
            "session_role": null,
            "ssl_mode": "prefer",
            "trust_input": true,
            "value": "*"
        }
    },
    "msg": "Failed to import the required Python library (psycopg2) on pgtnt-replicator.novalocal's Python /usr/bin/python. Please read the module documentation and install it in the appropriate location. If the required library is installed, but Ansible is using the wrong Python interpreter, please consult the documentation on ansible_python_interpreter"
}

postgresql_query fails with 'can't execute an empty query' when a commented out query is present in the script

SUMMARY

When trying to run this script through postgresql_query using path_to_script, the module fails with

Cannot execute SQL: -- create index ttrss_entries_title_index on ttrss_entries(title)' None: can't execute an empty query, query list:
[...]

Deleting the commented-out query line 164 makes the module work again.

ISSUE TYPE

• Bug Report

COMPONENT NAME

posqtgresql_query

ANSIBLE VERSION

ansible 2.10.5
  config file = None
  configured module search path = ['/home/live/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/live/.local/share/xsrv/venv/lib/python3.7/site-packages/ansible
  executable location = /home/live/.local/share/xsrv/venv/bin/ansible
  python version = 3.7.3 (default, Jul 25 2020, 13:03:44) [GCC 8.3.0]

CONFIGURATION

OS / ENVIRONMENT

• Debian 10 Buster on host and controller
• postgresql 11+200+deb10u4

STEPS TO REPRODUCE

# playbook.yml
- hosts: my.EXAMPLE.org
  roles:
    - tt_rss

# roles/tt_rss/tasks/main.yml
- include: tt-rss.yml
  become: yes

# roles/tt_rss/tasks/tt-rss.yml
- name: install postgresql
  apt:
    state: present
    package:
      - postgresql
      - postgresql-client

- name: install packages for postgresql support
  apt:
    state: present
    package:
      - php-pgsql
      - python3-psycopg2

- name: create tt-rss postgresql user
  become: yes
  become_user: postgres
  postgresql_user:
    name: "ttrss"
    state: present

- name: set tt-rss postgresql user password
  become: yes
  become_user: postgres
  postgresql_user:
    name: "ttrss"
    password: "CHANGEME"
  no_log: True

- name: create tt-rss postgresql database
  become: yes
  become_user: postgres
  postgresql_db:
    name: "ttrss"
    state: present
    encoding: 'UNICODE'
    owner: 'ttrss'

- name: grant privileges on tt-rss database to tt-rss postgresql user
  become: yes
  become_user: postgres
  postgresql_privs:
    db: "ttrss"
    privs: ALL
    type: database
    role: "ttrss"

- name: clone/upgrade tt-rss
  git:
    repo: https://git.tt-rss.org/git/tt-rss.git
    dest: "/var/www/rss.EXAMPLE.org"
    version: 'master'
    accept_hostkey: yes
    force: yes

- name: import tt-rss database schema to postgresql
  postgresql_query:
    login_host: "localhost"
    login_user: "ttrss"
    login_password: "CHANGEME"
    db: "ttrss"
    path_to_script: "/var/www/rss.EXAMPLE.org/schema/ttrss_schema_pgsql.sql"

EXPECTED RESULTS

The commented out line should be ignored and the script should execute correctly. Note that running the script with sudo -u postgres psql --file=/var/www/rss.EXAMPLE.org/schema/ttrss_schema_pgsql.sql works without problems.

ACTUAL RESULTS

$ ansible-playbook playbook.yml

[...]

TASK [tt_rss : import tt-rss database schema to postgresql] *************************************************************************************************************
fatal: [my.EXAMPLE.org]: FAILED! => {
    "changed": false
}

MSG:

Cannot execute SQL '

-- create index ttrss_entries_title_index on ttrss_entries(title)' None: can't execute an empty query, query list: ['drop table if exists ttrss_error_log', '\ndrop table if exists ttrss_plugin_storage', '\ndrop table if exists ttrss_linked_feeds', '...................

Inserting the following task before import tt-rss database schema to postgresql makes the playbook work again:

- name: remove comments from SQL schema file
  lineinfile:
    path: "{{ tt_rss_install_dir }}/schema/ttrss_schema_pgsql.sql"
    state: absent
    regexp: '^--.*'

postgresql_cluster: manage psql clusters on a host - not just main

Copied from ansible-collections/community.general#122
Initially reported by @Andersson007

Copied from ansible/ansible#67125
Initially reported by @DEvil0000

Before starting this, please read the discussion in the initial issue

SUMMARY

ansible allows to manage databases and so on for postgresql but misses management of clusters.
PSQL comes with tools to manage this: pg_lsclusters, pg_createcluster and so on.
There should be a postgresql_cluster to do so.

ISSUE TYPE

• Feature Idea

COMPONENT NAME

postgresql

ADDITIONAL INFORMATION

You could have more then one cluster with different DB versions or extensions installed. This makes sense for production and especially for migration scenarios.
PSQL comes with tools to manage this: pg_lsclusters, pg_createcluster and so on.

CI: integration testing against all supported Postgres versions

SUMMARY

• Currently we tests using AZP and several distros with default Postgres versions available for each distro. See .azure-pipelines/azure-pipelines.yml file.
• Postgres 14 has been recently released with some breaking changes and we don't have testing against it yet.

Solution:

• Moving to GitHub actions
• Testing using one distro against all supported Postgres versions + maybe 9.2
• An example is community.mysql collection, .github directory content

Temporary solution (done with Added with #156) can be using one of the distros (say, Ubuntu 20.04 or whatever) to install Postgres 14.

postgresql_query cannot handle interval type

Hey, first off: thanks for this great collection of modules, I find them very useful!

SUMMARY

When trying to SELECT a field of type interval (a PostgreSQL type describing time intervals), postgresql_query fails with

TypeError: Value of unknown type: <class 'datetime.timedelta'>

This datetime.timedelta type seems to come from psycopg2...

I stumbled across this when querying pg_stat_replication, which contains a few columns with type interval.
The funny thing is: as long as e.g. replay_lag is null (meaning there is essentially no time difference between the production of WAL and replaying of the same on a replica) the query works fine. But as soon as there is an actual interval present, execution fails.

I hope this is quite an easy issue to solve, I think it could work when we simply return the datetime.timedelta as a String.

In the meantime, it is possible to work around this issue by casting to epoch or another date/time type in PostgreSQL.

Please let me know if or how I can be of further help when fixing this.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_query

ANSIBLE VERSION

ansible 2.9.16
  config file = /home/julian/test_project/ansible.cfg
  configured module search path = ['/home/julian/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.9/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.9.1 (default, Jan 20 2021, 00:00:00) [GCC 10.2.1 20201125 (Red Hat 10.2.1-9)]

CONFIGURATION

ANSIBLE_PIPELINING(/home/julian/test_project/ansible.cfg) = True
CACHE_PLUGIN(/home/julian/test_project/ansible.cfg) = jsonfile
CACHE_PLUGIN_CONNECTION(/home/julian/test_project/ansible.cfg) = /tmp/ansible_cache/
CACHE_PLUGIN_TIMEOUT(/home/julian/test_project/ansible.cfg) = 86400
DEFAULT_BECOME(/home/julian/test_project/ansible.cfg) = True
DEFAULT_GATHERING(/home/julian/test_project/ansible.cfg) = smart
DEFAULT_REMOTE_USER(/home/julian/test_project/ansible.cfg) = vagrant
HOST_KEY_CHECKING(/home/julian/test_project/ansible.cfg) = False

OS / ENVIRONMENT

Controller:

• Fedora 33
• Python 3.9.1
  Hosts:
• Ubuntu 20.04 Focal Fossa
• Python 3.8.5
• python3-psycopg2/focal,now 2.8.4-2 amd64
• PostgreSQL 12.5 (installed from postgresql.org) psql (12.5 (Ubuntu 12.5-0ubuntu0.20.04.1))

STEPS TO REPRODUCE

Here's a playbook which should be able to reproduce the issue. In my project, there are a few members to the database cluster, that's why all tasks are only run once.

- hosts: db_hosts
  tasks:
  - name: try selecting interval as epoch
    run_once: true
    become: yes
    become_user: postgres
    postgresql_query:
      port: "{{postgres_port}}"
      query: "SELECT EXTRACT(epoch from make_interval(secs => 3));"
    register: interval_epoch_out

  - run_once: true
    debug:
      var: interval_epoch_out

  - name: try selecting interval
    run_once: true
    become: yes
    become_user: postgres
    postgresql_query:
      port: "{{postgres_port}}"
      query: "SELECT make_interval(secs => 3);"
    register: interval_out

  - run_once: true
    debug:
      var: interval_out

The first task (and debug) run fine, but the execution fails on the second usage of postgresql_query.

Here's the same data retrieved by psycopg2:

>>> import psycopg2
>>> conn = psycopg2.connect("dbname=postgres user=postgres")
>>> cur = conn.cursor()
>>> cur.execute("SELECT EXTRACT(epoch from make_interval(secs => 3));")
>>> cur.fetchone()
(3.0,)
>>> cur.execute("SELECT make_interval(secs => 3);")
>>> x = cur.fetchone()
>>> print(x)
(datetime.timedelta(seconds=3),)
>>> print(x[0])
0:00:03
>>> cur.close()
>>> conn.close()
>>> exit

EXPECTED RESULTS

PLAY [db_hosts] ***********************************************************************************************************************************************

TASK [try selecting interval as epoch] ************************************************************************************************************************
[WARNING]: Database name has not been passed, used default database to connect to.
ok: [node1]

TASK [debug] **************************************************************************************************************************************************
ok: [node1] => {
    "interval_epoch_out": {
        "changed": false,
        "failed": false,
        "query": "SELECT EXTRACT(epoch from make_interval(secs => 3));",
        "query_result": [
            {
                "date_part": 3.0
            }
        ],
        "rowcount": 1,
        "statusmessage": "SELECT 1",
        "warnings": [
            "Database name has not been passed, used default database to connect to."
        ]
    }
}

TASK [try selecting interval] *********************************************************************************************************************************
[WARNING]: Database name has not been passed, used default database to connect to.
ok: [node1]

TASK [debug] **************************************************************************************************************************************************
ok: [node1] => {
    "interval_epoch_out": {
        "changed": false,
        "failed": false,
        "query": "SELECT make_interval(secs => 3);",
        "query_result": [
            {
                "interval": "0:00:03"
            }
        ],
        "rowcount": 1,
        "statusmessage": "SELECT 1",
        "warnings": [
            "Database name has not been passed, used default database to connect to."
        ]
    }
}


PLAY RECAP ****************************************************************************************************************************************************
node1                      : ok=4    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0   

ACTUAL RESULTS

PLAY [db_hosts] ***********************************************************************************************************************************************

TASK [try selecting interval as epoch] ************************************************************************************************************************
[WARNING]: Database name has not been passed, used default database to connect to.
ok: [node1]

TASK [debug] **************************************************************************************************************************************************
ok: [node1] => {
    "interval_epoch_out": {
        "changed": false,
        "failed": false,
        "query": "SELECT EXTRACT(epoch from make_interval(secs => 3));",
        "query_result": [
            {
                "date_part": 3.0
            }
        ],
        "rowcount": 1,
        "statusmessage": "SELECT 1",
        "warnings": [
            "Database name has not been passed, used default database to connect to."
        ]
    }
}

TASK [try selecting interval] *********************************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: TypeError: Value of unknown type: <class 'datetime.timedelta'>, 0:00:03
fatal: [node1]: FAILED! => {"changed": false, "module_stderr": "Traceback (most recent call last):\n  File \"<stdin>\", line 102, in <module>\n  File \"<stdin>\", line 94, in _ansiballz_main\n  File \"<stdin>\", line 40, in invoke_module\n  File \"/usr/lib/python3.8/runpy.py\", line 207, in run_module\n    return _run_module_code(code, init_globals, run_name, mod_spec)\n  File \"/usr/lib/python3.8/runpy.py\", line 97, in _run_module_code\n    _run_code(code, mod_globals, init_globals,\n  File \"/usr/lib/python3.8/runpy.py\", line 87, in _run_code\n    exec(code, run_globals)\n  File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 350, in <module>\n  File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 346, in main\n  File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2168, in exit_json\n  File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2161, in _return_formatted\n  File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 426, in remove_values\n  File \"/tmp/ansible_postgresql_query_payload_2z2dmvfp/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 403, in _remove_values_conditions\nTypeError: Value of unknown type: <class 'datetime.timedelta'>, 0:00:03\n", "module_stdout": "", "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", "rc": 1}

NO MORE HOSTS LEFT ********************************************************************************************************************************************

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

Here's more verbose output of the failed task:

TASK [try selecting interval] *********************************************************************************************************************************
task path: /home/julian/test_project/postgresql_query_reproducer.yml:16
Using module file /usr/lib/python3.9/site-packages/ansible/modules/database/postgresql/postgresql_query.py
Pipelining is enabled.
<192.168.178.221> ESTABLISH SSH CONNECTION FOR USER: vagrant
<192.168.178.221> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o 'IdentityFile="misc/vagrant_ubuntu_focal/.vagrant/machines/ubuntu-focal-node-1/virtualbox/private_key"' -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o 'User="vagrant"' -o ConnectTimeout=10 -o ControlPath=/home/julian/.ansible/cp/4983164cf0 192.168.178.221 '/bin/sh -c '"'"'sudo -H -S -n  -u postgres /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-lvleoubjilhmusqcpwkppbrqwrsisqkl ; /usr/bin/python3'"'"'"'"'"'"'"'"' && sleep 0'"'"''
Escalation succeeded
<192.168.178.221> (1, b'', b'OpenSSH_8.4p1, OpenSSL 1.1.1i FIPS  8 Dec 2020\r\ndebug1: Reading configuration data /home/julian/.ssh/config\r\ndebug1: /home/julian/.ssh/config line 1: Applying options for *\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0\r\ndebug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf\r\ndebug2: checking match for \'final all\' host 192.168.178.221 originally 192.168.178.221\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: not matched \'final\'\r\ndebug2: match not found\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1 (parse only)\r\ndebug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config\r\ndebug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]\r\ndebug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]\r\ndebug1: configuration requests final Match pass\r\ndebug2: resolve_canonicalize: hostname 192.168.178.221 is address\r\ndebug1: re-parsing configuration\r\ndebug1: Reading configuration data /home/julian/.ssh/config\r\ndebug1: /home/julian/.ssh/config line 1: Applying options for *\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0\r\ndebug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf\r\ndebug2: checking match for \'final all\' host 192.168.178.221 originally 192.168.178.221\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: matched \'final\'\r\ndebug2: match found\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1\r\ndebug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config\r\ndebug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]\r\ndebug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]\r\ndebug3: expanded UserKnownHostsFile \'~/.ssh/known_hosts\' -> \'/home/julian/.ssh/known_hosts\'\r\ndebug3: expanded UserKnownHostsFile \'~/.ssh/known_hosts2\' -> \'/home/julian/.ssh/known_hosts2\'\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 4 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 308806\r\ndebug3: mux_client_request_session: session request sent\r\ndebug1: mux_client_request_session: master session id: 2\r\nTraceback (most recent call last):\n  File "<stdin>", line 102, in <module>\n  File "<stdin>", line 94, in _ansiballz_main\n  File "<stdin>", line 40, in invoke_module\n  File "/usr/lib/python3.8/runpy.py", line 207, in run_module\n    return _run_module_code(code, init_globals, run_name, mod_spec)\n  File "/usr/lib/python3.8/runpy.py", line 97, in _run_module_code\n    _run_code(code, mod_globals, init_globals,\n  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code\n    exec(code, run_globals)\n  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 350, in <module>\n  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 346, in main\n  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2168, in exit_json\n  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2161, in _return_formatted\n  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 426, in remove_values\n  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 403, in _remove_values_conditions\nTypeError: Value of unknown type: <class \'datetime.timedelta\'>, 0:00:03\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 1\r\n')
<192.168.178.221> Failed to connect to the host via ssh: OpenSSH_8.4p1, OpenSSL 1.1.1i FIPS  8 Dec 2020
debug1: Reading configuration data /home/julian/.ssh/config
debug1: /home/julian/.ssh/config line 1: Applying options for *
debug1: Reading configuration data /etc/ssh/ssh_config
debug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0
debug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf
debug2: checking match for 'final all' host 192.168.178.221 originally 192.168.178.221
debug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: not matched 'final'
debug2: match not found
debug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1 (parse only)
debug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config
debug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]
debug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]
debug1: configuration requests final Match pass
debug2: resolve_canonicalize: hostname 192.168.178.221 is address
debug1: re-parsing configuration
debug1: Reading configuration data /home/julian/.ssh/config
debug1: /home/julian/.ssh/config line 1: Applying options for *
debug1: Reading configuration data /etc/ssh/ssh_config
debug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0
debug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf
debug2: checking match for 'final all' host 192.168.178.221 originally 192.168.178.221
debug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: matched 'final'
debug2: match found
debug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1
debug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config
debug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]
debug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]
debug3: expanded UserKnownHostsFile '~/.ssh/known_hosts' -> '/home/julian/.ssh/known_hosts'
debug3: expanded UserKnownHostsFile '~/.ssh/known_hosts2' -> '/home/julian/.ssh/known_hosts2'
debug1: auto-mux: Trying existing master
debug2: fd 4 setting O_NONBLOCK
debug2: mux_client_hello_exchange: master version 4
debug3: mux_client_forwards: request forwardings: 0 local, 0 remote
debug3: mux_client_request_session: entering
debug3: mux_client_request_alive: entering
debug3: mux_client_request_alive: done pid = 308806
debug3: mux_client_request_session: session request sent
debug1: mux_client_request_session: master session id: 2
Traceback (most recent call last):
  File "<stdin>", line 102, in <module>
  File "<stdin>", line 94, in _ansiballz_main
  File "<stdin>", line 40, in invoke_module
  File "/usr/lib/python3.8/runpy.py", line 207, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/lib/python3.8/runpy.py", line 97, in _run_module_code
    _run_code(code, mod_globals, init_globals,
  File "/usr/lib/python3.8/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 350, in <module>
  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py", line 346, in main
  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2168, in exit_json
  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 2161, in _return_formatted
  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 426, in remove_values
  File "/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py", line 403, in _remove_values_conditions
TypeError: Value of unknown type: <class 'datetime.timedelta'>, 0:00:03
debug3: mux_client_read_packet: read header failed: Broken pipe
debug2: Received exit status from master 1
fatal: [node1]: FAILED! => {
    "changed": false,
    "module_stderr": "OpenSSH_8.4p1, OpenSSL 1.1.1i FIPS  8 Dec 2020\r\ndebug1: Reading configuration data /home/julian/.ssh/config\r\ndebug1: /home/julian/.ssh/config line 1: Applying options for *\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0\r\ndebug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf\r\ndebug2: checking match for 'final all' host 192.168.178.221 originally 192.168.178.221\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: not matched 'final'\r\ndebug2: match not found\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1 (parse only)\r\ndebug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config\r\ndebug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]\r\ndebug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]\r\ndebug1: configuration requests final Match pass\r\ndebug2: resolve_canonicalize: hostname 192.168.178.221 is address\r\ndebug1: re-parsing configuration\r\ndebug1: Reading configuration data /home/julian/.ssh/config\r\ndebug1: /home/julian/.ssh/config line 1: Applying options for *\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug3: /etc/ssh/ssh_config line 55: Including file /etc/ssh/ssh_config.d/50-redhat.conf depth 0\r\ndebug1: Reading configuration data /etc/ssh/ssh_config.d/50-redhat.conf\r\ndebug2: checking match for 'final all' host 192.168.178.221 originally 192.168.178.221\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 3: matched 'final'\r\ndebug2: match found\r\ndebug3: /etc/ssh/ssh_config.d/50-redhat.conf line 5: Including file /etc/crypto-policies/back-ends/openssh.config depth 1\r\ndebug1: Reading configuration data /etc/crypto-policies/back-ends/openssh.config\r\ndebug3: gss kex names ok: [gss-curve25519-sha256-,gss-nistp256-sha256-,gss-group14-sha256-,gss-group16-sha512-]\r\ndebug3: kex names ok: [curve25519-sha256,[email protected],ecdh-sha2-nistp256,ecdh-sha2-nistp384,ecdh-sha2-nistp521,diffie-hellman-group-exchange-sha256,diffie-hellman-group14-sha256,diffie-hellman-group16-sha512,diffie-hellman-group18-sha512]\r\ndebug3: expanded UserKnownHostsFile '~/.ssh/known_hosts' -> '/home/julian/.ssh/known_hosts'\r\ndebug3: expanded UserKnownHostsFile '~/.ssh/known_hosts2' -> '/home/julian/.ssh/known_hosts2'\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 4 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 308806\r\ndebug3: mux_client_request_session: session request sent\r\ndebug1: mux_client_request_session: master session id: 2\r\nTraceback (most recent call last):\n  File \"<stdin>\", line 102, in <module>\n  File \"<stdin>\", line 94, in _ansiballz_main\n  File \"<stdin>\", line 40, in invoke_module\n  File \"/usr/lib/python3.8/runpy.py\", line 207, in run_module\n    return _run_module_code(code, init_globals, run_name, mod_spec)\n  File \"/usr/lib/python3.8/runpy.py\", line 97, in _run_module_code\n    _run_code(code, mod_globals, init_globals,\n  File \"/usr/lib/python3.8/runpy.py\", line 87, in _run_code\n    exec(code, run_globals)\n  File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 350, in <module>\n  File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/modules/database/postgresql/postgresql_query.py\", line 346, in main\n  File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2168, in exit_json\n  File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 2161, in _return_formatted\n  File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 426, in remove_values\n  File \"/tmp/ansible_postgresql_query_payload_fh11zb4h/ansible_postgresql_query_payload.zip/ansible/module_utils/basic.py\", line 403, in _remove_values_conditions\nTypeError: Value of unknown type: <class 'datetime.timedelta'>, 0:00:03\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 1\r\n",
    "module_stdout": "",
    "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error",
    "rc": 1
}

Postgresql_query and Postgresql_* modules do not provide option for target_session_attrs

SUMMARY

Postgresql_* modules use Python psycopg2 library and this library allow use when connection to cluster attribute target_session_attrs. When we set target_session_attrs="read-write" we will connect to master/write host. It's useful when we use PostgreSQL clusters.

ISSUE TYPE

• Provide attribute target_session_attrs and passthrough to psycopg2

COMPONENT NAME

postgresql_query, postgresql_user, postgresql_db

ADDITIONAL INFORMATION

This postgres modules are based on psycopg2 python module and this module support target_session_attrs and cluster connection. Current ansible modules require extension for supporting this attribute on connection part.

conn = psycopg2.connect(host="ip1,ip2",
            port=port,
            database=db,
            user=user,
            password=pass,
            sslmode='require',
            target_session_attrs="read-write")

postgresql_privs: Add examples of granting privs to functions

SUMMARY

Add examples of granting privs to functions to postgresql_privs module. See #132 for details.

For newcomers, there's a quick start guide how to set up a dev environment and submit a PR . You can also add examples and submit a PR directly via GH GUI.

ISSUE TYPE

• Documentation Report

COMPONENT NAME

plugin/modules/postgresql_privs.py

postgresql_query - loading script fails if script contain plpgsql definitions

SUMMARY

Note: previously opened here: ansible/ansible#73041

We are using an ansible recipe to setup a postgresql in a Centos 7 environment. I recently updated my virtualenv (and in turn my ansible version). A postgresql_query step with a script containing a plpgsql function was working using the previous version of Ansible (2.9) but not anymore with Ansible 2.10.

FYI the following sql script is failing:
https://raw.githubusercontent.com/georchestra/georchestra/master/postgresql/050-ogc-server-statistics.sql

Removing the virtualenv and regenerating it with ansible==2.9 allows to run the playbook normally with no issue

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_query module

ANSIBLE VERSION

ansible 2.10.4
  config file = /home/pmauduit/projects/aaaa/bbbb/ansible.cfg
  configured module search path = ['/home/pmauduit/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/pmauduit/projects/aaaa/bbbb/venv/lib/python3.9/site-packages/ansible
  executable location = /home/pmauduit/projects/aaaa/bbbb/venv/bin/ansible
  python version = 3.9.0+ (default, Oct 19 2020, 09:51:18) [GCC 10.2.0]

CONFIGURATION

ALLOW_WORLD_READABLE_TMPFILES(/home/pmauduit/projects/aaaa/bbbb/ansible.cfg) = True
DEFAULT_HOST_LIST(/home/pmauduit/projects/aaaa/bbbb/ansible.cfg) = ['/home/pmauduit/projects/aaaa/bbbb/inventory.yaml']
DEFAULT_LOG_PATH(/home/pmauduit/projects/aaaa/bbbb/ansible.cfg) = /home/pmauduit/projects/aaaa/bbbb/ansible.log
DEFAULT_ROLES_PATH(/home/pmauduit/projects/aaaa/bbbb/ansible.cfg) = ['/home/pmauduit/projects/aaaa/bbbb/roles']
INTERPRETER_PYTHON(/home/pmauduit/projects/aaaa/bbbb/ansible.cfg) = /usr/bin/python

OS / ENVIRONMENT

The host environment is running debian sid, the guest is a vagrant/virtualbox running centos 7.

I retested the playbook under ansible 2.9 and it works smoothly. It does not work anymore on ansible 2.10.

STEPS TO REPRODUCE

    - name: Download official geOrchestra SQL scripts # noqa 503
      get_url:
        url: " https://raw.githubusercontent.com/georchestra/georchestra/20.0.6/postgresql/050-ogc-server-statistics.sql"
        dest: /tmp/ogc-server-statistics.sql
      when: georchestra_db.changed

    - name: Sources the georchestra database layout # noqa 503
      postgresql_query:
        path_to_script: /tmp/ogc-server-statistics.sql
        db: "{{ georchestra_database_name }}"
        login_unix_socket: '/var/run/postgresql'
      become_user: postgres
      become: true

Note: in the real playbook, we are iterating over several different scripts, I just kept one here for readability and removed the "items: " attribute.

EXPECTED RESULTS

The script can be loaded regardless of its content (plpgsql scripts or not)

ACTUAL RESULTS

failed: [database] (item=ogc-server-statistics.sql) => {"ansible_loop_var": "item", "changed": false, "item": "ogc-server-statistics.sql", "msg": "Cannot execute SQL '\n\n-- Return name of table that correspond to specified date, also create table if it does\n-- not exists and indexes on table of previous month\nCREATE OR REPLACE FUNCTION get_partition_table(my_date timestamp without time zone)\n  RETURNS character varying AS\n$BODY$\nDECLARE\n    my_table_name character varying' None: unterminated dollar-quoted string at or near \"$BODY$\nDECLARE\n    my_table_name character varying\"\nLINE 7: $BODY$\n        ^\n, query list: ['--\\n-- PostgreSQL database\\n--\\n\\nBEGIN', '\\n\\nCREATE SCHEMA ogcstatistics', '\\nSET search_path TO ogcstatistics,public,pg_catalog', '\\n\\n-- Create new version of ogc_services_log table\\nCREATE TABLE ogc_services_log(\\n  user_name character varying(255),\\n  date timestamp without time zone,\\n  service character varying(5),\\n  layer character varying(255),\\n  id bigserial,\\n  request character varying(20),\\n  org character varying(255),\\n  roles text[]\\n)', '\\n\\n-- Return name of table that correspond to specified date, also create table if it does\\n-- not exists and indexes on table of previous month\\nCREATE OR REPLACE FUNCTION get_partition_table(my_date timestamp without time zone)\\n  RETURNS character varying AS\\n$BODY$\\nDECLARE\\n    my_table_name character varying', '\\n    my_month character varying', '\\n    my_year character varying', '\\n    previous_month character varying', '\\n    previous_year character varying', '\\n    previous_table_name character varying', '\\n    previous_table_oid oid', '\\n    borne_sup date', '\\n    borne_inf date', '\\n    query character varying', \"\\n    base_table_name character varying = 'ogc_services_log'\", \"\\n    base_schema_name character varying = 'ogcstatistics'\", '\\nBEGIN\\n\\n  -- Generate table name\\n  my_month := EXTRACT(MONTH FROM my_date)', '\\n  my_year := EXTRACT(YEAR FROM my_date)', \"\\n\\n  my_table_name := base_table_name || '_y' || my_year || 'm' || my_month\", \"\\n\\n  -- RAISE NOTICE 'table name %.%', base_schema_name, my_table_name\", \"\\n\\n  -- Test if table already exists\\n  IF NOT (SELECT count(*) > 0\\n          FROM information_schema.tables\\n          WHERE table_schema = base_schema_name\\n    AND table_name = my_table_name) THEN\\n\\n    borne_inf := (my_year || '-' || my_month || '-01')::date\", \"\\n    borne_sup := borne_inf + INTERVAL '1 month'\", \"\\n\\n    query := 'CREATE TABLE ' || base_schema_name || '.' || my_table_name || '( CHECK ( date >= DATE ''' || borne_inf || ''' AND date < DATE ''' || borne_sup || ''' ) '\", \"\\n    query := query || ') INHERITS (' || base_schema_name || '.' || base_table_name || ')'\", '\\n\\n    -- Create table if it does not exists\\n    EXECUTE query', \"\\n\\n    -- Create Indexes on previous table for user_name and date fields\\n    previous_month := EXTRACT(MONTH FROM (my_date - INTERVAL '1 month'))\", \"\\n    previous_year := EXTRACT(YEAR FROM (my_date - INTERVAL '1 month'))\", \"\\n    previous_table_name := base_table_name || '_y' || previous_year || 'm' || previous_month\", \"\\n\\n    -- Check if previous table exists\\n    IF (SELECT count(*) > 0\\n        FROM information_schema.tables\\n        WHERE table_schema = base_schema_name\\n        AND table_name = previous_table_name) THEN\\n\\n      previous_table_oid := (base_schema_name || '.' || previous_table_name)::regclass::int\", \"\\n      -- Check if indexes already exists\\n      IF NOT (WITH stat_indexes AS (SELECT t.oid,\\n                                           t.relname AS table_name,\\n                                           i.relname AS index_name,\\n                                           array_agg(a.attname) AS column_names\\n                                    FROM\\n                                       pg_class t,\\n                                       pg_class i,\\n                                       pg_index ix,\\n                                       pg_attribute a\\n                                    WHERE\\n                                       t.oid = ix.indrelid\\n                                       AND i.oid = ix.indexrelid\\n                                       AND a.attrelid = t.oid\\n                                       AND a.attnum = ANY(ix.indkey)\\n                                       AND t.relkind = 'r'\\n                                       AND t.oid = previous_table_oid\\n                                    GROUP BY t.oid, t.relname, index_name)\\n              SELECT count(*) = 2\\n              FROM stat_indexes\\n              WHERE column_names IN (ARRAY['date']::name[], ARRAY['user_name']::name[])) THEN\\n\\n        query := 'CREATE INDEX ' || previous_table_name || '_date_idx ON ' || base_schema_name || '.' || previous_table_name || '(date)'\", '\\n        EXECUTE query', \"\\n        query := 'CREATE INDEX ' || previous_table_name || '_user_name_idx ON ' || base_schema_name || '.' || previous_table_name || '(user_name)'\", '\\n        EXECUTE query', '\\n\\n      END IF', '\\n\\n    END IF', '\\n\\n  END IF', \"\\n\\n  RETURN base_schema_name || '.' || my_table_name\", '\\n\\nEND', '\\n$BODY$\\n  LANGUAGE plpgsql VOLATILE', \"\\n\\nCOMMENT ON FUNCTION get_partition_table(timestamp without time zone) IS 'Return name of table that correspond to specified date, also create table if it does not exists and indexes on table of previous month'\", '\\n\\n\\n\\nCREATE OR REPLACE FUNCTION insert_stat_trigger_function()\\nRETURNS TRIGGER AS $$\\nDECLARE\\n  table_name character varying', '\\nBEGIN\\n\\n  table_name := ogcstatistics.get_partition_table(NEW.date)', \"\\n\\n  -- insert record in child table\\n  EXECUTE 'INSERT INTO ' || table_name || ' VALUES ($1.*)' USING NEW\", '\\n  -- do *not* insert record in master table\\n  RETURN NULL', '\\n\\nEND', '\\n$$\\nLANGUAGE plpgsql', '\\n\\n\\n\\nCREATE TRIGGER insert_stat_trigger\\n    BEFORE INSERT ON ogc_services_log\\n    FOR EACH ROW EXECUTE PROCEDURE insert_stat_trigger_function()', '\\n\\nCOMMIT']"}

Dissecting the previous error above, it seems that the sql scripts are weirdly split into several queries, in a way it makes it invalid for PostGreSQL to load it correctly.

Superuser flag unnecessarily needed ?

SUMMARY

superuser is required to create a non-superuser one, tested against aws rds which does have a limited postgres user.

Connecting with psql and doing user create and also alter user works fine, but the second run of postgfresql_user fails with psycopg2.errors.InsufficientPrivilege: must be superuser to alter superusers

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_user

ANSIBLE VERSION

also tested with ansible 2.8.11
psycopg2 is 2.8.6

/ansible $ ansible --version
ansible 2.9.20
  config file = /ansible/ansible_with_vault.cfg
  configured module search path = ['/home/andrea/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.7/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.7.10 (default, Mar  2 2021, 09:06:08) [GCC 8.3.0]

CONFIGURATION

ANSIBLE_PIPELINING(/ansible-testme/ansible_with_vault.cfg) = True
ANSIBLE_SSH_ARGS(/ansible-testme/ansible_with_vault.cfg) = -o UserKnownHostsFile=./ssh_known_hosts -o ForwardAgent=yes -o ConnectTimeout=30 -o ControlMaster=auto -o ControlPersist=180s -o ControlPath=.ansible_ssh/master-%r@%h:%p.socket
CACHE_PLUGIN(/ansible-testme/ansible_with_vault.cfg) = jsonfile
CACHE_PLUGIN_CONNECTION(/ansible-testme/ansible_with_vault.cfg) = ./ansible-fact_caching
CACHE_PLUGIN_TIMEOUT(/ansible-testme/ansible_with_vault.cfg) = 86400
DEFAULT_CALLBACK_WHITELIST(/ansible-testme/ansible_with_vault.cfg) = ['profile_tasks']
DEFAULT_FORKS(/ansible-testme/ansible_with_vault.cfg) = 20
DEFAULT_GATHERING(/ansible-testme/ansible_with_vault.cfg) = smart
DEFAULT_HOST_LIST(/ansible-testme/ansible_with_vault.cfg) = ['/ansible-testme/NOTHING']
DEFAULT_LOG_PATH(/ansible-testme/ansible_with_vault.cfg) = /ansible-testme/ansible.log
DEFAULT_LOOKUP_PLUGIN_PATH(/ansible-testme/ansible_with_vault.cfg) = ['/ansible-testme/plugins/lookup']
DEFAULT_ROLES_PATH(/ansible-testme/ansible_with_vault.cfg) = ['/ansible-testme/roles']
DEFAULT_STDOUT_CALLBACK(/ansible-testme/ansible_with_vault.cfg) = debug
DEFAULT_STRATEGY(/ansible-testme/ansible_with_vault.cfg) = mitogen_linear
DEFAULT_STRATEGY_PLUGIN_PATH(/ansible-testme/ansible_with_vault.cfg) = ['/usr/lib/python3.7/site-packages/ansible_mitogen/plugins/strategy']
DEFAULT_TIMEOUT(/ansible-testme/ansible_with_vault.cfg) = 30
DEFAULT_VAULT_IDENTITY_LIST(/ansible-testme/ansible_with_vault.cfg) = ['pre_ca@../ansible-testme-vault/pre_ca.txt', 'production@../ansible-testme-vault/production.txt', 'pre@../ansible-testme-vault/pre.txt', 'old@../ansible-testme-vault/old.txt']
DIFF_ALWAYS(/ansible-testme/ansible_with_vault.cfg) = True
DISPLAY_SKIPPED_HOSTS(env: ANSIBLE_DISPLAY_SKIPPED_HOSTS) = False
HOST_KEY_CHECKING(/ansible-testme/ansible_with_vault.cfg) = True
MAX_FILE_SIZE_FOR_DIFF(/ansible-testme/ansible_with_vault.cfg) = 104857600
PERSISTENT_COMMAND_TIMEOUT(/ansible-testme/ansible_with_vault.cfg) = 180
PERSISTENT_CONNECT_TIMEOUT(/ansible-testme/ansible_with_vault.cfg) = 180

OS / ENVIRONMENT

controller : ansible on docker (alpine) container.
Target : AWS RDS postgres

STEPS TO REPRODUCE

On a vanilla postgresql istance this code is idempotent.
If run against a AWS RDS postgres instance (where the postgres user miss the supersuer flag, see this serverfault answer the first run is succesful, the second one throws this error :

psycopg2.errors.InsufficientPrivilege: must be superuser to alter superusers

    - name: create the user
      postgresql_user:
        login_host: "{{ postgres_login_host }}"
        login_user: "{{ postgres_login_user }}"
        login_password: "{{ postgres_login_password }}"
        name: "{{ pg_username }}"
        password: "{{ pg_password }}"
        role_attr_flags: NOSUPERUSER,NOCREATEDB,NOCREATEROLE,NOINHERIT
        state: present

EXPECTED RESULTS

User is created succesfully, and task is not changed on subsequent runs

ACTUAL RESULTS

An exception occurred during task execution. To see the full traceback, use -vvv. The error was: psycopg2.errors.InsufficientPrivilege: must be superuser to alter superusers
fatal: [my.host.net -> localhost]: FAILED! => {
    "changed": false,
    "rc": 1
}

MSG:

MODULE FAILURE
See stdout/stderr for the exact error


MODULE_STDERR:

Traceback (most recent call last):
  File "master:/usr/lib/python3.7/site-packages/ansible_mitogen/runner.py", line 975, in _run
    self._run_code(code, mod)
  File "master:/usr/lib/python3.7/site-packages/ansible_mitogen/runner.py", line 939, in _run_code
    exec(code, vars(mod))
  File "master:/usr/lib/python3.7/site-packages/ansible/modules/database/postgresql/postgresql_user.py", line 871, in <module>
  File "master:/usr/lib/python3.7/site-packages/ansible/modules/database/postgresql/postgresql_user.py", line 825, in main
  File "master:/usr/lib/python3.7/site-packages/ansible/modules/database/postgresql/postgresql_user.py", line 433, in user_alter
  File "/usr/lib/python3.7/site-packages/psycopg2/extras.py", line 146, in execute
    return super(DictCursor, self).execute(query, vars)
psycopg2.errors.InsufficientPrivilege: must be superuser to alter superusers

TODO (migration related)

SUMMARY

Relates to migration from community.general to this repository (the order is not strict)

• Move issues / prs
• Move saslprep unit tests
• Copy changelog related things
• Copy fragments
• Release 0.1.0
• Add redirect
• BOTMETA.yml: migrated_to Nothing uses migrated_to so we can skip this - gundalow
• Remove all extra stuff from tests
• Look through the checklist
• Look through the migration doc

postgresql_ping fails when connecting to Azure postgresql (version has text after minor version)

SUMMARY

postgresql_ping fails when connecting to Microsoft Azure postgresql with error ValueError: invalid literal for int() with base 10: '6,'
The query SELECT version() returns PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.general.postgresql_ping

ANSIBLE VERSION

ansible 2.10.4
  config file = /z/2now/kit-ar/19019-portal/19019-portal-ops/root/ansible.cfg
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /venv/ans/lib/python3.7/site-packages/ansible
  executable location = /venv/ans/bin/ansible
  python version = 3.7.9 (default, Dec 18 2020, 05:48:13) [GCC 9.3.0]

CONFIGURATION

OS / ENVIRONMENT

STEPS TO REPRODUCE

EXPECTED RESULTS

Correct version being returned

ACTUAL RESULTS

Querying an Azure Postgresql server seems to break the version detection code, as it returns text after the version.

SELECT version()
PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit

Ansible crash

Using module file /venv/ans/lib/python3.7/site-packages/ansible_collections/community/general/plugins/modules/postgresql_ping.py
Pipelining is enabled.
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: root
<127.0.0.1> EXEC /bin/sh -c '/venv/ans/bin/python3 && sleep 0'
The full traceback is:
Traceback (most recent call last):
  File "<stdin>", line 102, in <module>
  File "<stdin>", line 94, in _ansiballz_main
  File "<stdin>", line 40, in invoke_module
  File "/usr/local/lib/python3.7/runpy.py", line 205, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/local/lib/python3.7/runpy.py", line 96, in _run_module_code
    mod_name, mod_spec, pkg_name, script_name)
  File "/usr/local/lib/python3.7/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/tmp/ansible_community.general.postgresql_ping_payload_acr0tfma/ansible_community.general.postgresql_ping_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ping.py", line 170, in <module>
  File "/tmp/ansible_community.general.postgresql_ping_payload_acr0tfma/ansible_community.general.postgresql_ping_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ping.py", line 163, in main
  File "/tmp/ansible_community.general.postgresql_ping_payload_acr0tfma/ansible_community.general.postgresql_ping_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ping.py", line 108, in do
  File "/tmp/ansible_community.general.postgresql_ping_payload_acr0tfma/ansible_community.general.postgresql_ping_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ping.py", line 119, in get_pg_version
ValueError: invalid literal for int() with base 10: '6,'
fatal: [kar_platf__1dev]: FAILED! => changed=false 
  failed_when_result: true
  module_stderr: |-
    Traceback (most recent call last):
      File "<stdin>", line 102, in <module>
      File "<stdin>", line 94, in _ansiballz_main
      File "<stdin>", line 40, in invoke_module
      File "/usr/local/lib/python3.7/runpy.py", line 205, in run_module
        return _run_module_code(code, init_globals, run_name, mod_spec)
      File "/usr/local/lib/python3.7/runpy.py", line 96, in _run_module_code
        mod_name, mod_spec, pkg_name, script_name)
      File "/usr/local/lib/python3.7/runpy.py", line 85, in _run_code
        exec(code, run_globals)
      File "/tmp/ansible_community.general.postgresql_ping_payload_acr0tfma/ansible_community.general.postgresql_ping_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ping.py", line 170, in <module>
      File "/tmp/ansible_community.general.postgresql_ping_payload_acr0tfma/ansible_community.general.postgresql_ping_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ping.py", line 163, in main
      File "/tmp/ansible_community.general.postgresql_ping_payload_acr0tfma/ansible_community.general.postgresql_ping_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ping.py", line 108, in do
      File "/tmp/ansible_community.general.postgresql_ping_payload_acr0tfma/ansible_community.general.postgresql_ping_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_ping.py", line 119, in get_pg_version
    ValueError: invalid literal for int() with base 10: '6,'
  module_stdout: ''
  msg: |-
    MODULE FAILURE
    See stdout/stderr for the exact error
  rc: 1

The 'postgresql_privs' module cannot grant access to database by another role

SUMMARY

The postgresql_privs role is broken when trying to grant access to a PostgreSQL database owned by one role, to a different PostgreSQL role.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_privs

ANSIBLE VERSION

ansible 2.10.4
  config file = None
  configured module search path = ['/home/drybjed/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/drybjed/.local/lib/python3.7/site-packages/ansible
  executable location = /home/drybjed/.local/bin/ansible
  python version = 3.7.3 (default, Dec 20 2019, 18:57:59) [GCC 8.3.0]

CONFIGURATION

Empty output

OS / ENVIRONMENT

OS: Debian Buster
Database: PostgreSQL 11

STEPS TO REPRODUCE

The idea is to create a PostgreSQL "database role" which is an owner of the database itself and cannot be logged in directly. Then, an "application role" is granted access to the "database role", and this allows the application role to access the database without the ability to remove the database itself, or perform other admin-level tasks on it, for security reasons.

---

- hosts: 'localhost'
  become: True

  tasks:

    - name: Create database role
      postgresql_user:
        name: "gitlabhq_production"
        role_attr_flags: "NOLOGIN"
        state: present
      become: True
      become_user: 'postgres'

    - name: Create database
      postgresql_db:
        name: "gitlabhq_production"
        owner: 'gitlabhq_production'
        state: present
      become: True
      become_user: 'postgres'

    - name: Create application role
      postgresql_user:
        name: "gitlab"
        state: present
      become: True
      become_user: 'postgres'

    - name: "Grant access to database role by application role"
      postgresql_privs:
        roles: "gitlab"
        type: "group"
        objs: "gitlabhq_production"
        database: "gitlabhq_production"
      become: True
      become_user: 'postgres'

EXPECTED RESULTS

The postgres_privs module should grant access to the gitlabhq_production PostgreSQL role by the gitlab PostgreSQL role.

ACTUAL RESULTS

PLAY [localhost] ********************************************************************************************************************************************

TASK [Gathering Facts] *********************************************************************************************************************************
ok: [localhost]

TASK [Create database role] ****************************************************************************************************************************
changed: [localhost]

TASK [Create database] *********************************************************************************************************************************
changed: [localhost]

TASK [Create application role] *************************************************************************************************************************
changed: [localhost]

TASK [Grant access from app role to database role] *****************************************************************************************************
fatal: [localhost]: FAILED! => changed=false 
  msg: |-
    zero-length delimited identifier at or near """"
    LINE 1: GRANT ""gitlabhq_production"" TO "gitlab";
                   ^

Output of the failed task with -vvvv debugging enabled:

TASK [Grant access from app role to database role] *****************************************************************************************************
task path: /home/drybjed/src/projects/lxc.tycho.drybjed.net/playbooks/test-postgres.yml:31                                                             
The full traceback is:
  File "/tmp/ansible_postgresql_privs_payload_s8jzrx80/ansible_postgresql_privs_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_privs.py", line 1120, in main
  File "/tmp/ansible_postgresql_privs_payload_s8jzrx80/ansible_postgresql_privs_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_privs.py", line 802, in manipulate_privs
fatal: [code]: FAILED! => changed=false 
  invocation:
    module_args:
      ca_cert: null
      database: gitlabhq_production
      fail_on_role: true
      grant_option: null
      host: ''
      login: postgres
      login_host: ''
      login_password: ''
      login_unix_socket: ''
      login_user: postgres
      objs: gitlabhq_production
      password: ''
      port: 5432
      privs: null
      roles: gitlab
      schema: null
      session_role: null
      ssl_mode: prefer
      state: present
      target_roles: null
      trust_input: true
      type: group
      unix_socket: ''
      usage_on_types: true
  msg: |-
    zero-length delimited identifier at or near """"
    LINE 1: GRANT ""gitlabhq_production"" TO "gitlab";
                   ^

explicitly set "no_log: false" not taken into consideration by postgresql_user

SUMMARY

when explicitly set no_log to false, the postgresql_user module seems not take it into consideration.

This is important in the situation where the default value is set true to mask sensitive information but set to false during password troubleshooting without turning on too much debug information.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_user

ANSIBLE VERSION

bash-4.4# ansible --version
ansible 2.10.5
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/var/lib/awx/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.6/site-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 3.6.8 (default, Aug 24 2020, 17:57:11) [GCC 8.3.1 20191121 (Red Hat 8.3.1-5)]

COLLECTION VERSION

root@server$ ansible-galaxy collection list community.general

# /root/ansible_collections
Collection        Version
----------------- -------
community.general 2.4.0
community.postgresql 1.4.0

CONFIGURATION

root@server$ ansible-config dump --only-changed
COLLECTIONS_PATHS(/root/ansible.cfg) = ['/root/ansible_collections']
DEFAULT_HOST_LIST(/root/ansible.cfg) = ['/root/inventory.ini']
DEFAULT_ROLES_PATH(/root/ansible.cfg) = ['/etc/ansible/roles', '/root/roles']

OS / ENVIRONMENT

CentOS 7
Ansible AWX 16.0.0

STEPS TO REPRODUCE

set no_log: false in a task and run below playbook or use extra-variables in AWX

- name: update PostgreSQL users
  postgresql_user:
    name: "{{ item.name }}"
    password: "{{ item.password | default(omit) }}"
    encrypted: "{{ item.encrypted | default(omit) }}"
    priv: "{{ item.priv | default(omit) }}"
    role_attr_flags: "{{ item.role_attr_flags | default(omit) }}"
    db: "{{ item.db | default(omit) }}"
    login_host: "{{ item.login_host | default('localhost') }}"
    login_user: "{{ item.login_user | default(postgresql_user) }}"
    login_password: "{{ item.login_password | default(postgresql_pass) }}"
    login_unix_socket: "{{ item.login_unix_socket | default(postgresql_unix_socket_directories) }}"
    port: "{{ item.port | default(omit) }}"
    state: "{{ item.state | default('present') }}"
  with_items: "{{ postgresql_users }}"
  no_log: false  <----------------------------------------------------------------------- explicitly set to default value
  become: true
  become_user: postgres

EXPECTED RESULTS

"XXXXXXXX" below to be shown as plain-text string.

{
  "invocation": {
    "module_args": {
      "ssl_mode": "prefer",
      "ca_cert": null,
      "login_user": "XXXXXXXX",
      "login_host": "",
      "session_role": null,
      "db": "XXXXXXXX",
      "login_unix_socket": "",
      "trust_input": true,
      "login_password": "XXXXXXXX",
      "port": 5432
    }
  },
  "changed": false,
  "is_available": true,
  "server_version": {
    "major": 9,
    "minor": 6
  },
  "_ansible_no_log": false,
  "attempts": 1
}

ACTUAL RESULTS

{
  "invocation": {
    "module_args": {
      "ssl_mode": "prefer",
      "ca_cert": null,
      "login_user": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
      "login_host": "",
      "session_role": null,
      "db": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
      "login_unix_socket": "",
      "trust_input": true,
      "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
      "port": 5432
    }
  },
  "changed": false,
  "is_available": true,
  "server_version": {
    "major": 9,
    "minor": 6
  },
  "_ansible_no_log": false,  <-------------------------------------------------------------even it is indeed explicitly set to false
  "attempts": 1
}

Also, it seems some outputs shown in the issue https://github.com/ansible-collections/community.postgresql/issues/117 also show this issue even that issue was not created for this specific problem though.

postgresql_query: set default behavior of as_single_query at True right before 2.0.0 release

SUMMARY

postgresql_query: change the default behavior of as_single_query to yes before 2.0.0 release (~in half a year). Add warning now. Announce the breaking change.

COMPONENT NAME

postgresql_query

postgresql_ext can't handle '0' version

SUMMARY

The pguint extension declares its version as 0. This seems to confuse the postgresql_ext module as it thinks the extension doesn't exist.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_ext

ANSIBLE VERSION

ansible 2.10.11
  config file = None
  configured module search path = ['/home/phemmer/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/phemmer/.local/lib/python3.9/site-packages/ansible
  executable location = /home/phemmer/.local/bin/ansible
  python version = 3.9.5 (default, May 14 2021, 00:00:00) [GCC 11.1.1 20210428 (Red Hat 11.1.1-1)]

COLLECTION VERSION

# /home/phemmer/.local/lib/python3.9/site-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.1.1  

CONFIGURATION

OS / ENVIRONMENT

Debian 10/Buster
PostgreSQL 13

STEPS TO REPRODUCE

# ansible myhost -m postgresql_ext -a 'name=uint db=mydb'

EXPECTED RESULTS

Install extension to database

ACTUAL RESULTS

# ansible myhost -m postgresql_ext -a 'name=uint db=mydb'
...
ded5523.ded.reflected.net | FAILED! => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/bin/python3"
    },
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "cascade": false,
            "db": "mydb",
            "ext": "uint",
            "login_host": "",
            "login_password": "",
            "login_unix_socket": "",
            "login_user": "postgres",
            "name": "uint",
            "port": 5432,
            "schema": null,
            "session_role": null,
            "ssl_mode": "prefer",
            "state": "present",
            "trust_input": true,
            "version": null
        }
    },
    "msg": "Extension uint is not installed"
}
...

# ansible myhost -m postgresql_ext -a 'name=uint db=mydb version=0'
...
ded5523.ded.reflected.net | FAILED! => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/bin/python3"
    },
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "cascade": false,
            "db": "edgestats",
            "ext": "uint",
            "login_host": "",
            "login_password": "",
            "login_unix_socket": "",
            "login_user": "postgres",
            "name": "uint",
            "port": 5432,
            "schema": null,
            "session_role": null,
            "ssl_mode": "prefer",
            "state": "present",
            "trust_input": true,
            "version": "0"
        }
    },
    "msg": "Passed version '0' is not available"
}
...

...

ADDITIONAL INFO

mydb=> select * from pg_available_extensions where name='uint';
 name | default_version | installed_version |        comment         
------+-----------------+-------------------+------------------------
 uint | 0               |                   | unsigned integer types
(1 row)

PostgreSQL community pinboard

Copied from #435 originally created by Dag Wieers

We could collectively benefit from forming a Working Group related to PostgreSQL integration. We have quite some contributors on Github and users on IRC that are interested in improving this integration.

So this issue is a call for potential interested parties (earlier and existing contributors to Ansible). The benefits of having a Working Group is that members of the Working Group can:

• test, review and approve existing PostgreSQL PRs
• work collectively on the PostgreSQL roadmap
• provide a single-point-of-contact for interested PostgreSQL contributors
• collaborate on an PostgreSQL-specific Wiki

All ok local but nothing happens in production

SUMMARY

I run community.postgresql.postgresql_user in production but after 10 min of waiting nothing happens.

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.postgresql.postgresql_user

ANSIBLE VERSION

ansible 2.9.12
  config file = /home/xxxxx/ansible/ansible.cfg
  configured module search path = ['/usr/local/lib/python3.8/dist-packages/git_acp/modules']
  ansible python module location = /usr/local/lib/python3.8/dist-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 3.8.10 (default, Jun  2 2021, 10:49:15) [GCC 9.4.0]

COLLECTION VERSION

usage: ansible-galaxy collection [-h] COLLECTION_ACTION ...
ansible-galaxy collection: error: argument COLLECTION_ACTION: invalid choice: 'list' (choose from 'init', 'build', 'publish', 'install')

CONFIGURATION

ANSIBLE_PIPELINING(/home/xxx/ansible/ansible.cfg) = True
DEFAULT_HOST_LIST(/home/xxx/ansible/ansible.cfg) = ['/home/xxx/ansible/hosts.ini']
DEFAULT_LOG_PATH(/home/xxx/ansible/ansible.cfg) = /home/xxx/ansible/ansible.log
DEFAULT_MODULE_PATH(/home/xxx/ansible/ansible.cfg) = ['/usr/local/lib/python3.8/dist-packages/git_acp/modules']
DEFAULT_VAULT_PASSWORD_FILE(/home/xxx/ansible/ansible.cfg) = /home/xxx/.ansible_pass.txt
HOST_KEY_CHECKING(/home/xxx/ansible/ansible.cfg) = False
INTERPRETER_PYTHON(/home/xxx/ansible/ansible.cfg) = /usr/bin/python3.8

OS / ENVIRONMENT

Local (all ok)

lsb_release -a

Distributor ID: Ubuntu
Description:    Ubuntu 20.04.2 LTS
Release:        20.04
Codename:       focal

Prod (nothing happens)

lsb_release -a

Distributor ID: Ubuntu
Description:    Ubuntu 18.04.5 LTS
Release:        18.04
Codename:       bionic

STEPS TO REPRODUCE

  - name: Create jirauser user, and grant access to database jira
    community.postgresql.postgresql_user:
      state: present
      login_host: localhost
      login_user: postgresql
      login_password: "{{ postgresql_root_password }}"
      name: jirauser
      password: {{ p }}
      expires: infinity

EXPECTED RESULTS

All ok.

TASK [Create jirauser user, and grant access to database jira] **********************************************************************
task path: /home/xxx/ansible/roles/jira/tasks/main.yml:24
Using module file /root/.ansible/collections/ansible_collections/community/postgresql/plugins/modules/postgresql_user.py
Pipelining is enabled.
<127.0.0.1> ESTABLISH LOCAL CONNECTION FOR USER: root
<127.0.0.1> EXEC /bin/sh -c '/usr/bin/python3 && sleep 0'
[WARNING]: The value ******** (type int) in a string field was converted to '********' (type string). If this does not look like
what you expect, quote the entire value to ensure it does not change.
ok: [xxx] => {
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "comment": null,
            "conn_limit": null,
            "db": "",
            "encrypted": true,
            "expires": "infinity",
            "fail_on_user": true,
            "groups": null,
            "login_host": "localhost",
            "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "login_unix_socket": "",
            "login_user": "postgres",
            "name": "jirauser",
            "no_password_changes": false,
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "port": 5432,
            "priv": null,
            "role_attr_flags": "",
            "session_role": null,
            "ssl_mode": "prefer",
            "state": "present",
            "trust_input": true,
            "user": "jirauser"
        }
    },
    "queries": [],
    "user": "jirauser"
}

ACTUAL RESULTS

Nothing happens.

TASK [Create jirauser user, and grant access to database jira] **********************************************************************
task path: /home/xxx/ansible/roles/jira/tasks/main.yml:24
Using module file /root/.ansible/collections/ansible_collections/community/postgresql/plugins/modules/postgresql_user.py
Pipelining is enabled.
<xxx> ESTABLISH SSH CONNECTION FOR USER: None
<xxx> SSH: EXEC sshpass -d12 ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o StrictHostKeyChecking=no -o ConnectTimeout=10 -o ControlPath=/root/.ansible/cp/4b2924865d xxx '/bin/sh -c '"'"'/usr/bin/python3 && sleep 0'"'"''

The 'postgresql_privs' module is not idempotent in certain cases

SUMMARY

Certain grant and revoke actions in the postgresql_privs module are not idempotent since Ansible 2.10.1. I have checked this on multiple versions of Ansible:

ansible: 2.10.0
ansible-base: 2.10.1
status: idempotent

ansible: 2.10.0
ansible-base: 2.10.2
status: idempotent

ansible: 2.10.1
ansible-base: 2.10.2
status: non-idempotent

ansible: 2.10.2
ansible-base: 2.10.3
status: non-idempotent

ansible: 2.10.3
ansible-base: 2.10.4
status: non-idempotent

ansible: 2.10.4
ansible-base: 2.10.4
status: non-idempotent

ansible: 2.10.5
ansible-base: 2.10.5
status: non-idempotent

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_privs

ANSIBLE VERSION

ansible 2.10.5
  config file = None
  configured module search path = ['/home/drybjed/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/drybjed/.local/lib/python3.7/site-packages/ansible
  executable location = /home/drybjed/.local/bin/ansible
  python version = 3.7.3 (default, Dec 20 2019, 18:57:59) [GCC 8.3.0]

CONFIGURATION

Nothing changed

OS / ENVIRONMENT

OS: Debian 10 (Buster)
Database: PostgreSQL 11

STEPS TO REPRODUCE

---

- hosts: 'localhost'
  become: True

  tasks:

    - name: Grant connect on postgres to PUBLIC
      postgresql_privs:
        database: 'postgres'
        port: '5432'
        role: 'PUBLIC'
        type: 'database'
        privs: 'CONNECT'
        state: 'present'
      become: True
      become_user: 'postgres'

    - name: Revoke temporary on postgres from PUBLIC
      postgresql_privs:
        database: 'postgres'
        port: '5432'
        role: 'PUBLIC'
        type: 'database'
        privs: 'TEMPORARY'
        state: 'absent'
      become: True
      become_user: 'postgres'

EXPECTED RESULTS

The playbook grants and revokes the permissions on the first run, on second run nothing is changed.

ACTUAL RESULTS

Playbook reports the changed status on each execution.

TASK [Grant connect on postgres to PUBLIC] ****************************************************************************************************************
changed: [localhost] => changed=true                                                                                                                           
  invocation:                                                                                                                                              
    module_args:                                                                                                                                           
      ca_cert: null                                                                                                                                        
      database: postgres                                                                                                                                   
      fail_on_role: true                                                                                                                                   
      grant_option: null                                                                                                                                   
      host: ''                                                                                                                                             
      login: postgres                                                                                                                                      
      login_host: ''                                                                                                                                       
      login_password: ''                                                                                                                                   
      login_unix_socket: ''                                                                                                                                
      login_user: postgres                                                                                                                                 
      objs: null                                                                                                                                           
      password: ''                                                                                                                                         
      port: 5432                                                                                                                                           
      privs: CONNECT                                                                                                                                       
      role: PUBLIC                                                                                                                                         
      roles: PUBLIC                                                                                                                                        
      schema: null                                                                                                                                         
      session_role: null                                                                                                                                   
      ssl_mode: prefer                                                                                                                                     
      state: present                                                                                                                                       
      target_roles: null                                                                                                                                   
      trust_input: true                                                                                                                                    
      type: database                                                                                                                                       
      unix_socket: ''                                                                                                                                      
      usage_on_types: true                                                                                                                                 
  queries:                                                                                                                                                 
  - GRANT CONNECT ON database "postgres" TO PUBLIC;                                                                                                        

TASK [Revoke temporary on postgres from PUBLIC] ***********************************************************************************************************
changed: [localhost] => changed=true
  invocation:
    module_args:
      ca_cert: null
      database: postgres
      fail_on_role: true
      grant_option: null
      host: ''
      login: postgres
      login_host: ''
      login_password: ''
      login_unix_socket: ''
      login_user: postgres
      objs: null
      password: ''
      port: 5432
      privs: TEMPORARY
      role: PUBLIC
      roles: PUBLIC
      schema: null
      session_role: null
      ssl_mode: prefer
      state: absent
      target_roles: null
      trust_input: true
      type: database
      unix_socket: ''
      usage_on_types: true
  queries:
  - REVOKE TEMPORARY ON database "postgres" FROM PUBLIC;

add support to restore from directory formats

SUMMARY

db_restore has support for pgc and tar formats, would be nice to have directory (which is compressed by default)

for some reasons (being able to pass arbitrary pg_restore options) i'm not comfortable with using dumps restored with psql, supported via .sql.gz, .sql.xz, etc..

ISSUE TYPE

• Feature Idea

COMPONENT NAME

postgresql_db

ADDITIONAL INFORMATION

postgresql_db:
  name: mydb
  target: /tmp/dump/
  state: restore

would under the hood use --format=directory

Grant on administrative function pg_start_backup

SUMMARY

I'm not able to grant execute rights to pg_start_backup function to any role using postgresql_privs Module. Please find a sample playbook and the exception below.

ISSUE TYPE

• Bug Report

ANSIBLE VERSION

$ ansible --version
ansible 2.9.24
  config file = /home/foo/git/ansible/ansible.cfg
  configured module search path = [u'/home/foo/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python2.7/dist-packages/ansible
  executable location = /usr/bin/ansible
  python version = 2.7.18 (default, Mar  8 2021, 13:02:45) [GCC 9.3.0]

OS / ENVIRONMENT

Ubuntu 20.04 (Ansible Host). Debian 10 (Postgresq Host) with Postgresql 11

STEPS TO REPRODUCE

- name: Just a Test
  hosts: pg1
  gather_facts: false

  tasks:

    - name: Grant execute on pg_start_backup to foo
      postgresql_privs:
        port: 5432
        database: postgres
        schema: pg_catalog
        type: function
        objs: "pg_start_backup(text,boolean,boolean)"
        state: present
        privs: EXECUTE
        roles: foo
      become_user: postgres

postgres=# \df+ pg_catalog.pg_start_backup 
List of functions
-[ RECORD 1 ]-------+-----------------------------------------------------------------------
Schema              | pg_catalog
Name                | pg_start_backup
Result data type    | pg_lsn
Argument data types | label text, fast boolean DEFAULT false, exclusive boolean DEFAULT true
Type                | func
Volatility          | volatile
Parallel            | restricted
Owner               | postgres
Security            | invoker
Access privileges   | postgres=X/postgres
Language            | internal
Source code         | pg_start_backup
Description         | prepare for taking an online backup

EXPECTED RESULTS

Permission to execute pg_start_backup ist granted to foo.

ACTUAL RESULTS

he full traceback is:
Traceback (most recent call last):
  File "/tmp/ansible_postgresql_privs_payload_pijzh5e1/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py", line 692, in manipulate_privs
ValueError: not enough values to unpack (expected 2, got 1)

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/tmp/ansible_postgresql_privs_payload_pijzh5e1/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py", line 1052, in main
  File "/tmp/ansible_postgresql_privs_payload_pijzh5e1/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py", line 694, in manipulate_privs
__main__.Error: Illegal function signature: "boolean".

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/var/tmp/ansible-tmp-1630050360.89-1744351-116965364760712/AnsiballZ_postgresql_privs.py", line 102, in <module>
    _ansiballz_main()
  File "/var/tmp/ansible-tmp-1630050360.89-1744351-116965364760712/AnsiballZ_postgresql_privs.py", line 94, in _ansiballz_main
    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)
  File "/var/tmp/ansible-tmp-1630050360.89-1744351-116965364760712/AnsiballZ_postgresql_privs.py", line 40, in invoke_module
    runpy.run_module(mod_name='ansible.modules.database.postgresql.postgresql_privs', init_globals=None, run_name='__main__', alter_sys=True)
  File "/usr/lib/python3.7/runpy.py", line 205, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/lib/python3.7/runpy.py", line 96, in _run_module_code
    mod_name, mod_spec, pkg_name, script_name)
  File "/usr/lib/python3.7/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/tmp/ansible_postgresql_privs_payload_pijzh5e1/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py", line 1071, in <module>
  File "/tmp/ansible_postgresql_privs_payload_pijzh5e1/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py", line 1057, in main
AttributeError: 'Error' object has no attribute 'message'
fatal: [pg1]: FAILED! => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/bin/python3.7"
    }, 
    "changed": false, 
    "module_stderr": "Shared connection to 192.168.120.136 closed.\r\n", 
    "module_stdout": "Traceback (most recent call last):\r\n  File \"/tmp/ansible_postgresql_privs_payload_pijzh5e1/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py\", line 692, in manipulate_privs\r\nValueError: not enough values to unpack (expected 2, got 1)\r\n\r\nDuring handling of the above exception, another exception occurred:\r\n\r\nTraceback (most recent call last):\r\n  File \"/tmp/ansible_postgresql_privs_payload_pijzh5e1/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py\", line 1052, in main\r\n  File \"/tmp/ansible_postgresql_privs_payload_pijzh5e1/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py\", line 694, in manipulate_privs\r\n__main__.Error: Illegal function signature: \"boolean\".\r\n\r\nDuring handling of the above exception, another exception occurred:\r\n\r\nTraceback (most recent call last):\r\n  File \"/var/tmp/ansible-tmp-1630050360.89-1744351-116965364760712/AnsiballZ_postgresql_privs.py\", line 102, in <module>\r\n    _ansiballz_main()\r\n  File \"/var/tmp/ansible-tmp-1630050360.89-1744351-116965364760712/AnsiballZ_postgresql_privs.py\", line 94, in _ansiballz_main\r\n    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\r\n  File \"/var/tmp/ansible-tmp-1630050360.89-1744351-116965364760712/AnsiballZ_postgresql_privs.py\", line 40, in invoke_module\r\n    runpy.run_module(mod_name='ansible.modules.database.postgresql.postgresql_privs', init_globals=None, run_name='__main__', alter_sys=True)\r\n  File \"/usr/lib/python3.7/runpy.py\", line 205, in run_module\r\n    return _run_module_code(code, init_globals, run_name, mod_spec)\r\n  File \"/usr/lib/python3.7/runpy.py\", line 96, in _run_module_code\r\n    mod_name, mod_spec, pkg_name, script_name)\r\n  File \"/usr/lib/python3.7/runpy.py\", line 85, in _run_code\r\n    exec(code, run_globals)\r\n  File \"/tmp/ansible_postgresql_privs_payload_pijzh5e1/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py\", line 1071, in <module>\r\n  File \"/tmp/ansible_postgresql_privs_payload_pijzh5e1/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py\", line 1057, in main\r\nAttributeError: 'Error' object has no attribute 'message'\r\n", 
    "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", 
    "rc": 1
}

Unable to dynamically use/substitute positional_args to NULLs in postgresql_query

SUMMARY

Unable to dynamically use/substitute positional_args to NULLs in postgresql_query

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.general.postgresql_query

ANSIBLE VERSION

ansible 2.9.14
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/var/lib/awx/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.6/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.6.8 (default, Apr 16 2020, 01:36:27) [GCC 8.3.1 20191121 (Red Hat 8.3.1-5)]

CONFIGURATION

DEFAULT_STDOUT_CALLBACK(/etc/ansible/ansible.cfg) = yaml
HOST_KEY_CHECKING(/etc/ansible/ansible.cfg) = False

OS / ENVIRONMENT

postgresql11-server-11.9-1PGDG.rhel7.x86_64
NAME="Red Hat Enterprise Linux Server"
VERSION="7.7 (Maipo)"

STEPS TO REPRODUCE

Try to dynamically upsert some NULLs into tables

community.general.postgresql_query:
        login_host: "{{ awx_request_audit_db_host }}"
        login_user: "{{ awx_request_audit_db_user }}"
        login_password: "{{ awx_request_audit_db_pass }}"
        db: "{{ awx_request_audit_db_name }}"
        query: >
          INSERT INTO request_id_table(request_id)
           VALUES (%s) ON CONFLICT ON CONSTRAINT request_id_table_pk DO NOTHING;
          INSERT INTO requests(request_id,device_hostname,request_type,error_step,config_snapshot,awx_user_name)
           VALUES (%s,%s,%s,%s,%s,%s) ON CONFLICT ON CONSTRAINT requests_pk DO UPDATE
           SET error_step=NULL, config_snapshot=EXCLUDED.config_snapshot, awx_user_name=EXCLUDED.awx_user_name;
          INSERT INTO add_bd_table(request_id,bd_id,bd_vni_id,bd_routedist,bd_subinterface,bd_vlan_id,bd_if_encap)
           VALUES (%s,%s,%s,%s,%s,%s,%s) ON CONFLICT ON CONSTRAINT add_bd_table_pk DO UPDATE
           SET bd_vni_id=EXCLUDED.bd_vni_id,bd_routedist=EXCLUDED.bd_routedist,bd_subinterface=EXCLUDED.bd_subinterface,bd_vlan_id=EXCLUDED.bd_vlan_id,bd_if_encap=EXCLUDED.bd_if_encap;
        positional_args:
          - '{{ request_id }}'
#          - requests
          - '{{ request_id }}'
          - '{{ inventory_hostname }}'
          - '{{ request_type[inventory_hostname] }}'
#          - '{{ error_step[inventory_hostname] }}'
          - NULL
          - '{{ backup_contents_b64.stdout }}'
          - '{{ awx_user_name }}'
#          add_bd_table
          - "{{ request_id }}"
          - "{{ bd_id }}"
          - "{{ bd_vni_id }}"
          - "{{ bd_routedist }}"
          - "{{ bd_subinterface }}"
          - "{{ bd_vlan_id if (bd_vlan_id is defined) else 'NULL' | default('NULL') }}"
          - "{{ bd_if_encap if (bd_if_encap is defined) else 'NULL' | default('NULL') }}"
      delegate_to: localhost

EXPECTED RESULTS

Being able to provide dynamically assigned NULLs

ACTUAL RESULTS

While static NULL (after # - '{{ error_step[inventory_hostname] }}') does get upserted properly, variables upsert 'NULL' into table or '' (empty string), if used without quotes (just NULL, not 'NULL').
I have searched for the clues in ansible examples and SO with no avail. It is highly likely that I am missing some crucial basic Ansible knowledge, but I just can't find it (or using wrong keywords).

postgresql_user not idempotent when user privilege set to pg_stat_database:SELECT

SUMMARY

postgresql_user module keeps reporting "changed" state for existing postgresql user whose privilege set to pg_stat_database:SELECT

However, if I set privilege value to ALL, then problem goes away.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_user module

ANSIBLE VERSION

bash-4.4# ansible --version
ansible 2.10.5
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/var/lib/awx/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.6/site-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 3.6.8 (default, Aug 24 2020, 17:57:11) [GCC 8.3.1 20191121 (Red Hat 8.3.1-5)]

COLLECTION VERSION

root@server$ ansible-galaxy collection list community.general

# /root/ansible_collections
Collection        Version
----------------- -------
community.general 2.4.0  

CONFIGURATION

root@server$ ansible-config dump --only-changed
COLLECTIONS_PATHS(/root/ansible.cfg) = ['/root/ansible_collections']
DEFAULT_HOST_LIST(/root/ansible.cfg) = ['/root/inventory.ini']
DEFAULT_ROLES_PATH(/root/ansible.cfg) = ['/etc/ansible/roles', '/root/roles']

OS / ENVIRONMENT

CentOS 7
Ansible AWX 16.0.0

STEPS TO REPRODUCE

1. run playbook to create user
2. run the same playbook again

EXPECTED RESULTS

The task shows OK

ACTUAL RESULTS

The task about the user whose priv set to pg_stat_database:SELECT keeps showing "changed" while other users whose priv set to ALL do not have the problem and their states are "OK"

master playbook call the task "postgresql_manage_user" from role "postgresql"
---
- name: update postgresql users on standalone or global master
  hosts: "{{ target | default('harbor_all_pgsql') }}"
  gather_facts: true
  remote_user: root
  tasks:
    - include_role:
        name: postgresql
        tasks_from: postgresql_manage_user


task "postgresql_manage_user" playbook as below:
---
- name: update PostgreSQL users
  postgresql_user:
    name: "{{ item.name }}"
    password: "{{ item.password | default(omit) }}"
    encrypted: "{{ item.encrypted | default(omit) }}"
    priv: "{{ item.priv | default(omit) }}"
    role_attr_flags: "{{ item.role_attr_flags | default(omit) }}"
    db: "{{ item.db | default(omit) }}"
    login_host: "{{ item.login_host | default('localhost') }}"
    login_user: "{{ item.login_user | default(postgresql_user) }}"
    login_password: "{{ item.login_password | default(postgresql_pass) }}"
    login_unix_socket: "{{ item.login_unix_socket | default(postgresql_unix_socket_directories) }}"
    port: "{{ item.port | default(omit) }}"
    state: "{{ item.state | default('present') }}"
  with_items: "{{ postgresql_users }}"
  become: true
  become_user: postgres
  vars:
    ansible_ssh_pipelining: true


"postgresql_users" variable contains 3 users as below, the one whose priv set to is the one with non-idempotent problem.
postgresql_users:
  - name: user1
    password: XXXX
    encrypted: yes
    db: postgres
    priv: ALL
    state: present
  - name: user2
    password: XXXX
    encrypted: yes
    db: postgres
    priv: ALL
    state: present
  - name: user3
    password: XXXX
    encrypted: yes
    db: postgres
    priv: pg_stat_database:SELECT   <---------------------------------------------------------
    state: present 

CI tests against different distro containers are broken

SUMMARY

Presumably because of ansible-collections/overview#45 (comment) :(

Maybe it's time to

1. move to GH Actions
2. default container
3. installing and testing against all supported versions of postgresql instead of distro default ones
4. installing and testing using different psycopg2 versions

In other words, make the CI platform independent but PostgreSQL / driver version dependent.

ISSUE TYPE

• Bug Report

pg_hba.conf: add parameter to keep rule-specific comments next to the rule

SUMMARY

My pg_hba.confs often contain comments in the same line as rules, like this:

host	db1	user1	2001:db8::1/128	md5	# TICKET-1234
host	db2	user2	192.0.2.2/32	md5	# TICKET-5678

Currently, the module community.postgresql.postgresql_pg_hba would move those comments to the beginning of the file, which removes the comment-to-rule relation. To optionally preserve that and to optionally add such comments, I propose to introduce two new parameters to the module:

• keep_comments_at_rules (bool, default=false)
  • If true, comments that stand together with a rule in one line are kept behind that line.
  • If false, such comments are moved to the beginning of the file, like all other comments.
• comment (string, optional)
  • A comment that will be placed in the same line behind the rule.

If not set, the behavior of the module remains like before.

ISSUE TYPE

• Feature Idea

COMPONENT NAME

postgresql_pg_hba

ADDITIONAL INFORMATION

example use in a playbook:

- name: manage pg_hba.conf
  community.postgresql.postgresql_pg_hba:
    address: "{{ address }}"
    contype: host
    create: yes
    databases: "{{ database }}"
    dest: "{{ data_dir }}/pg_hba.conf"
    method: md5
    owner: postgres
    state: present
    users: "{{ username }}"
    keep_comments_at_rules: true
    comment: "this rule is for {{ reason }}"

I already started implementing this and will submit a pull request.

Documentation improvement for fast ramp up

SUMMARY

I need help using your module. Maybe the documentation can be improved in this area.

ISSUE TYPE

• Documentation Report

COMPONENT NAME

all

ANSIBLE VERSION

ansible 2.10.6
  config file = /development/projects/Firmware.Tools.AnsiblePlaybooks/ansible.cfg
  configured module search path = ['/home/sfr/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.9/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.9.2 (default, Feb 20 2021, 18:40:11) [GCC 10.2.0]

I'm trying to use community.postgresql and failing miserably!
What did I do: First install via: ansible-galaxy collection install community.postgresql. Then in the playbook just reference via

collections:
  - community.postgresql

The log output tells me that the collection is loaded, but the desired role is not found.

Loading collection community.postgresql from /home/sfr/.ansible/collections/ansible_collections/community/postgresql
ERROR! the role 'community.postgresql.postgresql_db' was not found in /development/projects/Firmware.Tools.AnsiblePlaybooks/roles:/home/sfr/.ansible/roles:/usr/share/ansible/roles:/etc/ansible/roles:/development/projects/Firmware.Tools.AnsiblePlaybooks

What am I doing wrong?

Module `postgresql_query` cannot cope with identifiers

Copied from ansible-collections/community.general#121
Initially reported by @Andersson007

Copied from ansible/ansible#58644
Initially reported by @cans

SUMMARY

The postgresql_query module cannot be used with any query that requires an identifier as an argument. Indeed as per the documentation, the underlying python DB API implementation (psycopg2) require a string containing a indentifier to be "annotated" as a psycopg2.sql.Identifier instance. If not the string is systematically surrounded with quotes. It would be nice to be able to do such thing.

- hosts: db
  tasks:
    - name: "Configure foreign data wrapper"
      postgresql_query:
          positional_args:
            - "servername"
            - "hostname.domain.tld"
          query: ALTER SERVER %s OPTIONS (SET host %s)
          # and the required connection parameters 

This query is rewritten ALTER SERVER 'server-name' OPTIONS (SET host 'hostname.domain.tld') which is invalid and will fail with the error:

{"msg": "Cannot execute SQL 'ALTER SERVER %s OPTIONS (SET host %s)\n' ['servername', 'hostname.domain.tld']: syntax error at or near \"'servername'\"\nLINE 1: ALTER SERVER 'servername' OPTIONS (SET host 'hostname.doma...\n                     ^\n"}

In the end you are better off using the command module to call psql. It is a bit of a pity.

ISSUE TYPE

• Feature Idea

COMPONENT NAME

postgresql_query

This issue may apply to other database modules (though with different manifestations and solutions).

ADDITIONAL INFORMATION

- hosts: db
  tasks:
    - name: "Configure foreign data wrapper"
      postgresql_query:
          positional_args:
            - "{{ 'servername' | pgidentifier }}"  # Or a variable instead of a literal
            - "hostname.domain.tld"
          query: ALTER SERVER %s OPTIONS (SET host %s)
          # and the required connection parameters 

This query would be rewritten ALTER SERVER "server-name" OPTIONS (SET host 'hostname.domain.tld') as expected.

I quickly tried to put together a filter plugin that does the above. But the value keeps being turned to a string `"'Identifier('servername')'", which is no better (cf. GIST below).

https://gist.github.com/cans/165ecb5042e8c45b6cd5db18227f1c1f

Better handling of PostgreSQL extension versioning

SUMMARY

Better support PostgreSQL extension version handling to support arbitrary naming and downgrades

ISSUE TYPE

Feature Request

COMPONENT NAME

postgresql_ext module

ADDITIONAL INFORMATION

Our organization is still running Ansible 2.9 and we haven't made the full jump to collections and using Galaxy yet. So when testing the installation of PostgreSQL with PostGIS, we ran into this issue with the unpackaged extension version causing issues.

ansible-collections/community.general#1099

We saw that it has been fixed in the community.postgresql collection, but seeing how it was fixed is still a bit concerning for the long term. It is still trying to do a version comparison and, as far as PostgreSQL extensions are concerned, you cannot compare versions like this. This is because the version value and ordering is completely arbitrary, can be any valid alphanumeric value that PG can accept, and there really is no concept of one version being "greater" than another.

How PostgreSQL determines which version is installed and how you can upgrade from one to another is controlled entirely by the update files that an extension author provides. You give one version value followed by another and that provides to PG a valid update path. You can upgrade from one extension version to another, or even downgrade, as long as all the relevant update path files have been provided. For example, for pg_partman, you could literally go from all the way back in 0.1.0 to the current 4.5.1 because there is an unbroken chain (pending any compatiblity issues mentioned in the changelogs of course).

https://github.com/pgpartman/pg_partman/tree/master/updates

[...]
-rw-rw-r-- 1 keith keith  94381 Dec 23  2020 pg_partman--4.3.0--4.3.1.sql
-rw-rw-r-- 1 keith keith 131036 May 11  2020 pg_partman--4.3.1--4.4.0.sql
-rw-rw-r-- 1 keith keith 189468 May  3 11:28 pg_partman--4.4.0--4.5.0.sql
-rw-rw-r-- 1 keith keith 188926 May  3 11:28 pg_partman--4.4.1--4.5.0.sql
-rw-rw-r-- 1 keith keith  60613 May 11 14:13 pg_partman--4.5.0--4.5.1.sql
-rw-rw-r-- 1 keith keith  78361 Aug  2 19:10 pg_partman--4.5.1--4.6.0.sql
[...]

As far as PG is concerned, it's not technically an upgrade or a downgrade. It's just following the shortest path from the existing version to the target. You can see this from within PostgreSQL as well

select * from pg_extension_update_paths('pg_partman') where source = '2.3.4' and target = '3.0.0';
 source | target |                                    path                                     
--------+--------+-----------------------------------------------------------------------------
 2.3.4  | 3.0.0  | 2.3.4--2.4.0--2.4.1--2.5.0--2.5.1--2.6.0--2.6.1--2.6.2--2.6.3--2.6.4--3.0.0

If a valid path does not exist, PostgreSQL will return NULL

select * from pg_extension_update_paths('pg_partman') where source = '2.3.4' and target = '1.0.0';
 source | target |  path  
--------+--------+--------
 2.3.4  | 1.0.0  | «NULL»

This leads me to my recommendation for how this should actually be handled. Instead of trying to compare versions to find the latest or to ensure that the user's given version is "greater", it should just check and see if a valid update path exists. This would allow the postgresql_ext module to accept any extension versioning scheme that PG itself supports and also indirectly support downgrading (if the extension author provides that path).

I'd be happy to try and work on this update for the module if the community is interested. Please let me know.

Case mismatch in postgresql_set causes MODULE FAILURE

SUMMARY

Then trying to set timezone with postgresql_set I got a large error stack ending in MODULE FAILURE instead of it either:

• Just Working, or:
• Giving a clue to the workaround (use name: TimeZone).

ISSUE TYPE

• Bug Report

COMPONENT NAME

MODULE community.general.postgresql_set

ANSIBLE VERSION

ansible 2.10.1
  config file = None
  configured module search path = ['/home/dave/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/dave/.local/lib/python3.6/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.6.9 (default, Oct  8 2020, 12:12:24) [GCC 8.4.0]

CONFIGURATION

None.

OS / ENVIRONMENT

Postgres installed on Ubuntu 18.04.2 LTS with:

    - name: Add postgres apt key
      apt_key:
        url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
        state: present

    - name: Add postgres apt repo
      apt_repository:
          repo: deb http://apt.postgresql.org/pub/repos/apt bionic-pgdg main
          state: present

    - name: Install postgres and Python prerequisites
      apt:
        name:
          - postgresql-10
          - python-setuptools
          - python-pip

STEPS TO REPRODUCE

    - name: Set time zone to UTC
      become_user: postgres
      community.general.postgresql_set:
        name: "Timezone"
        value: "utc"
      register: timezone

EXPECTED RESULTS

Either it Just Worked, or gave me an error telling me what happened.
Even this would be useful, but I didn't see it in the output:

community.postgresql/plugins/modules/postgresql_set.py

Lines 201 to 202 in ed91c87

	except Exception as e:
	module.fail_json(msg="Unable to get %s value due to : %s" % (name, to_native(e)))

ACTUAL RESULTS

The full traceback is:
Traceback (most recent call last):
  File "/var/tmp/ansible-tmp-1610565309.636085-418-62394782136681/AnsiballZ_postgresql_set.py", line 102, in <module>
    _ansiballz_main()
  File "/var/tmp/ansible-tmp-1610565309.636085-418-62394782136681/AnsiballZ_postgresql_set.py", line 94, in _ansiballz_main
    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)
  File "/var/tmp/ansible-tmp-1610565309.636085-418-62394782136681/AnsiballZ_postgresql_set.py", line 40, in invoke_module
    runpy.run_module(mod_name='ansible_collections.community.general.plugins.modules.postgresql_set', init_globals=None, run_name='__main__', alter_sys=True)
  File "/usr/lib/python2.7/runpy.py", line 188, in run_module
    fname, loader, pkg_name)
  File "/usr/lib/python2.7/runpy.py", line 82, in _run_module_code
    mod_name, mod_fname, mod_loader, pkg_name)
  File "/usr/lib/python2.7/runpy.py", line 72, in _run_code
    exec code in run_globals
  File "/tmp/ansible_community.general.postgresql_set_payload_17xqrt/ansible_community.general.postgresql_set_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_set.py", line 447, in <module>
  File "/tmp/ansible_community.general.postgresql_set_payload_17xqrt/ansible_community.general.postgresql_set_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_set.py", line 350, in main
  File "/tmp/ansible_community.general.postgresql_set_payload_17xqrt/ansible_community.general.postgresql_set_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_set.py", line 202, in param_get
IndexError: list index out of range
fatal: [db_test]: FAILED! => {
    "changed": false,
    "module_stderr": "Shared connection to 10.1.0.4 closed.\r\n",
    "module_stdout": "Traceback (most recent call last):\r\n  File \"/var/tmp/ansible-tmp-1610565309.636085-418-62394782136681/AnsiballZ_postgresql_set.py\", line 102, in <module>\r\n    _ansiballz_main()\r\n  File \"/var/tmp/ansible-tmp-1610565309.636085-418-62394782136681/AnsiballZ_postgresql_set.py\", line 94, in _ansiballz_main\r\n    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\r\n  File \"/var/tmp/ansible-tmp-1610565309.636085-418-62394782136681/AnsiballZ_postgresql_set.py\", line 40, in invoke_module\r\n    runpy.run_module(mod_name='ansible_collections.community.general.plugins.modules.postgresql_set', init_globals=None, run_name='__main__', alter_sys=True)\r\n  File \"/usr/lib/python2.7/runpy.py\", line 188, in run_module\r\n    fname, loader, pkg_name)\r\n  File \"/usr/lib/python2.7/runpy.py\", line 82, in _run_module_code\r\n    mod_name, mod_fname, mod_loader, pkg_name)\r\n  File \"/usr/lib/python2.7/runpy.py\", line 72, in _run_code\r\n    exec code in run_globals\r\n  File \"/tmp/ansible_community.general.postgresql_set_payload_17xqrt/ansible_community.general.postgresql_set_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_set.py\", line 447, in <module>\r\n  File \"/tmp/ansible_community.general.postgresql_set_payload_17xqrt/ansible_community.general.postgresql_set_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_set.py\", line 350, in main\r\n  File \"/tmp/ansible_community.general.postgresql_set_payload_17xqrt/ansible_community.general.postgresql_set_payload.zip/ansible_collections/community/general/plugins/modules/postgresql_set.py\", line 202, in param_get\r\nIndexError: list index out of range\r\n",
    "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error",
    "rc": 1

postgresql_set adding extra quotes for comma seperated list

SUMMARY

Using postgresql_set module to set a value for a setting ( For example 'shared_preload_libraries') that contains comma separated list. The module is wrapping extra quotes around the resulting value in postgresql.auto.conf file causing the postgresql service to fail.

For example:

- postgresql_set:
     name: shared_preload_libraries
     value: somelib,someotherlib

Results in this line in postgresql.auto.conf config. Because of the inner "", it treats this as a single module name and service fails to start.
shared_preload_libraries = '"somelib,someotherlib"'
The expected value is
shared_preload_libraries = 'somelib,someotherlib'

However, a single value does work

- postgresql_set:
     name: shared_preload_libraries
     value: somelib

correctly results in
shared_preload_libraries = 'somelib'

If I have tried various ways of formatting the string without success.

Additionally, it would be very helpful if a list object could be passed to value instead of a string (Currently does not work).

- postgresql_set:
     name: shared_preload_libraries
     value:
        - somelib
        - someotherlib

ISSUE TYPE

• Bug Report

COMPONENT NAME

community.postgresql.postgresql_set

ANSIBLE VERSION

  ansible 2.10.5
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/home/user/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3/dist-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.6.9 (default, Jan 26 2021, 15:33:00) [GCC 8.4.0]

CONFIGURATION

ANSIBLE_PIPELINING(/etc/ansible/ansible.cfg) = True

OS / ENVIRONMENT

Ubuntu 18.04 LTS

postgresql_user module fails with password on version 10.x if server is fips enabled

From @Andersson007 on Apr 06, 2020 11:27

Copied from ansible/ansible#41787
Initially reported by @jbscalia

Postgresql_user needs to support AES-256 encryption.

SUMMARY

If a server is FIPS-140-2 enabled, md5 is not permitted as an "encryption/hashing" algorithm. PostgreSQL version 10 supports AES-256 encryption, but postgresql_user does not support that option, and is unusable on a FIPS enabled server.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_user

ANSIBLE VERSION

ansible --version
ansible 2.4.2.0
config file = /home/419635/.ansible.cfg
configured module search path = [u'/home/419635/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
ansible python module location = /usr/lib/python2.7/site-packages/ansible
executable location = /usr/bin/ansible
python version = 2.7.5 (default, May 3 2017, 07:55:04) [GCC 4.8.5 20150623 (Red Hat 4.8.5-14)]

##### CONFIGURATION
ansible-config dump --only-changed
DEFAULT_FORKS(/home/419635/.ansible.cfg) = 2
DEFAULT_HOST_LIST(/home/419635/.ansible.cfg) = [u'/home/419635/postgres/9.6/ansible/environm
DEFAULT_LOG_PATH(/home/419635/.ansible.cfg) = /home/419635/ansible.log
DEFAULT_ROLES_PATH(/home/419635/.ansible.cfg) = [u'/etc/ansible/roles', u'/usr/share/ansible
DEFAULT_VAULT_PASSWORD_FILE(/home/419635/.ansible.cfg) = /home/419635/postgres/10/ansible/.v
PERSISTENT_CONNECT_TIMEOUT(/home/419635/.ansible.cfg) = 30

##### OS / ENVIRONMENT
Redhat Enterprise Linux 7.4, kernel 3.10.0-514.el7

##### STEPS TO REPRODUCE
create a play using the postgresql_user module as shown and attempt to run the play on a fips enabled server.

""
- name: Ensure the "{{ pg_replication_user }}" role exists in our primary
  become: yes
  become_user: "{{ postgresql_superuser }}"
  postgresql_user:
    name: "{{ pg_replication_user }}"
    password: "{{ pg_replication_user_password }}"
    encrypted: True
    port: "{{ pg_port }}"
    role_attr_flags: LOGIN,REPLICATION

EXPECTED RESULTS

Expected the user to be created in the specified PostgreSQL cluster.

ACTUAL RESULTS

The module failed.

TASK [replica : Ensure the "rplctn_usr" role exists in our primary] ************************
atal: [cl-rhdb-7025.ba.ssa.gov]: FAILED! => {"changed": false, "module_stderr": "Shared connection to cl-rhdb-7025.ba.ssa.gov closed.\r\n", "module_stdout": "Traceback (most recent call last):\r\n  File \"/tmp/ansible_IsugL6/ansible_module_postgresql_user.py\", line 844, in <module>\r\n    main()\r\n  File \"/tmp/ansible_IsugL6/ansible_module_postgresql_user.py\", line 803, in main\r\n    role_attr_flags, encrypted, expires, no_password_changes, conn_limit)\r\n  File \"/tmp/ansible_IsugL6/ansible_module_postgresql_user.py\", line 338, in user_alter\r\n    pwchanging = user_should_we_change_password(current_role_attrs, user, password, encrypted)\r\n  File \"/tmp/ansible_IsugL6/ansible_module_postgresql_user.py\", line 304, in user_should_we_change_password\r\n    hashed_password = 'md5{0}'.format(md5(to_bytes(password) + to_bytes(user)).hexdigest())\r\nValueError: error:060800A3:digital envelope routines:EVP_DigestInit_ex:disabled for fips\r\n", "msg": "MODULE FAILURE", "rc": 1}

Copied from original issue: ansible/ansible#119

postgresql_privs regression when deleting a user/role

SUMMARY

postgresql_privs should not fail if state: absent and the given role does not exist. This used to work as expected in the past.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_privs

ANSIBLE VERSION

ansible --version
ansible [core 2.11.5] 
  config file = None
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.9/dist-packages/ansible
  ansible collection location = /root/.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.0.1
  libyaml = True

COLLECTION VERSION

ansible-galaxy collection list community.postgresql

# /usr/local/lib/python3.9/dist-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.4.0  

CONFIGURATION

No output

OS / ENVIRONMENT

I can reproduce the problem on Ubuntu 20.04 and Debian Bullseye. Postgres is version 12 in both cases.

STEPS TO REPRODUCE

Here is the task I'm using, which has been working fine for a long time:

- name: Revoke all privileges of former users
  postgresql_privs:
    database: postgres
    login_host: localhost
    login_user: postgres
    login_password: password
    privs: ALL
    roles: someuser
    type: database
    state: absent

Here is how to reproduce the successful case using a previous version of ansible:

docker run -d --rm --name postgres  -e POSTGRES_HOST_AUTH_METHOD=trust -v $HOME:/root  postgres:bullseye
docker exec -it postgres /bin/bash
# Remaining steps are inside the postgres container

# Install previous version of ansible and also install psycopg2
apt-get update && apt-get install -y python3-pip python3-psycopg2 && pip3 install ansible==2.10.6

ansible-playbook --step --start-at-task "Revoke all privileges of former users" /root/path/to/playbook.yml

TASK [my_playbook : Revoke all privileges of former users] ********************************************************************************************************************************
ok: [localhost]

Exit the container and docker stop postgres to get rid of it after seeing the successful case.

Here is how to reproduce the breakage on the most recent version of ansible:

docker run -d --rm --name postgres  -e POSTGRES_HOST_AUTH_METHOD=trust -v $HOME:/root  postgres:bullseye
docker exec -it postgres /bin/bash
# Remaining steps are inside the postgres container

# Install most recent version of ansible and also install psycopg2
apt-get update && apt-get install -y python3-pip python3-psycopg2 && pip3 install ansible

ansible --version
ansible [core 2.11.5] 
  config file = None
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.9/dist-packages/ansible
  ansible collection location = /root/.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.0.1
  libyaml = True

# Run the same playbook again and crank up the verbosity since this will fail
ansible-playbook -vvvv --step --start-at-task "Revoke all privileges of former users" /root/path/to/playbook.yml

fatal: [localhost]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "database": "postgres",
            "fail_on_role": true,
            "grant_option": null,
            "host": "localhost",
            "login": "postgres",
            "login_host": "localhost",
            "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "login_unix_socket": "",
            "login_user": "postgres",
            "objs": null,
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "port": 5432,
            "privs": "ALL",
            "roles": "someuser",
            "schema": null,
            "session_role": null,
            "ssl_mode": "prefer",
            "state": "absent",
            "target_roles": null,
            "trust_input": true,
            "type": "database",
            "unix_socket": "",
            "usage_on_types": true
        }
    },
    "msg": "Role 'someuser' does not exist"
}

EXPECTED RESULTS

The desired state of the postgres role in the database is state: absent, so the task should delete the role if it exists or just move on without error if the role does not exist.

ACTUAL RESULTS

The task fails and crashes the ansible run when the role does not exist. See the steps to reproduce for the -vvvv output.

We are looking for new maintainers and contributors

SUMMARY

Hi everyone!

This collection is a part of Ansible package and is looking for new maintainers and contributors.
To learn how to contribute to the collection and how to maintain it / become a maintainer, refer to the Contributing guidelines which also includes the Quick-start guide.

This collection is made by good people like you.
If you are interested in becoming a maintainer, feel free to create an issue in this repository!

postgresql_user applying changes in check mode

SUMMARY

I am currently creating a playbook to configure a postgresdb running on kubernetes and am facing unwanted behaviour with the dry run of the user provisioning.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_user

ANSIBLE VERSION

ansible [core 2.11.2]
  config file = /Users/manuelgall/projects/devops/platform-ansible/ansible.cfg
  configured module search path = ['/Users/manuelgall/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/ansible
  ansible collection location = /Users/manuelgall/.ansible/collections:/usr/share/ansible/collections
  executable location = /Library/Frameworks/Python.framework/Versions/3.9/bin/ansible
  python version = 3.9.2 (v3.9.2:1a79785e3e, Feb 19 2021, 09:06:10) [Clang 6.0 (clang-600.0.57)]
  jinja version = 3.0.1
  libyaml = True

COLLECTION VERSION

# /Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.3.0

# /Users/manuelgall/.ansible/collections/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.4.0

CONFIGURATION

ANSIBLE_NOCOWS(env: ANSIBLE_NOCOWS) = True
INTERPRETER_PYTHON(/Users/manuelgall/projects/devops/platform-ansible/ansible.cfg) = /Library/Frameworks/Python.framework/Versions/3.9/bin/python3.9

OS / ENVIRONMENT

macOS 11.2.3
Kubernetes v1.16.3 on Ubuntu 20.04.2 LTS

STEPS TO REPRODUCE

• have postgresdb running on kubernetes
• port-forward to postgresdb service
• run playbook with --check flag
• check for users with \du

command to run playbook

ansible-playbook -i inifile.ini yamlfile.yml --vault-password-file .vault.password -vvv --diff --tags "createuser" --check --connection=local

example playbook

- name: provision user
  no_log: false
  postgresql_user:
    login_db: dbname
    name: "{{ 'user' + item.name }}"
    password: "{{ user_password }}"
    login_user: "{{ admin_user }}"
    login_password: "{{ admin_password }}"
    login_host: "{{ host }}"
    port: "{{ port }}"
  tags: createuser
  loop: "{{ users }}"

EXPECTED RESULTS

I expect the output to be a dry run and no changes to be applied. The users to be provisioned should not be listed when checking with \du.

ACTUAL RESULTS

Changes are applied and users are provisioned to database. \du is showing the provisioned users.

changed: [localhost] => (item=None) => {
    "censored": "the output has been hidden due to the fact that 'no_log: true' was specified for this result",
    "changed": true
}
changed: [localhost] => {
    "censored": "the output has been hidden due to the fact that 'no_log: true' was specified for this result",
    "changed": true
}
META: role_complete for localhost
META: ran handlers
META: ran handlers

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

Release plan

SUMMARY

(partially copied from ansible-collections/community.crypto#74 , thanks to @felixfontein)

We should decide on how to release this collection (w.r.t. versioning).
Small collections like this one don't need a complex plan like the one for community.general and community.network.
So how about the following?

1. Release minor and patch releases whenever we want (like after adding new features or fixing bugs). Since this collection is small, there's no need to fix things in advance. Just add features, and after a feature either wait a bit longer for more features/bugs, or make a release.

I suggest releasing without branching https://github.com/ansible/community-docs/blob/main/releasing_collections_without_release_branches.rst
Breaking changes don't work with this schema but we might change the approach and start releasing from branches when needed.

For release support timeline, refer to README (we should keep it updated)

First release will be 0.1.0

customising login_unix_socket is not working

SUMMARY

When trying to adjust the login_unix_socket option

      - set_fact: postgresql_conn="/tmp/.s.PGSQL.{{ item.port }}"
        with_items:
          - "{{ postgresql_config.app[instance] }}"

The modules community.postgresql.postgresql_db and community.postgresql.postgresql_user (at least) ignore the full path and append the default setting unix socket .s.PGSQL.5432.
As a result, the error below manifests

TASK [Create new databases] **************************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: 	connections on Unix domain socket "/tmp/.s.PGSQL.5433/.s.PGSQL.5432"?
failed: [light-20-pro] (item=pmacct) => {"ansible_loop_var": "item", "changed": false, "item": "pmacct", "msg": "unable to connect to database: could not connect to server: Not a directory\n\tIs the server running locally and accepting\n\tconnections on Unix domain socket \"/tmp/.s.PGSQL.5433/.s.PGSQL.5432\"?\n"}

ISSUE TYPE

• Bug Report

COMPONENT NAME

• community.postgresql.postgresql_db
• community.postgresql.postgresql_user

ANSIBLE VERSION

> ansible --version
ansible 2.10.5
  config file = /home/xxx/ip-spotlight/ansible/ansible.cfg
  configured module search path = ['/home/xxx/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/xxx/.pyenv/versions/3.8.7/lib/python3.8/site-packages/ansible
  executable location = /home/xxx/.pyenv/versions/3.8.7/bin/ansible
  python version = 3.8.7 (default, Feb 13 2021, 11:13:05) [GCC 7.5.0]

CONFIGURATION

DEFAULT_HOST_LIST(/home/app/ip-spotlight/ansible/ansible.cfg) = ['/home/xxx/ip-spotlight/ansible/hosts']
DEFAULT_JINJA2_EXTENSIONS(/home/xxx/ip-spotlight/ansible/ansible.cfg) = jinja2.ext.do,jinja2.ext.i18n,jinja2.ext.loopcontrols
INTERPRETER_PYTHON(/home/xxx/ip-spotlight/ansible/ansible.cfg) = ~/.pyenv/shims/python

OS / ENVIRONMENT

> hostnamectl
   Static hostname: light-00-micro
         Icon name: computer-vm
           Chassis: vm
        Machine ID: c88688bb337042c3b0842a372753675f
           Boot ID: 583af949666640e59b22e6ac33e8ed14
    Virtualization: vmware
  Operating System: openSUSE Leap 15.2
       CPE OS Name: cpe:/o:opensuse:leap:15.2
            Kernel: Linux 5.3.18-lp152.63-default
      Architecture: x86-64

STEPS TO REPRODUCE

      - set_fact: postgresql_conn="/tmp/.s.PGSQL.{{ item.port }}"
        with_items:
          - "{{ postgresql_config.app[instance] }}"

      - name: "Create new databases"
        become_user: postgres
        community.postgresql.postgresql_db: login_unix_socket="{{ postgresql_conn }}" name="{{ item }}" state=present template="template0" encoding="UTF-8" lc_collate="en_US.UTF-8" lc_ctype="en_US.UTF-8"
        with_items:
          - pmacct

      - name: "Add the database user"
        become_user: postgres
        community.postgresql.postgresql_user: login_unix_socket="{{ postgresql_conn }}" name="{{ username }}" encrypted=yes password="{{ database.password }}" role_attr_flags=SUPERUSER,NOINHERIT,CREATEROLE,CREATEDB,LOGIN,REPLICATION state=present 

EXPECTED RESULTS

Unix domain socket should be set to

/tmp/.s.PGSQL.5433

ACTUAL RESULTS

/tmp/.s.PGSQL.5433/.s.PGSQL.5432

Change server version handling in postgresql_slot module

SUMMARY

There's the line:
if self.cursor.connection.server_version < 96000

Which should be 90600, i think, because of https://www.postgresql.org/docs/current/libpq-status.html#LIBPQ-PQSERVERVERSION

NO tests needed

Thanks @asifiqbal !

ISSUE TYPE

• Bug Report

postgresql_slot

Add force to DROP Database

SUMMARY

I like to have a force option with postgresql_db to allow drop database.

ISSUE TYPE

• Feature data
  force option with postgresql_db

COMPONENT NAME

`force: yes`

ADDITIONAL INFORMATION

https://www.postgresql.org/docs/current/sql-dropdatabase.html has FORCE option available
It allows to drop a database without care for active sessions to it

- hosts: database

  tasks:
     - name: Drop database mydb
       postgresql_db:
           name: mydb
           state: absent
           force: yes

extension version without decimal causes postgresql_info exception

SUMMARY

When an extension is installed that doesn't have a . in the version, the postgresql_info module throws an exception.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_info

ANSIBLE VERSION

ansible 2.10.11
  config file = None
  configured module search path = ['/home/phemmer/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/phemmer/.local/lib/python3.9/site-packages/ansible
  executable location = /home/phemmer/.local/bin/ansible
  python version = 3.9.5 (default, May 14 2021, 00:00:00) [GCC 11.1.1 20210428 (Red Hat 11.1.1-1)]

COLLECTION VERSION

# /home/phemmer/.local/lib/python3.9/site-packages/ansible_collections
Collection           Version
-------------------- -------
community.postgresql 1.1.1  

CONFIGURATION

OS / ENVIRONMENT

Debian 10/Buster
PostgreSQL 13

STEPS TO REPRODUCE

1. Install an extension without a . in the version, for example pguint.

# psql mydb
mydb=> select * from pg_available_extensions where name='uint';
 name | default_version | installed_version |        comment         
------+-----------------+-------------------+------------------------
 uint | 0               |                   | unsigned integer types
(1 row)

mydb=> create extension uint;
CREATE EXTENSION

2. use postgresql_info.

ansible myhost -m postgresql_info

EXPECTED RESULTS

Works

ACTUAL RESULTS

# ansible myhost -m postgresql_info
The full traceback is:
Traceback (most recent call last):
  File "master:/home/phemmer/git/ht/edge/ansible/ht/mitogen/ansible_mitogen/runner.py", line 975, in _run
    self._run_code(code, mod)
  File "master:/home/phemmer/git/ht/edge/ansible/ht/mitogen/ansible_mitogen/runner.py", line 939, in _run_code
    exec(code, vars(mod))
  File "master:/home/phemmer/.local/lib/python3.9/site-packages/ansible_collections/community/general/plugins/modules/postgresql_info.py", line 1030, in <module>
  File "master:/home/phemmer/.local/lib/python3.9/site-packages/ansible_collections/community/general/plugins/modules/postgresql_info.py", line 1026, in main
  File "master:/home/phemmer/.local/lib/python3.9/site-packages/ansible_collections/community/general/plugins/modules/postgresql_info.py", line 617, in collect
  File "master:/home/phemmer/.local/lib/python3.9/site-packages/ansible_collections/community/general/plugins/modules/postgresql_info.py", line 973, in get_db_info
  File "master:/home/phemmer/.local/lib/python3.9/site-packages/ansible_collections/community/general/plugins/modules/postgresql_info.py", line 734, in get_ext_info
IndexError: list index out of range
myhost | FAILED! => {
    "ansible_facts": {
        "discovered_interpreter_python": "/usr/bin/python3"
    },
    "changed": false,
    "module_stderr": "Traceback (most recent call last):\n  File \"master:/home/phemmer/git/ht/edge/ansible/ht/mitogen/ansible_mitogen/runner.py\", line 975, in _run\n    self._run_code(code, mod)\n  File \"master:/home/phemmer/git/ht/edge/ansible/ht/mitogen/ansible_mitogen/runner.py\", line 939, in _run_code\n    exec(code, vars(mod))\n  File \"master:/home/phemmer/.local/lib/python3.9/site-packages/ansible_collections/community/general/plugins/modules/postgresql_info.py\", line 1030, in <module>\n  File \"master:/home/phemmer/.local/lib/python3.9/site-packages/ansible_collections/community/general/plugins/modules/postgresql_info.py\", line 1026, in main\n  File \"master:/home/phemmer/.local/lib/python3.9/site-packages/ansible_collections/community/general/plugins/modules/postgresql_info.py\", line 617, in collect\n  File \"master:/home/phemmer/.local/lib/python3.9/site-packages/ansible_collections/community/general/plugins/modules/postgresql_info.py\", line 973, in get_db_info\n  File \"master:/home/phemmer/.local/lib/python3.9/site-packages/ansible_collections/community/general/plugins/modules/postgresql_info.py\", line 734, in get_ext_info\nIndexError: list index out of range\n",
    "module_stdout": "",
    "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error",
    "rc": 1
}

postgresql_privs: Impossible to set column-level privileges

Copied from ansible-collections/community.general#118
Initially reported by @Andersson007

Copied from ansible/ansible#18955
Initially reported by @jnv

ISSUE TYPE

• Feature Idea

COMPONENT NAME

postgresql_privs

ANSIBLE VERSION

ansible 2.3.0.0
  config file = 
  configured module search path = Default w/o overrides
  python version = 2.7.13 (default, Jan 19 2017, 14:48:08) [GCC 6.3.0 20170118]

CONFIGURATION

OS / ENVIRONMENT

Host: Ubuntu 17.04

Target: Ubuntu 14.04, PostgreSQL 9.4

SUMMARY

It is not possible to set privileges for individual columns through the postgresql_privs, expected way to do this (i.e. privs: "SELECT(column,column)") fails. If there is an alternative solution, it is not described in documentation.

STEPS TO REPRODUCE

With PostgreSQL database mydb and table data_sources being setup, I use the following command in my playbook:

- name: Grant privileges to columns in data_sources
  postgresql_privs:
    database: mydb
    roles: mydb_reader
    privs: "SELECT(id,name,type)"
    objs: "data_sources"

EXPECTED RESULTS

Permissions to select only id, name and type columns from table data_sources is granted to the user mydb_reader.

ACTUAL RESULTS

Module fails with the following message:

Invalid privileges specified: frozenset(['TYPE)', 'NAME', 'SELECT(ID'])

NOTES

Attempt to grant privileges through postgresql_user module yields a similar result (Invalid privs specified for table: TYPE) NAME SELECT(ID), which is very similar to the equivalent MySQL's module issue ansible/ansible-modules-core#1120.

I think the solution could be to either do a smarter parsing of the privs parameter, or provide an alternative syntax for column-specific permissions, e.g. : similar to functions objects permissions.

Though I wonder why I can't just pass a list of privileges instead of comma delimeted string? This makes parsing much simpler and can be implemented in backward-compatible manner.

So far the only workaround for me is to execute psql manually.

---

Copied from original issue: ansible/ansible-modules-core#1529

Add TimeZone example in doc

SUMMARY

Hello,

I lost a few hours trying to understand why i got an error like 'No such parameter' while setting 'timezone' with postgresql_set.

Finally i ended up on this post : #41

I'm sure, many will have the same troubleshoot with that parameter so i guess it would be nice to add an example on the documentation with that TimeZone name, and maybe add an explanation or a warning somehow.

What do you think ?

ISSUE TYPE

• Documentation Report

COMPONENT NAME

postgresql_set

ANSIBLE VERSION

ansible 2.10.6