Re: source of connection fails at pg startup?

On 05/22/2018 07:58 AM, Tom Lane wrote:
> Stuart McGraw <smcg4191@mtneva.com> 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.