Run a postgresql command with ansible playbook. Postgresql requires password

Question

I would like to write an Ansible playbook which tries to handle some postgresql command. I did earlier a similar playbook, but in that case Postgresql didn't need password.

I thought I could use my old playbook, which works fine, but I was wrong. This database requires password. I know that Ansible has postgresql support which is fine (postgresql_db, postgresql_ext, postgresql_lang, postgresql_privs, postgresql_user) and with them I can do half of the work and it handles password just fine, BUT I can't figure out how to send a simple sql commands to my database. In my old playbook I used "shell" command to send those requests (eg: shell: "psql {{ databases.name }} --username {{ admin_user}} -c 'DROP SCHEMA public CASCADE;'").

And also I would like to upload a db_dump. Earlier I used psql for this, but this new database requires password...

Can someone give me tips how to handle this situation? I'm using ansible 2.0 and the database can be reached only from a specific remote host.

Thanks

Craig Ringer · Accepted Answer · 2015-09-30 11:44:08Z

1

Create a .pgpass file at a non-default location as part of your playbook, and put the password there. Specify its location in the PGPASSFILE environment variable. Delete it once it's no longer needed.

See libpq environment variables.

Alternately I think you can use a connection string to connect and specify the password as part of the connection string, e.g.

 'dbname=mydb user=fred password=borkbork'

(untested)

answered Sep 30, 2015 at 11:44

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Craig Ringer Over a year ago

@kilimandzsaro Note that command lines can be visible to other users in the system via ps etc so be careful if using passwords in connection strings.

kilimandzsaro Over a year ago

Thanks for the note, I'm trying the .pgpass solution and it seems to work for my case.

Denis Gasparin · Accepted Answer · 2017-10-01 20:01:16Z

1

You can use a new role that provides four new modules:

postgresql_table: ensure that a table is present (or absent) in database
postgresql_row: ensure that a row is present (or absent) in a table
postgresql_query: execute an arbitrary query in database and return results
postgresql_command: execute an arbitrary query in database

Look here for docs: https://github.com/rtshome/ansible_pgsql

answered Oct 1, 2017 at 20:01

Denis Gasparin

111 bronze badge

Comments

Arghya Sadhu · Accepted Answer · 2020-09-02 03:11:48Z

0

You can use the following module in Ansible https://docs.ansible.com/ansible/latest/modules/postgresql_query_module.html

For example:

- name: Select query to db acme with positional arguments and non-default credentials
  postgresql_query:
    db: acme
    login_user: django
    login_password: mysecretpass
    query: SELECT * FROM acme WHERE id = %s AND story = %s
    positional_args:
    - 1
    - test

edited Sep 2, 2020 at 3:11

Arghya Sadhu

45.2k10 gold badges100 silver badges122 bronze badges

answered Sep 1, 2020 at 20:08

Jenya

11 bronze badge

Collectives™ on Stack Overflow

Run a postgresql command with ansible playbook. Postgresql requires password

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related