Specify PostgreSQL passwords in Ansible Playbook

2
votes

New to Ansible, Running version 2.1.0. I've written an Ansible playbook that runs a PostgreSQL query against a group of hosts. When I specify the SQL DB password in the shell command it works, but I'm looking to run the playbook against a group of hosts and need a better way to input the passwords as they are all unique. Could anyone suggest a better way to do this?

---

- hosts: Test_Hosts    
  sudo: yes    
  sudo_user: root    
  gather_facts: yes
  tasks:    
  - name: Login to DB and run command    
    shell: export PGPASSWORD='Password'; psql -U 'user' -d 'db' -c 'select * FROM table'; 
    register: select_all_from_table

  - name: Display table contents    
    debug: msg="{{ select_all_from_table.stdout }}"

I saw another thread on the topic but was not sure how to implement the suggestion: Run a postgresql command with ansible playbook. Postgresql requires password

2
postgresqlansibleansible-playbook

2 Answers

5
votes

Ansible allows you to set environment variables for a task using the environment parameter to any task.

So in your case you could just do this:

  - name: Login to DB and run command    
    shell: psql -U 'user' -d 'db' -c 'select * FROM table'; 
    register: select_all_from_table
    environment:
      PGPASSWORD: '{{ pgpassword }}'

And then set the pgpassword variable at the group or host level.

0
votes

I just ran into this issue today and this is what worked for me. On Linux you can package all the credentials into a ~/.pgpass hidden file.

Just create it locally (in this case in ./files/pgpass) and then use ansible to copy it onto the host before you run the psql commands.

- name: set passwd file for PSQL
  copy:
    src: files/pgpass
    dest: ~/.pgpass
    mode: 0600           ### important: will not work with wrong permissions

- name: PSQL command
  shell: "psql -U 'user' -d 'db' -c 'select * FROM table'"
  register: select_all_from_table

The file contents must be in the following format:

hostname:port:database:username:password

However, you can use wildcards, so mine looks like this, for example:

*:*:db1:user1:passwd1
*:*:db2:user2:passwd2

See documentation for more details: https://www.postgresql.org/docs/9.1/static/libpq-pgpass.html