How to grant a user pg_monitor privileges using this role?

[pandafy]

I have already tried configuring the postgresql_user_privileges variable as following"

postgresql_user_privileges:
  - name: "my_user"
    db: "postgres"
    priv: "pg_monitor"

My PostgreSQL version is 12.13

[pulse-mind]

Hi, did you find the solution ?

[pulse-mind]

If we look at the documentation of ansible : https://docs.ansible.com/ansible/latest/collections/community/postgresql/postgresql_privs_module.html
There is a similar example :

# Note that group role memberships apply cluster-wide and therefore are not
# restricted to database "library" here.
- name: GRANT librarian, reader TO alice, bob WITH ADMIN OPTION
  community.postgresql.postgresql_privs:
    db: library
    type: group
    objs: librarian,reader
    roles: alice,bob
    admin_option: true

In our case we need to do :

GRANT pg_monitor TO my_user;

So objs should be pg_monitor

But I do not see this in ANXS/postgresql module...

[pulse-mind]

I did not find but you can do in a task

- name: GRANT pg_monitor TO my_user;
  become: yes
  become_user: 'postgres'
  community.postgresql.postgresql_query:
    db: postgres
    login_user: 'postgres'
    query: GRANT pg_monitor TO my_user
  tags: grant_ my_user

[Jamal-B]

You can try this:

postgresql_user_privileges:
  - roles: my_user
    database: postgres
    type: group
    objs: pg_monitor
    admin_option: false

[Jamal-B]

And/or with community.postgresql.postgresql_privs:

- name: Grant pg_monitor to my_user (without admin option)
  community.postgresql.postgresql_privs:
    roles: my_user
    database: postgres
    type: group
    objs: pg_monitor
    admin_option: false

[pulse-mind]

I tried this one but it does not work.

Cannot execute SQL 'GRANT \"pg_monitor\",\" pg_read_all_stats\" TO \"my_user\";': role \" pg_read_all_stats\" does not exist\n"

It is really strange. I tried some other solution using become_user : postgres, login_user: postgres, database: postgres... But nothing is working.
The only solution was to use the query

[Jamal-B]

You must provide a comma separated list with no spaces after the commas:

objs: pg_monitor,pg_read_all_stats

But, indeed, there is no mention of this subtlety in the documentation.

[pandafy]

I ended up using a task for achieve this:

- name: Add myuser to "pg_monitor" group
  community.postgresql.postgresql_membership:
    group: pg_monitor
    target_role: myuser
    state: present
  become: true
  become_user: postgres