community.postgresql
community.postgresql copied to clipboard
Published
20 hours ago •
ansible-collections
ansible-collections
postgresql_user: Cannot change user password with session_role in Azure Database.
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
@t-ymwk hello, thanks for raising the issue! I'll put the help_wanted label hoping someone can take a deeper look
