community.postgresql.postgresql_privs add extra GRANT OPTIONS #668
Description
SUMMARY
Extra GRANT OPTIONS appears when adding privileges with community.postgresql.postgresql_privs
Works with ansible==8.7.0, after upgrading to newer versions (ansible==9.1.0) an error appears.
ISSUE TYPE
- Bug Report
COMPONENT NAME
community.postgresql.postgresql_privs ( strings 910, 921 )
ANSIBLE VERSION
ansible [core 2.16.3]
config file = /etc/ansible/ansible.cfg
configured module search path = ['/home/paliakou/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
ansible python module location = /home/paliakou/.local/lib/python3.10/site-packages/ansible
ansible collection location = /home/paliakou/.ansible/collections:/usr/share/ansible/collections
executable location = /home/paliakou/.local/bin/ansible
python version = 3.10.12 (main, Nov 20 2023, 15:14:05) [GCC 11.4.0] (/usr/bin/python3)
jinja version = 3.1.3
libyaml = True
COLLECTION VERSION
community.general 7.5.2
CONFIGURATION
CONFIG_FILE() = /etc/ansible/ansible.cfg
HOST_KEY_CHECKING(/etc/ansible/ansible.cfg) = False
OS / ENVIRONMENT
Ubuntu 22.04
target OS: Rocky Linux 8.9
Correct execution is expected with the right privileges assigned.
STEPS TO REPRODUCE
- name: Manage postgresql privileges
community.postgresql.postgresql_privs:
database: "{{ item['database'] }}"
privs: "{{ item['privs'] | default('') }}"
type: "{{ item['type'] | default('database') }}"
objs: "{{ item['objs'] | default(omit) }}"
schema: "{{ item['schema'] | default(omit) }}"
roles: "{{ item['roles'] }}"
target_roles: "{{ item['target_roles'] | default(omit) }}"
grant_option: "{{ item['grant_option'] | default(false) }}"
login_unix_socket: "{{ postgres_login_unix_socket }}"
port: "{{ postgres_port }}"
state: present
loop: "{{ postgres_privileges }}"
no_log: true
become_user: postgres
become: true
postgres_privileges:
- database: 'DB_1'
type: 'table'
objs: 'ALL_IN_SCHEMA'
privs: 'SELECT'
schema: 'public'
roles: 'test_user'
grant_option: true
- database: 'DB_2'
type: 'table'
objs: 'ALL_IN_SCHEMA'
privs: 'SELECT'
schema: 'public'
roles: 'test_user'
grant_option: true
- database: 'DB_3'
type: 'table'
objs: 'ALL_IN_SCHEMA'
privs: 'SELECT'
schema: 'public'
roles: 'test_user'
grant_option: true
EXPECTED RESULTS
ok: [pgsql] => (item={'database': 'DB_3', 'type': 'default_privs', 'objs': 'TABLES', 'privs': 'SELECT', 'schema': 'public', 'target_roles': 'test_user', 'roles': 'test_user', 'grant_option': True}) => {
"ansible_loop_var": "item",
"changed": false,
"invocation": {
"module_args": {
"ca_cert": null,
"connect_params": {},
"database": "DB_3",
"fail_on_role": true,
"grant_option": true,
"login_host": "",
"login_password": "",
"login_unix_socket": "/var/run/postgresql",
"login_user": "postgres",
"objs": "TABLES",
"password": "",
"port": 5432,
"privs": "SELECT",
"roles": "test_user",
"schema": "public",
"session_role": null,
"ssl_cert": null,
"ssl_key": null,
"ssl_mode": "prefer",
"state": "present",
"target_roles": "test_user",
"trust_input": true,
"type": "default_privs",
"usage_on_types": true
}
},
"item": {
"database": "DB_3",
"grant_option": true,
"objs": "TABLES",
"privs": "SELECT",
"roles": "test_user",
"schema": "public",
"target_roles": "test_user",
"type": "default_privs"
},
"queries": [
"ALTER DEFAULT PRIVILEGES FOR ROLE \"test_user\" IN SCHEMA \"public\" REVOKE ALL ON TABLES FROM \"test_user\";\nALTER DEFAULT PRIVILEGES FOR ROLE \"test_user\" IN SCHEMA \"public\" GRANT SELECT ON TABLES TO \"test_user\" WITH GRANT OPTION;\nALTER DEFAULT PRIVILEGES FOR ROLE \"test_user\" IN SCHEMA \"public\" GRANT USAGE ON TYPES TO \"test_user\" WITH GRANT OPTION;"
]
}
ACTUAL RESULTS
... WITH GRANT OPTION; WITH GRANT OPTION; ...
The full traceback is:
File "/tmp/ansible_community.postgresql.postgresql_privs_payload_bdfe1c0h/ansible_community.postgresql.postgresql_privs_payload.zip/ansible_collections/community/postgresql/plugins/modules/postgresql_privs.py", line 505, in execute
File "/usr/lib64/python3.6/site-packages/psycopg2/extras.py", line 146, in execute
return super().execute(query, vars)
failed: [pgsql] (item={'database': 'DB_3', 'type': 'default_privs', 'objs': 'TABLES', 'privs': 'SELECT', 'schema': 'public', 'target_roles': 'DB_3', 'roles': 'test_user', 'grant_option': True}) => {
"ansible_loop_var": "item",
"changed": false,
"invocation": {
"module_args": {
"ca_cert": null,
"connect_params": {},
"database": "DB_3",
"fail_on_role": true,
"grant_option": true,
"login_host": "",
"login_password": "",
"login_unix_socket": "/var/run/postgresql",
"login_user": "postgres",
"objs": "TABLES",
"password": "",
"port": 5432,
"privs": "SELECT",
"roles": "test_user",
"schema": "public",
"session_role": null,
"ssl_cert": null,
"ssl_key": null,
"ssl_mode": "prefer",
"state": "present",
"target_roles": "DB_3",
"trust_input": true,
"type": "default_privs"
}
},
"item": {
"database": "DB_3",
"grant_option": true,
"objs": "TABLES",
"privs": "SELECT",
"roles": "test_user",
"schema": "public",
"target_roles": "DB_3",
"type": "default_privs"
},
"msg": "Cannot execute SQL 'ALTER DEFAULT PRIVILEGES FOR ROLE \"DB_3\" IN SCHEMA \"public\" REVOKE ALL ON TABLES FROM \"test_user\";\nALTER DEFAULT PRIVILEGES FOR ROLE \"DB_3\" IN SCHEMA \"public\" GRANT SELECT ON TABLES TO \"test_user\" WITH GRANT OPTION; WITH GRANT OPTION;': syntax error at or near \"GRANT\"\nLINE 2: ... ON TABLES TO \"test_user\" WITH GRANT OPTION; WITH GRANT OPTI...\n ^\n"
}