postgresql_privs module mistakes a procedure for a function

[hkraal]

SUMMARY

The postgresql_privs module tries to manipulate the privileges on a procedure when trying to set privileges on objects of type function. Procedures have been added in PostgreSQL 11

The error is reproducible in a clean docker container with various Ansible versions.

ISSUE TYPE

• Bug Report

COMPONENT NAME

postgresql_privs

ANSIBLE VERSION

ansible 2.8.15
  config file = None
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.5/dist-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 3.5.3 (default, Jul  9 2020, 13:00:10) [GCC 6.3.0 20170516]

CONFIGURATION

Tests have been done on a clean ansible installation.

OS / ENVIRONMENT

OS: Debian GNU/Linux 9.13 (stretch)
Ansible 2.8.15 / 2.10 / 2.11.0.dev0 have been tried
Postgres: 11
psycopg2: debian package (2.6.2-1) and pip package (psycopg2==2.8.6)

STEPS TO REPRODUCE

Start and login a docker PostgreSQL container

docker run -d -e POSTGRES_HOST_AUTH_METHOD=trust postgres:11
docker exec -it <container> bash

Install Ansible and prepare database/user for testing inside container

apt update
apt install python3-pip libpq-dev sudo -y
pip3 install ansible==2.8.15 psycopg2
ansible -b --become-user=postgres -m postgresql_user -a "name=test" localhost
ansible -b --become-user=postgres -m postgresql_db -a "name=test_db owner=test" localhost
ansible -b --become-user=postgres -m postgresql_privs -a "database=test_db privs=ALL roles=test objs=ALL_IN_SCHEMA type=function" localhost

Create a stored procedure in the database test, still inside the container.

sudo -u postgres psql
postgres=# \c test_db
test_db=# SET role 'test';
SET
test2_db=> create or replace procedure transfer(
   sender int,
   receiver int, 
   amount dec
)
language plpgsql    
as $$
begin
    -- subtracting the amount from the sender's account 
    update accounts 
    set balance = balance - amount 
    where id = sender;

    -- adding the amount to the receiver's account
    update accounts 
    set balance = balance + amount 
    where id = receiver;

    commit;
end;$$
;
test_db=> \q

Run the last ansible command again to trigger the error.

ansible -b --become-user=postgres -m postgresql_privs -a "database=test_db privs=ALL roles=test objs=ALL_IN_SCHEMA type=function" localhost

EXPECTED RESULTS

I would expect no changes/actions to be taken as I've created a stored procedure, not a function.

ACTUAL RESULTS

The Ansible command fails on the fact that the stored procedure is not a function.

root@0d5d530f0306:~# ansible -b --become-user=postgres -m postgresql_privs -a "database=test2_db privs=ALL roles=test objs=ALL_IN_SCHEMA type=function" localhost
[WARNING]: No inventory was parsed, only implicit localhost is available

