How to resolve Insufficient Privilege error during PostgreSQL user creation in Ansible
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
During the execution of an Ansible playbook, a task failure occurs during the "Create application DB user" step. The error message indicates an "InsufficientPrivilege" exception from the psycopg2 module. This prevents further execution of the playbook, hindering the deployment process.
Environment
Jira instance that uses a PostgreSQL database is hosted on Amazon Web Services (AWS).
Diagnosis
You can find similar log entries or error messages in the output generated when the Ansible automation tool executes a playbook or script.
1
TASK [database_init : Create application DB user] ****************************** Saturday ** July *** 09:02:00 +0000 (0:00:00.036) 0:01:08.213 ********* 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 \"/root/.ansible/tmp/ansible-tmp-1689411720.8955069-4822-186398384716096/AnsiballZ_postgresql_user.py\", line 107, in <module>\n _ansiballz_main()\n File \"/root/.ansible/tmp/ansible-tmp-1689411720.8955069-4822-186398384716096/AnsiballZ_postgresql_user.py\", line 99, in _ansiballz_main\n invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\n File \"/root/.ansible/tmp/ansible-tmp-1689411720.8955069-4822-186398384716096/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/lib64/python3.8/runpy.py\", line 207, in run_module\n return _run_module_code(code, init_globals, run_name, mod_spec)\n File \"/usr/lib64/python3.8/runpy.py\", line 97, in _run_module_code\n _run_code(code, mod_globals, init_globals,\n File \"/usr/lib64/python3.8/runpy.py\", line 87, in _run_code\n exec(code, run_globals)\n File \"/tmp/ansible_community.postgresql.postgresql_user_payload_wkp3_drr/ansible_community.postgresql.postgresql_user_payload.zip/ansible_collections/community/postgresql/plugins/modules/postgresql_user.py\", line 1050, in <module>\n File \"/tmp/ansible_community.postgresql.postgresql_user_payload_wkp3_drr/ansible_community.postgresql.postgresql_user_payload.zip/ansible_collections/community/postgresql/plugins/modules/postgresql_user.py\", line 989, in main\n File \"/tmp/ansible_community.postgresql.postgresql_user_payload_wkp3_drr/ansible_community.postgresql.postgresql_user_payload.zip/ansible_collections/community/postgresql/plugins/modules/postgresql_user.py\", line 552, in user_alter\n File \"/root/.local/share/virtualenvs/dc-deployments-automation-gdgnP2zs/lib64/python3.8/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} Cause
The PostgreSQL user executing the Ansible task lacks the necessary permissions to perform the operation. Specifically, the user lacks the CREATEDB and CREATEROLE privileges, which are required to create new databases and roles (users) in PostgreSQL, respectively.
Solution
Connect to your PostgreSQL database.
Confirm the current user's privileges using the following SQL query, replacing
'your_role_name'with the relevant PostgreSQL username:1SELECT rolname, rolsuper, rolcreaterole, rolcreatedb FROM pg_roles WHERE rolname = 'your_role_name';If the
rolcreatedborrolcreaterolevalues arefalse, the user lacks the necessary privileges to create databases or roles.If necessary, grant the
CREATEDBandCREATEROLEprivileges to the user. Replace'your_role_name'With the relevant PostgreSQL username:1ALTER USER your_role_name CREATEDB CREATEROLE;Re-run the Ansible playbook. The "Create application DB user" task should now complete successfully.
Was this helpful?