postgresql_membership doesn't recognize ADMIN, SET and INHERIT options

[HauserV]

SUMMARY

Make it possible to control the ADMIN, SET and INHERIT options when GRANTing roles.

ISSUE TYPE

• Feature Idea

COMPONENT NAME

community.postgresql.postgresql_membership

ADDITIONAL INFORMATION

When a non-superuser creates a role in PostgreSQL 16, it is automatically granted the new role only with ADMIN option set. However, that's not enough to create objects owned by the user. To quote the documentation:

To create an object owned by another role or give ownership of an existing object to another role, you must have the ability to SET ROLE to that role; otherwise, commands such as ALTER ... OWNER TO or CREATE DATABASE ... OWNER will fail.

In earlier versions of PostgreSQL, I simply could use the community.postgresql.postgresql_membership to grant the new role to the non-superuser, but that doesn't work anymore because the module doesn't do anything now because the grant is there (albeit not with the options I need).

In my scenario, I cannot use a superuser to do this (superuser wouldn't receive this automatic grant) because I'm using a managed database as a service.

- name: Demo
  hosts: integration
  gather_facts: true
  tasks:
    - name: Create role
      community.postgresql.postgresql_user:
        name: ansible_new_role
        password: dummy123!
        login_host: example.com
        login_db: postgres
        login_user: dbadmin
        login_password: password
        login_port: 5432
    - name: Grant new role to the admin user
      community.postgresql.postgresql_membership:
        group: ansible_new_role
        target_role: dbadmin
        login_host: example.com
        login_db: postgres
        login_user: dbadmin
        login_password: password
        login_port: 5432
    - name: Create database owned by the new role
      community.postgresql.postgresql_db:
        name: ansible_new_database
        owner: ansible_new_role
        login_host: example.com
        login_user: dbadmin
        login_password: password
        login_port: 5432

$ ansible-playbook play.yml -v -i inventory.yml 
Using /etc/ansible/ansible.cfg as config file

PLAY [Demo] **********************************************************************************************************************************************************************

TASK [Gathering Facts] ***********************************************************************************************************************************************************
[WARNING]: Platform linux on host integration is using the discovered Python interpreter at /usr/bin/python3.6, but future installation of another Python interpreter could
change the meaning of that path. See https://docs.ansible.com/ansible-core/2.16/reference_appendices/interpreter_discovery.html for more information.
ok: [integration]

TASK [Create role] ***************************************************************************************************************************************************************
changed: [integration] => changed=true 
  queries:
  - 'ALTER USER "ansible_new_role" WITH ENCRYPTED PASSWORD %(password)s '
  user: ansible_new_role

TASK [Grant new role to the admin user] ******************************************************************************************************************************************
ok: [integration] => changed=false 
  granted:
    ansible_new_role: []
  groups:
  - ansible_new_role
  queries: []
  state: present
  target_roles:
  - dbadmin

TASK [Create database owned by the new role] *************************************************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: psycopg2.errors.InsufficientPrivilege: must be able to SET ROLE "ansible_new_role"
fatal: [integration]: FAILED! => changed=false 
  msg: |-
    Database query failed: must be able to SET ROLE "ansible_new_role"

PLAY RECAP ***********************************************************************************************************************************************************************
integration                : ok=3    changed=1    unreachable=0    failed=1    skipped=0    rescued=0    ignored=0   

It seems to me that this is partly a bug report because the module would otherwise run a regular GRANT statement which defaults to SET and INHERIT options enabled. In my case, however, it doesn't do anything because it sees the implicit GRANT with the ADMIN option only. There's the obvious workaround to use the community.postgresql.postgresql_query to apply the GRANT no matter what, but that's not clean.