search

ansible-collections / community.postgresql

Manage PostgreSQL with Ansible
https://galaxy.ansible.com/ui/repo/published/community/postgresql/
Other
108 stars 88 forks source link

postgresql_db dump gives incorrect error 'password authentication failed' #66

Closed asifiqbal closed 3 years ago

asifiqbal commented 3 years ago
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
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": []
}
Andersson007 commented 3 years ago

@asifiqbal hi, thanks for the report! I investigated a little bit.

There's no artificial exceptions thrown around this functionality (and in general containing this message).

The dump functionality is implemented via

503 def do_with_password(module, cmd, password):
504     env = {}
505     if password:
506         env = {"PGPASSWORD": password}
507     executed_commands.append(cmd)
508     rc, stderr, stdout = module.run_command(cmd, use_unsafe_shell=True, environ_update=env)
509     return rc, stderr, stdout, cmd

This runs pg_dump via shell and return pg_dumps stderr when the command fails

653                 if rc != 0:
654                     module.fail_json(msg=stderr, stdout=stdout, rc=rc, cmd=cmd)

pg_dump: [archiver (db)] connection to database \"jira\" failed: FATAL: password authentication failed for user \"jirauser\"\nFATAL: password authentication failed for user \"jirauser\"\n it is definitely thrown by pg_dump.

@asifiqbal could you try 1) to do the same on your server directly via shell using bare pg_dump, the user, password, host, and dump location just to see what will happen? 2) i tried this with PostgreSQL 13 on Fedora33, got only filename: Permission denied. It can also be related to RDS. I don't use it but there were reports related to databases on RDS because they are internally customized.

Andersson007 commented 3 years ago

@asifiqbal could you please look at my previous comment and give feedback? Thanks!

Andersson007 commented 3 years ago

as no response, I'll close the issue. We could reopen it if there are any updates.