postgresql_user: Cannot change user password with session_role in Azure Database.

[t-ymwk]

SUMMARY

Administrative privileges in Azure Database for PostgreSQL are to belong to the azure_pg_admin role.
The login user should not have administrative privileges,
Specify the role with azure_pg_admin as sesson_role,
Password change will result in an error.

Because Azure Database does not have access to pg_authid,
If it is rolled back, "SET ROLE" will be disabled,
If I change the password with "ALTER USER", it will be "permission denied".

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_user

ANSIBLE VERSION

ansible [core 2.15.0]
ansible 8.0.0
python 3.9.5
jinja 3.1.2
psycopg2-binary 2.9.6

COLLECTION VERSION

$ ansible-galaxy collection list community.postgresql
community.postgresql 2.4.1

CONFIGURATION

$ ansible-config dump --only-changed
CONFIG_FILE() = None

OS / ENVIRONMENT

Ubuntu 20.04.6 LTS

STEPS TO REPRODUCE

---
- hosts: localhost
  gather_facts: no

  vars:
    host: "{{ lookup('env','PG_HOST') }}"
    port: "{{ lookup('env','PG_PORT',default='5432') }}"
    user: "{{ lookup('env','PG_USR') }}"
    password: "{{ lookup('env','PG_PSW') }}"
    users:
      - name: user1
        role_attr_flags: NOLOGIN,CREATEDB,CREATEROLE
        groups:
          - azure_pg_admin
      - name: user2
        role_attr_flags: NOINHERIT,NOCREATEDB,NOCREATEROLE
        password: password
        groups:
          - user1

  tasks:
    - name: Create roles
      community.postgresql.postgresql_user:
        login_host: "{{ host }}"
        login_password: "{{ password }}"
        login_user: "{{ user }}"
        port: "{{ port }}"
        db: postgres
        name: "{{ item.name }}"
        password: "{{ item.password | default(omit) }}"
        no_password_changes: yes
        role_attr_flags: "{{ item.role_attr_flags | default(omit) }}"
        state: present
        ssl_mode: require
        ca_cert: cert/DigiCertGlobalRootCA.crt.pem
      loop: "{{ users }}"

    - name: Grant role
      community.postgresql.postgresql_membership:
        login_host: "{{ host }}"
        login_password: "{{ password }}"
        login_user: "{{ user }}"
        port: "{{ port }}"
        db: postgres
        groups: "{{ item.groups }}"
        target_role: "{{ item.name }}"
        state: present
        ssl_mode: require
        ca_cert: cert/DigiCertGlobalRootCA.crt.pem
      when: item.groups is defined
      loop: "{{ users }}"

    - name: Create new user
      community.postgresql.postgresql_user:
        login_host: "{{ host }}"
        login_password: password
        login_user: user2
        session_role: user1
        port: "{{ port }}"
        db: postgres
        name: user3
        password: password
        no_password_changes: yes
        state: present
        ssl_mode: require
        ca_cert: cert/DigiCertGlobalRootCA.crt.pem

    - name: Change password
      community.postgresql.postgresql_user:
        login_host: "{{ host }}"
        login_password: password
        login_user: user2
        session_role: user1
        port: "{{ port }}"
        db: postgres
        name: user3
        password: passw0rd
        no_password_changes: no
        state: present
        ssl_mode: require
        ca_cert: cert/DigiCertGlobalRootCA.crt.pem

EXPECTED RESULTS

Successful password change for user3

ACTUAL RESULTS

PLAY [localhost] *******************************************************************************************************

TASK [Create roles] ****************************************************************************************************
changed: [localhost] => (item={'name': 'user1', 'role_attr_flags': 'NOLOGIN,CREATEDB,CREATEROLE', 'groups': ['azure_pg_admin']})
changed: [localhost] => (item={'name': 'user2', 'role_attr_flags': 'NOINHERIT,NOCREATEDB,NOCREATEROLE', 'password': 'password', 'groups': ['user1']})

TASK [Grant role] ******************************************************************************************************
changed: [localhost] => (item={'name': 'user1', 'role_attr_flags': 'NOLOGIN,CREATEDB,CREATEROLE', 'groups': ['azure_pg_admin']})
changed: [localhost] => (item={'name': 'user2', 'role_attr_flags': 'NOINHERIT,NOCREATEDB,NOCREATEROLE', 'password': 'password', 'groups': ['user1']})

TASK [Create new user] *************************************************************************************************
changed: [localhost]

