I’m reading the docs @ community.postgresql.postgresql_query module – Run PostgreSQL queries — Ansible Community Documentation but I keep getting errors when trying to execute a few simple commands…
This is the output I’m getting
Any idea on what I’m doing wrong?
Not posting text, but pixely/hard-to-read images, for starters...
This is the code I’m using:
This is the console output:
fatal: [localhost]: FAILED! => {“changed”: false, “msg”: “Cannot execute SQL ‘GRANT %(role)s TO %(admin)s; REASSIGN OWNED BY %(role)s TO %(admin)s; DROP OWNED BY %(role)s; DROP ROLE %(role)s;’ {‘role’: ‘delete-user’, ‘admin’: ‘master’}: syntax error at or near "‘delete-user’"\nLINE 1: GRANT ‘delete-user’ TO ‘master’; REASSIGN OWNED BY 'delete-u…\n ^\n, query list: [‘GRANT %(role)s TO %(admin)s; REASSIGN OWNED BY %(role)s TO %(admin)s; DROP OWNED BY %(role)s; DROP ROLE %(role)s;’]”}
I see that you're supplying multiple SQL statements at the 'query' parameter.
I don't know the module that well, but it could be that that is not supported?
In that case, probably you could loop over a list of queries:
- name: DELETE POSTGRESQL ROLE
community.postgresql.postgresql_query:
login_host: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
db: db_test
login_user: master
login_password: 0123456789
query: GRANT %(role)s TO %(admin)s; REASSIGN OWNED BY %(role)s TO
%(admin)s; DROP OWNED BY %(role)s; DROP ROLE %(role)s;
named_args:
role: delete-user
admin: master
loop:
- GRANT %(role)s TO %(admin)s;
- REASSIGN OWNED BY %(role)s TO %(admin)s;
- DROP OWNED BY %(role)s;
- DROP ROLE %(role)s;
(not tried, ymmv)
The SQL is not idempotent: It will fail if run again, and nothing is
apparent in the code snippet to prevent ansible from trying to run it
even if it's already succeeded. Check the status of the relevant SQL
server to see whether the relevant roles and accounts exist now.
