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": ["/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:

postgresql_cluster/automation/vars/main.yml

Line 353 in 23d0219

pgbouncer_auth_password: "" # If not defined, a password will be generated automatically during deployment

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