pgbouncer user doesn't exist error when trying to deploy from command line

[biglolka]

Hello @vitabaks,

I'm trying to deploy the cluster with the ansible script, but I keep getting the following error:

`fatal: [172.22.134.241]: FAILED! => {"changed": true, "cmd": ["https://accionvegana.org/accio/0ITbvNmLiVHa0l2Z6MHc0/usr/lib/postgresql/16/bin/psql", "-p", "5432", "-U", "postgres", "-d", "postgres", "-tAXc", "CREATE FUNCTION user_search(uname TEXT) RETURNS TABLE (usename name, passwd text) AS $$ SELECT usename, passwd FROM pg_shadow WHERE usename=$1; $$ LANGUAGE sql SECURITY DEFINER; REVOKE ALL ON FUNCTION user_search(uname TEXT) FROM public; GRANT EXECUTE ON FUNCTION user_search(uname TEXT) TO pgbouncer"], "delta": "0:00:00.011840", "end": "2024-10-22 13:16:06.143323", "msg": "non-zero return code", "rc": 1, "start": "2024-10-22 13:16:06.131483", "stderr": "ERROR:  role \"pgbouncer\" does not exist", "stderr_lines": ["ERROR:  role \"pgbouncer\" does not exist"], "stdout": "CREATE FUNCTION\nREVOKE", "stdout_lines": ["CREATE FUNCTION", "REVOKE"]}`

Thanks in advance for your response!

[vitabaks]

Hi @biglolka

Which version of postgresql_cluster are you using?

Make sure that you have a pgbouncer user defined in the postgresql_users variable as here https://github.com/vitabaks/postgresql_cluster/blob/2.0.0/automation/vars/main.yml#L186

[biglolka]

Hi @vitabaks,

Thanks for the heads-up, you diverted my attention to the right direction:
According to the comment the password for the user will be generated if not defined explicitly in the yaml:
https://github.com/vitabaks/postgresql_cluster/blob/23d021967cd38dd11e84692e07d381d1d90bb381/automation/vars/main.yml#L353
In fact that caused the problem, when setting the password in the yaml for "pgbouncer"user it works OK.

[vitabaks]

there should be no problem for automatic password generation if pgbouncer_auth_password is not set, what error did you get?

[biglolka]

If I don't set pgbouncer password (but postgres and replicator) in main.yaml I get this error at this stage:

If I don't set postgres user password in main.yaml it fails at this stage:

If I set postgres password in main.yaml but I don't set replicator password it's hanging here:

so for sure generating the passwords in case they are not explicitly set in the main.yaml doesn't work.

[vitabaks]

At the same time, did you have the postgresql_users variable filled in? Could you attach the full Ansible log?

if I understood correctly, the problem with the password for pgbouncer (lack of autogeneration) is possible if the password for pgbouncer_auth_password is not set, but at the same time set it for patroni_superuser_password.

I'll check it out.

[biglolka]

well I left the postgresql_users section as default, didn't touch it
patroni_superuser_and_patroni_replicator_passwords_are_set_but_no_password_for_pgbouncer.gz
patroni_superuser_password_is_set_but_no_password_for_patroni_replicator_and_pgbouncer.gz
without_patroni_superuser_patroni_replicator_and_pgbouncer_password_set.gz

[mainframe]

I can confirm that leaving the pgbouncer_auth_password value empty will produce previously mentioned "role does not exist" error with deploy_pgcluster.yml playbook (ie password autogeneration does not seem to work). That also applies to patroni_superuser_password and patroni_replication_password - if their values are left empty.

[vitabaks]

Are you sure you're using version 2.0?

[mainframe]

@vitabaks you are correct - I was using latest master branch with newer commits beyond 2.0.0 tag:
commit 67a21273856c740eb917312fda092e98ddc279ac (origin/master, old-origin/master, old-origin/HEAD, master) Author: Vitaliy Kukharik <37010174+vitabaks@users.noreply.github.com> Date: Tue Nov 19 15:34:19 2024 +0300

Btw - great work!

[vitabaks]

I’m not yet sure how to reproduce your issue, as I’ve performed numerous cluster deployments without encountering such a problem. Additionally, daily tests confirm that passwords are successfully generated without any errors.

If you could provide the full Ansible logs, it might help me identify the issue. Even better, sharing an archive of your postgresql_cluster directory would allow for a more thorough investigation.

I also recommend trying to deploy via the Console (UI).

[mainframe]

@vitabaks could you please clarify "defined" meaning in this warning "If not defined, a password will be generated automatically during deployment"? Ie should password be autogenerated when password is left empty (pgbouncer_auth_password: "") or if pgbouncer_auth_password variable is commented out in vars/main.yml (as "not defined")?

[chobostar]

Deploy fails when passwords are not defined - ubuntu/jammy64

$ ansible --version
ansible [core 2.16.13]
  config file = /home/user/git/pgsql/postgresql-cluster/automation/ansible.cfg
  configured module search path = ['/home/user/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/user/git/pgsql/postgresql-cluster/.venv/lib/python3.12/site-packages/ansible
  ansible collection location = /home/user/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/user/git/pgsql/postgresql-cluster/.venv/bin/ansible
  python version = 3.12.3 (main, Nov  6 2024, 18:32:19) [GCC 13.2.0] (/home/user/git/pgsql/postgresql-cluster/.venv/bin/python3.12)
  jinja version = 3.1.4
  libyaml = True

on current master

[vitabaks]

Please attach your "automation" directory, I will try to solve the problem.