How to execute SQL query on PostgreSQL?

[Daniel Huesca]

I'm reading the docs @ https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_query_module.html 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?

[Dick Visser]

On Tue, 30 Nov 2021 at 16:48, Daniel Huesca <daniel...@wefox.com> wrote:

> Any idea on what I'm doing wrong?

Not posting text, but pixely/hard-to-read images, for starters...

[Daniel Huesca]

This is the code I'm using:

- 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

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;']"}

[Dick Visser]

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)

> --
> You received this message because you are subscribed to the Google Groups "Ansible Project" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/a09bb040-e64e-4bc1-aaa8-325c4af22fbcn%40googlegroups.com.



--
Dick Visser
Trust & Identity Service Operations Manager
GÉANT

[Nico Kadel-Garcia]

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.

> To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/CAL8fbwPGscEH_pHRmuYskxNw7qqnX%2BqOEnu0%2BgRDPDbQR-AxBw%40mail.gmail.com.