postgresql_user module doesn't close db connection

[boosterKRD]

SUMMARY
There is no closing connection to DB in postgresql_user.py. Because of this, when using loops/with_items and etc, the number of connections is constantly growing until the loop ends or it becomes more than the DB connection-limit parameter (if there are more elements in the array, then users connection-limit )

ISSUE TYPE

• Bug Report

COMPONENT NAME
postgresql_user

EXPECTED RESULTS

After each iteration of the loop connection to DB must be closed as it is done in other modules (postgresql_membership, postgresql_query).

Two lines have to be added to

community.postgresql/plugins/modules/postgresql_user.py

Line 1071 in 04c04c4

:
cursor.close()
db_connection.close()

STEPS TO REPRODUCE

1. Create a list with 10 elements
2. run task and use select count(*) from pg_stat_activity where usename=your_user_db. "Pause: 5" will allow you to easily see how the number of connections grows and the closure of all connections occurs only after the end of the task

db_users:

• { username: 'test1', actions: 'LOGIN,INHERIT'}
  ...
  ...

• { username: 'test10', actions: 'LOGIN,INHERIT'}

• name: Create users
  postgresql_user:
  login_host: "{{ hostvars[inventory_hostname]['ansible_default_ipv4']['address'] }}"
  login_user: 'your_user_db'
  db: 'postgres'
  login_password: 'your_db_pass'
  name: '{{ item.username }}'
  password: 'md56771d07fa780767a22d0092372bd41ab'
  role_attr_flags: '{{ item.actions }}'
  conn_limit: '{{ item.conn_limit }}'
  state: 'present'
  with_list: '{{ db_users }}'
  loop_control:
  pause: 5
  become_user: 'postgres'

[Andersson007]

@boosterKRD hi, thanks for reporting the issue!
Would you like to add the lines yourself? Use this Quick start guide to get started.
What do you think? If no time/wish, please let us know.
Waiting for your feedback

[boosterKRD]

@Andersson007 I implemented changes locally and tested-> all works fine.
I am just a DBA, not a programmer, but I will try to create the PR (but I can't give you 100%, give me one week)
Ultimately there is another one strange place in the code that could be changed (I will write more about it in PR)

[Andersson007]

@boosterKRD brilliant, thanks! Just a fyi: you'll see integration/unit tests mentioned in the guide. I don't think you need to add any tests in this particular case.

1. Add the lines you mentioned in the description
2. Add a changelog fragment so that users see the fix in collection's changelog.

[Andersson007]

closed via #436