pg_autoctl create postgres := ERROR Connection to database failed: fe_sendauth: no password supplied

[sacrophyte]

I am not quite sure what I am doing wrong. If you can help me spot the error of my ways, I would very much appreciate it.

"data" is running on port 5432
monitor is on port 5433

DB_HOME=/var/lib/pgsql/12
pg_autoctl create postgres \
 --pgdata $DB_HOME/data \
 --pghost urblimsdbdev2a.admin.uillinois.edu \
 --pgport 5432 \
 --hostname urblimsdbdev2a.admin.uillinois.edu \
 --name dev2a \
 --pg-hba-lan \
 --auth trust \
 --ssl-self-signed \
 --monitor 'postgres://[email protected]:5433/pg_auto_failover?sslmode=require'

07:14:02 1488183 INFO  Using default --ssl-mode "require"
07:14:02 1488183 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic
07:14:02 1488183 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.
07:14:02 1488183 WARN  See https://www.postgresql.org/docs/current/libpq-ssl.html for details
07:14:02 1488183 INFO  Started pg_autoctl postgres service with pid 1488186
07:14:02 1488186 INFO   /usr/pgsql-12/bin/pg_autoctl do service postgres --pgdata /var/lib/pgsql/12/data -v
07:14:02 1488183 INFO  Started pg_autoctl node-init service with pid 1488187
07:14:02 1488187 INFO  Continuing from a previous `pg_autoctl create` failed attempt
07:14:02 1488187 INFO  PostgreSQL state at registration time was: PGDATA exists
07:14:02 1488187 INFO  FSM transition from "init" to "single": Start as a single node
07:14:02 1488187 INFO  Initialising postgres as a primary
07:14:02 1488187 INFO  Granting connection privileges on 64.22.189.128/25
07:14:02 1488187 INFO  CREATE DATABASE postgres;
07:14:02 1488187 ERROR Connection to database failed: fe_sendauth: no password supplied
07:14:02 1488187 ERROR Failed to connect to local Postgres database at "port=5432 dbname=template1 host=urblimsdbdev2a.admin.uillinois.edu", see above for details
07:14:02 1488187 ERROR Failed to create database postgres with owner
07:14:02 1488187 ERROR Failed to transition from state "init" to state "single", see above.
07:14:02 1488183 ERROR pg_autoctl service node-init exited with exit status 12

I do not understand why pg_autoctl is trying to connect to template1 with a password. Further, I do not understand why the following rule in pg_hba.conf seems (to me) to be ignored:
hostssl all all 0.0.0.0/0 trust

I put the above in temporarily to try to get around the fe_sendauth error to no avail (took it back out).
I tried to use --pg-hba-lan, but that does not seem to help either. Also, I am confused why --pg-hba-lan adds a line for the postgres database, but the error is about template1.

Another very weird thing - running this command ALWAYS stops my "data" cluster. I do not know why.

What am I doing wrong?

Update:
I also tried to specify the name of the database with --dbname postgres , but that did not seem to alter the error.

[DimCitus]

So, we use template1 to issue the create database statement because we can count on that database existing in Postgres. Then, I see you use ``--pghost urblimsdbdev2a.admin.uillinois.eduand I believe that's where you problem comes from. Please try without using--pghost`, allowing the local connections to use Unix Domain Sockets.

Finally, pg_autoctl is the parent process of Postgres. So when pg_autoctl is not running, Postgres is not running, that's expected.

[sacrophyte]

I removed --pghost and still get the same error.

[postgres@urblimsdbdev2a ~]$ cat ./autoctl_create_postgres_dev2a
pg_autoctl create postgres \
 --pgdata $DB_HOME/data \
 --pgport 5432 \
 --hostname urblimsdbdev2a.admin.uillinois.edu \
 --dbname postgres \
 --name dev2a \
 --pg-hba-lan \
 --auth trust \
 --ssl-self-signed \
 --monitor 'postgres://[email protected]:5433/pg_auto_failover?sslmode=require'
[postgres@urblimsdbdev2a ~]$ ./autoctl_create_postgres_dev2a
08:47:28 1494393 INFO  Using default --ssl-mode "require"
08:47:28 1494393 INFO  Using --ssl-self-signed: pg_autoctl will create self-signed certificates, allowing for encrypted network traffic
08:47:28 1494393 WARN  Self-signed certificates provide protection against eavesdropping; this setup does NOT protect against Man-In-The-Middle attacks nor Impersonation attacks.
08:47:28 1494393 WARN  See https://www.postgresql.org/docs/current/libpq-ssl.html for details
08:47:28 1494393 INFO  Started pg_autoctl postgres service with pid 1494398
08:47:28 1494398 INFO   /usr/pgsql-12/bin/pg_autoctl do service postgres --pgdata /var/lib/pgsql/12/data -v
08:47:28 1494393 INFO  Started pg_autoctl node-init service with pid 1494399
08:47:28 1494399 INFO  Continuing from a previous `pg_autoctl create` failed attempt
08:47:28 1494399 INFO  PostgreSQL state at registration time was: PGDATA exists
08:47:28 1494399 INFO  FSM transition from "init" to "single": Start as a single node
08:47:28 1494399 INFO  Initialising postgres as a primary
08:47:28 1494399 INFO  Granting connection privileges on 64.22.189.128/25
08:47:28 1494403 INFO   /usr/pgsql-12/bin/postgres -D /var/lib/pgsql/12/data -p 5432 -h *
08:47:28 1494398 INFO  Postgres is now serving PGDATA "/var/lib/pgsql/12/data" on port 5432 with pid 1494403
08:47:28 1494399 INFO  CREATE DATABASE postgres;
08:47:28 1494399 ERROR Connection to database failed: fe_sendauth: no password supplied
08:47:28 1494399 ERROR Failed to connect to local Postgres database at "port=5432 dbname=template1 host=urblimsdbdev2a.admin.uillinois.edu", see above for details
08:47:28 1494399 ERROR Failed to create database postgres with owner
08:47:28 1494399 ERROR Failed to transition from state "init" to state "single", see above.
08:47:28 1494393 ERROR pg_autoctl service node-init exited with exit status 12

[DimCitus]

Yeah well you're still running the command on-top of your misconfigured attempt. It would work if you tried from scratch, I believe, or if you would pg_autoctl drop node --destroy and then try again.

If you want to override the current value for pghost, you could try --pghost localhost or, to use Unix Domain Sockets, the socket directory for your OS. On debian that would be --pghost /var/run/postgresql (and you need permissions, easiest to run as the postgres system user, or as a user that belongs to the postgres group), on RHEL derived systems that would be --pghost /tmp.

[sacrophyte]

My experience with --destroy is that the cluster will be deleted - I do not want that.
I did try to drop the node (sans destroy), but that did not make the problem go away.
I used --pghost /tmp and that did the trick.

If I do not wish to use --pghost localhost (because in the near future the pg host will not be on localhost), what is the correct way to specify --pghost initially? Stick with --pghost urblimsdbdev2a?

Thanks for your help!