Filtering database names from RETURN VALUES of postgresql_info

Get Help
1

I am trying to GRANT pg_read_all_date to a certain role on all existing databases exept postgres. template0, template1.

To get those infos I do

- name: get postgres infos
  hosts: "{{ targets | default('postgres') }}"
  tasks:
    - name: get postgres infos
      community.postgresql.postgresql_info:
        login_unix_socket: /tmp
        filter: 'databases'
      become: true
      become_user: postgres
      register: pg_info

    - name: show database_names
      debug:
        msg: "{{ pg_info }}"

how do I filter the database names from that wall of text retrun via pg_info in order to use those in when clauses further on?

2

I get a notion that somehow the solution on approaching such a situation may be lying in this dict_lookup article

Just applying the very simple example to the very complex usecase still provides some headaces.

3

Looking at how I have done this I think this might work?

    - name: show database_names
      ansible.builtin.debug:
        msg: "{{ pg_info.databases.keys() }}"
1 Like
4

so since 4 days I am pulling out my hair while taking this a step further (and actually doing something useful with the output).

The relevant part of my playbook is:

    - name: Set a fact for the existing PostgreSQL databases
      ansible.builtin.set_fact:
        postgresql_dbs_existing: "{{ pg_info.databases.keys() }}"

    - name: debug 'postgresql_dbs_existing'
      ansible.builtin.debug:
        msg: "{{ postgresql_dbs_existing }}"

I my concrete case (stdout_callback: yaml), I am getting this:

[gwagner@rd1985 ad_hoc_playbooks]$ play pg_get_info.yml -l '*rd5792*'

PLAY [get postgres infos] *******************************************************************************************

TASK [get postgres infos] *******************************************************************************************
ok: [dvzsn-rd5792.local]

TASK [Set a fact for the existing PostgreSQL databases] *************************************************************
ok: [dvzsn-rd5792.local]

TASK [debug 'postgresql_dbs_existing'] ******************************************************************************
ok: [dvzsn-rd5792.local] =>
  msg:
  - postgres
  - template1
  - prodmin_coo_store_001
  - prodmin_im_coo_store_001
  - test_db

My goal actually is to execute subsequent tasks on all databases expect postgres, template0, template1.

So I need to use the list (it’s a list, right?) I am getting for postgresql_dbs_existing and apply some filtering to it.

The way I am trying to use is a when: clause.

    - name: list db names with loop
      ansible.builtin.debug:
        msg: "{{ db_name }}"
      loop: "{{ postgresql_dbs_existing }}"
      loop_control:
        loop_var: db_name
      where: "'postgres' not in 'db_name'"

ERROR! conflicting action statements: ansible.builtin.debug, where

The error appears to be in '/home/gwagner/repos/ad_hoc_playbooks/pg_get_info.yml': line 30, column 7, but may
be elsewhere in the file depending on the exact syntax problem.

The offending line appears to be:


    - name: list db names with loop
      ^ here
5

The error you pasted above is caused by the typo, where rather than when.

Also the in test is a check for an item in a list not a test for the value of a variable, so in the example you provided you would want to test for a negative string match:

    - name: list db names with loop
      ansible.builtin.debug:
        var: db_name
      loop: "{{ postgresql_dbs_existing }}"
      loop_control:
        loop_var: db_name
      when: db_name != "postgres"

You can use debug_type to check the type of the variable:

    - name: list db name types with loop
      ansible.ansible debug_type.debug:
        msg: >-
          db_name is a variable with the type:
          {{ db_name | ansible.builtin.type_debug }}
      loop: "{{ postgresql_dbs_existing }}"
      loop_control:
        loop_var: db_name

However I’d omit the names earlier with the set_fact so a when is not needed on the loop, something like this:

    - name: Set a fact for the existing PostgreSQL databases
      ansible.builtin.set_fact:
        postgresql_dbs_existing: >-
          pg_info.databases.keys() |
          reject('regex', '^postgres$') |
          regect('regex', '^template[0|1]$')

Rather than skipping the ones you don’t want in the loop.

1 Like
6

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.