Thread: source of connection fails at pg startup?

When I start my postgresql server I get 11 messages reporting that "password
authentication failed for user 'postgres'" spaced about ~.5sec apart.
I increased the logging level to INFO, and added the application name to the
message format (after the pid) which resulted in:

   2018-05-21 23:04:44.395 MDT [20232][[unknown]] [unknown]@[unknown] LOG:  connection received: host=[local]
   2018-05-21 23:04:44.395 MDT [20232][[unknown]] postgres@postgres FATAL:  password authentication failed for user
"postgres"
   2018-05-21 23:04:44.395 MDT [20232][[unknown]] postgres@postgres DETAIL:  Password does not match for user
"postgres".
    Connection matched pg_hba.conf line 90: "local   all   all   md5"

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.  The processes are
too short-lived for me to catch with ps.  Successful connect message example:

   2018-05-21 23:25:13.577 MDT [21080][[unknown]] [unknown]@[unknown] LOG:  connection received: host=[local]
   2018-05-21 23:25:13.578 MDT [21080][[unknown]] postgres@postgres LOG:  connection authorized: user=postgres
database=postgres
   2018-05-21 23:25:13.579 MDT [21080][psql] postgres@postgres LOG:  disconnection: session time: 0:00:00.002
user=postgresdatabase=postgres host=[local]
 

My question is, how can I find out where the connections are coming from so I can
modify them to provide passwords (so I can go back to "md5")?  Are there startup-
time connections made by postgresql itself or is this likely from some Ubuntu-
specific configuration?

Thanks.

On 05/21/2018 10:48 PM, Stuart McGraw wrote:
> When I start my postgresql server I get 11 messages reporting that 
> "password
> authentication failed for user 'postgres'" spaced about ~.5sec apart.
> I increased the logging level to INFO, and added the application name to 
> the
> message format (after the pid) which resulted in:
> 
>    2018-05-21 23:04:44.395 MDT [20232][[unknown]] [unknown]@[unknown] 
> LOG:  connection received: host=[local]
>    2018-05-21 23:04:44.395 MDT [20232][[unknown]] postgres@postgres 
> FATAL:  password authentication failed for user "postgres"
>    2018-05-21 23:04:44.395 MDT [20232][[unknown]] postgres@postgres 
> DETAIL:  Password does not match for user "postgres".
>      Connection matched pg_hba.conf line 90: "local   all   all   md5"
> 
> 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.  The 
> processes are
> too short-lived for me to catch with ps.  Successful connect message 
> example:
> 
>    2018-05-21 23:25:13.577 MDT [21080][[unknown]] [unknown]@[unknown] 
> LOG:  connection received: host=[local]
>    2018-05-21 23:25:13.578 MDT [21080][[unknown]] postgres@postgres 
> LOG:  connection authorized: user=postgres database=postgres
>    2018-05-21 23:25:13.579 MDT [21080][psql] postgres@postgres LOG:  
> disconnection: session time: 0:00:00.002 user=postgres database=postgres 
> host=[local]
> 
> My question is, how can I find out where the connections are coming from 
> so I can
> modify them to provide passwords (so I can go back to "md5")?  Are there 

 From the error messages it looks like your connections are already 
supplying passwords. I am guessing that the issue is that you have not 
created a password for the database user postgres. See the below:

https://help.ubuntu.com/community/PostgreSQL

Basic Server Setup

for how to do that. If you have set up a password then the connections 
are using the wrong one. For catching the connections uncomment and set 
to on:

log_connections
log_disconnections

in postgresql.conf.

You might also try something like:

watch -n 0.5 'ps aux|grep post'

to see if you catch the connections from the system end.

> startup-
> time connections made by postgresql itself or is this likely from some 
> Ubuntu-
> specific configuration?
> 
> Thanks.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.

            regards, tom lane

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.