On 05/22/2018 07:58 AM, Tom Lane wrote:
Stuart McGraw <smcg4191@xxxxxxxxxx> writes:
When I start my postgresql server I get 11 messages reporting that "password
authentication failed for user 'postgres'" spaced about ~.5sec apart.
Sounds like the trace of something probing the postmaster to see if it's
ready yet. Pre-v10 versions of pg_ctl did exactly that, but with a
1-second wait interval, so this couldn't be pg_ctl itself (even if you
hadn't specified this is v10).
This is on a Ububuntu-18.04 machine with postgresql-10.3 from Ubuntu. As distributed
the pg_hba.conf line mentioned used "peer" authentication method, I have changed to
"md5". When I change back to "peer" the error messages go away.
In that case, whatever is doing it is running as the postgres user.
Putting all this together, I'd bet on the connections coming from an
Ubuntu-specific startup script. Poke around in their PG start script
for something like a pg_isready call in a loop with an 0.5 second wait.
I imagine that undoing that would be rather difficult, even if you
wanted to run with a locally-modified script. They probably had a
reason why they didn't want to leave it to pg_ctl to do the waiting.
Personally, my recommendation would be to go back to "peer" auth,
at least for local connections by postgres. There is no reason
to think that passwords are a more secure approach: password
management is a hard problem, especially for automated connections
like these.
Thanks, you were right, the issue is indeed from a Ubuntu (or Debian)
specific startup script. I eventually found that they use a Perl
script, /usr/bin/pg_ctlcluster, to run postgresql, and in there I
found a function that runs psql up to 10 times at .5 second intervals
to check if the server is ready.
There didn't seem to be any obvious way to give it a password. The
script intentionally sets the PGPASSWORD environment variable to a
bogus value. Giving both OS users root and postgres .pgpass files
didn't help, I guess because of the bogus PGPASSWORD value takes
precedence.
The reason for a password is not so much better security but that I
have bunch of scripts that set up and manipulate databases that came
over from my former Fedora system and that were written expecting the
postgres account has a password. I made a couple stabs at changing
some of them a while ago but it involved running the commands with
"su - postgres ...". Some are embedded in yaml, involve variable
substitution, and the multiple layers of quoting was just too much
for my meager scripting skills. :-(
Given that I understand the problem now thanks to you, Adrian and
Luan Huynh, and that the errors don't seem to have any bad effects on
the server's operation, I think I will just live with them for now.
Thanks very much for everyone's help.