An exception occurred during task execution. To see the full traceback, use -vvv. The error was: AttributeError: 'WrongObjectType' object has no attribute 'message'
localhost | FAILED! => {
    "changed": false,
    "module_stderr": "Traceback (most recent call last):\n  File \"/tmp/ansible_postgresql_privs_payload_jvrqrcnz/__main__.py\", line 1030, in main\n  File \"/tmp/ansible_postgresql_privs_payload_jvrqrcnz/__main__.py\", line 744, in manipulate_privs\npsycopg2.errors.WrongObjectType: public.transfer(integer, integer, numeric) is not a function\n\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n  File \"/var/tmp/ansible-tmp-1601302060.0145266-4752-221313839588631/AnsiballZ_postgresql_privs.py\", line 114, in <module>\n    _ansiballz_main()\n  File \"/var/tmp/ansible-tmp-1601302060.0145266-4752-221313839588631/AnsiballZ_postgresql_privs.py\", line 106, in _ansiballz_main\n    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\n  File \"/var/tmp/ansible-tmp-1601302060.0145266-4752-221313839588631/AnsiballZ_postgresql_privs.py\", line 49, in invoke_module\n    imp.load_module('__main__', mod, module, MOD_DESC)\n  File \"/usr/lib/python3.5/imp.py\", line 234, in load_module\n    return load_source(name, filename, file)\n  File \"/usr/lib/python3.5/imp.py\", line 170, in load_source\n    module = _exec(spec, sys.modules[name])\n  File \"<frozen importlib._bootstrap>\", line 626, in _exec\n  File \"<frozen importlib._bootstrap_external>\", line 673, in exec_module\n  File \"<frozen importlib._bootstrap>\", line 222, in _call_with_frames_removed\n  File \"/tmp/ansible_postgresql_privs_payload_jvrqrcnz/__main__.py\", line 1049, in <module>\n  File \"/tmp/ansible_postgresql_privs_payload_jvrqrcnz/__main__.py\", line 1039, in main\nAttributeError: 'WrongObjectType' object has no attribute 'message'\n",
    "module_stdout": "",
    "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error",
    "rc": 1
}

[ansibullbot]

Files identified in the description:

• plugins/modules/database/postgresql/postgresql_privs.py

If these files are inaccurate, please update the component name section of the description or use the !component bot command.

click here for bot help

[ansibullbot]

cc @Andersson007 @Dorn- @andytom @b6d @jbscalia @kostiantyn-nemchenko @matburt @nerzhul @sebasmannem @tcraxs
click here for bot help

[Andersson007]

@hkraal thanks for reporting this! Could you please run the playbook with 2.9 and '-vvv' option and provide the full traceback and return dictionary?

[Andersson007]

cc @ilicmilan just for information, i have no idea why the bot didn't mention you (will see later).

[hkraal]

@Andersson007 here is the requested output, if you need anything else let me know!

Ansible version

# ansible --version
/usr/local/lib/python3.5/dist-packages/ansible/parsing/vault/__init__.py:44: CryptographyDeprecationWarning: Python 3.5 support will be dropped in the next release ofcryptography. Please upgrade your Python.
  from cryptography.exceptions import InvalidSignature
ansible 2.9.13
  config file = None
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/local/lib/python3.5/dist-packages/ansible
  executable location = /usr/local/bin/ansible
  python version = 3.5.3 (default, Jul  9 2020, 13:00:10) [GCC 6.3.0 20170516]

Traceback

The full traceback is:
Traceback (most recent call last):
  File "/tmp/ansible_postgresql_privs_payload_bw70o0om/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py", line 1046, in main
  File "/tmp/ansible_postgresql_privs_payload_bw70o0om/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py", line 750, in manipulate_privs
psycopg2.errors.WrongObjectType: public.transfer(integer, integer, numeric) is not a function


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/var/tmp/ansible-tmp-1601366502.8659031-4447-49399226430370/AnsiballZ_postgresql_privs.py", line 102, in <module>
    _ansiballz_main()
  File "/var/tmp/ansible-tmp-1601366502.8659031-4447-49399226430370/AnsiballZ_postgresql_privs.py", line 94, in _ansiballz_main
    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)
  File "/var/tmp/ansible-tmp-1601366502.8659031-4447-49399226430370/AnsiballZ_postgresql_privs.py", line 40, in invoke_module
    runpy.run_module(mod_name='ansible.modules.database.postgresql.postgresql_privs', init_globals=None, run_name='__main__', alter_sys=True)
  File "/usr/lib/python3.5/runpy.py", line 205, in run_module
    return _run_module_code(code, init_globals, run_name, mod_spec)
  File "/usr/lib/python3.5/runpy.py", line 96, in _run_module_code
    mod_name, mod_spec, pkg_name, script_name)
  File "/usr/lib/python3.5/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/tmp/ansible_postgresql_privs_payload_bw70o0om/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py", line 1065, in <module>
  File "/tmp/ansible_postgresql_privs_payload_bw70o0om/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py", line 1055, in main