TASK [Change password] *************************************************************************************************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: psycopg2.errors.InsufficientPrivilege: permission denied
fatal: [localhost]: FAILED! => {"changed": false, "module_stderr": "Traceback (most recent call last):\n  File \"/home/takuya/.ansible/tmp/ansible-tmp-1686424332.522325-10590-215175973510025/AnsiballZ_postgresql_user.py\", line 107, in <module>\n    _ansiballz_main()\n  File \"/home/takuya/.ansible/tmp/ansible-tmp-1686424332.522325-10590-215175973510025/AnsiballZ_postgresql_user.py\", line 99, in _ansiballz_main\n    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\n  File \"/home/takuya/.ansible/tmp/ansible-tmp-1686424332.522325-10590-215175973510025/AnsiballZ_postgresql_user.py\", line 47, in invoke_module\n    runpy.run_module(mod_name='ansible_collections.community.postgresql.plugins.modules.postgresql_user', init_globals=dict(_module_fqn='ansible_collections.community.postgresql.plugins.modules.postgresql_user', _modlib_path=modlib_path),\n  File \"/usr/lib/python3.9/runpy.py\", line 210, in run_module\n    return _run_module_code(code, init_globals, run_name, mod_spec)\n  File \"/usr/lib/python3.9/runpy.py\", line 97, in _run_module_code\n    _run_code(code, mod_globals, init_globals,\n  File \"/usr/lib/python3.9/runpy.py\", line 87, in _run_code\n    exec(code, run_globals)\n  File \"/tmp/ansible_community.postgresql.postgresql_user_payload_pcfv1cw0/ansible_community.postgresql.postgresql_user_payload.zip/ansible_collections/community/postgresql/plugins/modules/postgresql_user.py\", line 1082, in <module>\n  File \"/tmp/ansible_community.postgresql.postgresql_user_payload_pcfv1cw0/ansible_community.postgresql.postgresql_user_payload.zip/ansible_collections/community/postgresql/plugins/modules/postgresql_user.py\", line 1013, in main\n  File \"/tmp/ansible_community.postgresql.postgresql_user_payload_pcfv1cw0/ansible_community.postgresql.postgresql_user_payload.zip/ansible_collections/community/postgresql/plugins/modules/postgresql_user.py\", line 569, in user_alter\n  File \"/home/takuya/ansible8.0/lib/python3.9/site-packages/psycopg2/extras.py\", line 146, in execute\n    return super().execute(query, vars)\npsycopg2.errors.InsufficientPrivilege: permission denied\n\n", "module_stdout": "", "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", "rc": 1}

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

--- Create new user
[2023-06-10 19:12:13 UTC][21936][6484cb0c.55b0][3][9/3077][0][user2][postgres] LOG: connection authenticated: identity="user2" method=md5 (/datadrive/pg/data/pg_hba.conf:25)
[2023-06-10 19:12:13 UTC][21936][6484cb0c.55b0][5][9/3077][0][user2][postgres] LOG: connection authorized: user=user2 database=postgres SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)
[2023-06-10 19:12:13 UTC][21936][6484cb0c.55b0][7][9/3078][0][user2][postgres] LOG: statement: BEGIN
[2023-06-10 19:12:13 UTC][21936][6484cb0c.55b0][9][9/3078][0][user2][postgres] LOG: statement: SET ROLE "user1"
[2023-06-10 19:12:13 UTC][21936][6484cb0c.55b0][11][9/3078][0][user2][postgres] LOG: statement: SELECT rolname FROM pg_roles WHERE rolname='user3'
[2023-06-10 19:12:13 UTC][21936][6484cb0c.55b0][13][9/3078][0][user2][postgres] LOG: statement: CREATE USER "user3" WITH ENCRYPTED PASSWORD 'password'
[2023-06-10 19:12:13 UTC][21936][6484cb0c.55b0][15][9/3078][6180][user2][postgres] LOG: statement: COMMIT
--- Change password
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][3][9/3079][0][user2][postgres] LOG: connection authenticated: identity="user2" method=md5 (/datadrive/pg/data/pg_hba.conf:25)
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][5][9/3079][0][user2][postgres] LOG: connection authorized: user=user2 database=postgres SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][7][9/3080][0][user2][postgres] LOG: statement: BEGIN
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][9][9/3080][0][user2][postgres] LOG: statement: SET ROLE "user1"
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][11][9/3080][0][user2][postgres] LOG: statement: SELECT rolname FROM pg_roles WHERE rolname='user3'
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][13][9/3080][0][user2][postgres] LOG: statement: SELECT * FROM pg_authid where rolname='user3'
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][15][9/3080][0][user2][postgres] ERROR: permission denied for table pg_authid
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][18][9/0][0][user2][postgres] LOG: statement: ROLLBACK
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][20][9/3081][0][user2][postgres] LOG: statement: BEGIN
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][22][9/3081][0][user2][postgres] LOG: statement: SELECT * FROM pg_roles where rolname='user3'
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][24][9/3081][0][user2][postgres] LOG: statement: ALTER USER "user3" WITH ENCRYPTED PASSWORD 'passw0rd'
[2023-06-10 19:12:13 UTC][21937][6484cb0d.55b1][26][9/3081][0][user2][postgres] ERROR: permission denied

[Andersson007]

@t-ymwk hello, thanks for raising the issue! I'll put the help_wanted label hoping someone can take a deeper look