AttributeError: 'WrongObjectType' object has no attribute 'message'
localhost | FAILED! => {
    "changed": false,
    "module_stderr": "Traceback (most recent call last):\n  File \"/tmp/ansible_postgresql_privs_payload_bw70o0om/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py\", line 1046, in main\n  File \"/tmp/ansible_postgresql_privs_payload_bw70o0om/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py\", line 750, in manipulate_privs\npsycopg2.errors.WrongObjectType: public.transfer(integer, integer, numeric) is not a function\n\n\nDuring handling of the above exception, another exception occurred:\n\nTraceback (most recent call last):\n  File \"/var/tmp/ansible-tmp-1601366502.8659031-4447-49399226430370/AnsiballZ_postgresql_privs.py\", line 102, in <module>\n    _ansiballz_main()\n  File \"/var/tmp/ansible-tmp-1601366502.8659031-4447-49399226430370/AnsiballZ_postgresql_privs.py\", line 94, in _ansiballz_main\n    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\n  File \"/var/tmp/ansible-tmp-1601366502.8659031-4447-49399226430370/AnsiballZ_postgresql_privs.py\", line 40, in invoke_module\n    runpy.run_module(mod_name='ansible.modules.database.postgresql.postgresql_privs', init_globals=None, run_name='__main__', alter_sys=True)\n  File \"/usr/lib/python3.5/runpy.py\", line 205, in run_module\n    return _run_module_code(code, init_globals, run_name, mod_spec)\n  File \"/usr/lib/python3.5/runpy.py\", line 96, in _run_module_code\n    mod_name, mod_spec, pkg_name, script_name)\n  File \"/usr/lib/python3.5/runpy.py\", line 85, in _run_code\n    exec(code, run_globals)\n  File \"/tmp/ansible_postgresql_privs_payload_bw70o0om/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py\", line 1065, in <module>\n  File \"/tmp/ansible_postgresql_privs_payload_bw70o0om/ansible_postgresql_privs_payload.zip/ansible/modules/database/postgresql/postgresql_privs.py\", line 1055, in main\nAttributeError: 'WrongObjectType' object has no attribute 'message'\n",
    "module_stdout": "",
    "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error",
    "rc": 1
}

[Andersson007]

@hkraal thanks!

[Andersson007]

bot_status

[ansibullbot]

Components

plugins/modules/database/postgresql/postgresql_privs.py
support: core
maintainers: Andersson007 Dorn- andytom b6d jbscalia kostiantyn-nemchenko matburt nerzhul sebasmannem tcraxs

Metadata

waiting_on: maintainer
needs_info: False

click here for bot help

[Andersson007]

i'm fixing the bug

[Andersson007]

the PR #996 ready for review

[hkraal]

@Andersson007 awesome to see how fast you picked up, Большое спасибо!

[Andersson007]

@hkraal you and the community are always welcome! Пожалуйста :)

[Andersson007]

@hkraal the fixed version has just been merged, thanks for reporting the issue and thanks the folks for reviewing!
You can download and use this after community.general collection 1.2.0 is released (in a couple of days). It'll be available on https://galaxy.ansible.com/community/general and can be used with 2.9 and 2.10, see https://docs.ansible.com/ansible/latest/user_guide/collections_using.html
If you notice anything wrong, would be greatful for feedback!

[Andersson007]

Ah, yes, also we can wait until Ansible 2.10 next patch release, this fix will be included.
And i created a backport to 2.9 ansible/ansible#72014, hope it'll be merged ASAP before the next Ansible 2.9 patch release

[hkraal]

@Andersson007 We are currently running on 2.8, any chance on a backport to that version as well or should we focus on moving to 2.9 on our earliest convenience?

[felixfontein]

@hkraal 2.8 only receives security fixes anymore (https://docs.ansible.com/ansible/devel/reference_appendices/release_and_maintenance.html#ansible-release-cycle)