BUG #5650: Postgres service showing as stopped when in fact it is running

On Thu, Sep 9, 2010 at 12:22 PM, Mark Llewellyn <mark_llewellyn@adp.com> wrote:

The following bug has been logged online:

Bug reference:      5650
Logged by:          Mark Llewellyn
Email address:      mark_llewellyn@adp.com
PostgreSQL version: 9.0 RC1
Operating system:   Windows XP
Description:        Postgres service showing as stopped when in fact it is
running
Details:

The postgresql-9.0 service is showing as *not running* in pgAdmin 3 1.12 RC1
and as *stopped* in the Windows services list, although the database is
actually running and I can connect to it and create databases, tables etc.
It also shows multiple postgresql tasks in Windows task manager.
However, because Windows XP is not aware that the postgresql-9.0 service is
running it is not possible to stop the service.

Maybe this is a dumb question, but how did you start the service?

And applying the good old Windows troubleshooting meme... if you
reboot, does that fix it?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Hi Mark,

On of my college (Sujeet) has found a way to reproduce the same behaviour.
1. Installed PG 9.0 on Win XP SP3
2. Stop the Postgresql-9.0 service from service manager console
3. Create pgpass.conf in postgres (service account) user's profile with an
incorrect password deliberately.
(Refer: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html)
4. Now start the postgresql-9.0 service, it will return an error and the
status
shows stopped
5. However i could connect to the psql shell and get the prompt which means
the server is running.

So, please check if pgpass.conf file exists in service account (postgres)
user's %APPDATA%\postgresql directory with wrong password.

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company<http://www.enterprisedb.com&gt;

On Thu, Sep 9, 2010 at 9:52 PM, Mark Llewellyn <mark_llewellyn@adp.com>wrote:

On Fri, Sep 17, 2010 at 05:51, Ashesh Vashi
<ashesh.vashi@enterprisedb.com> wrote:

Hi Mark,

On of my college (Sujeet) has found a way to reproduce the same behaviour.
1. Installed PG 9.0 on Win XP SP3
2. Stop the Postgresql-9.0 service from service manager console
3. Create pgpass.conf in postgres (service account) user's profile with an
incorrect password deliberately.
(Refer: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html)
4. Now start the postgresql-9.0 service, it will return an error and the
status
   shows stopped
5. However i could connect to the psql shell and get the prompt which means
    the server is running.

I took a quick look at the code, and from what I can tell this is
because PQconnectionNeedsPassword() always returns false if a
pgpass.conf has been used. There is no handling the case where pgpass
is used, but has an incorrect password.

Does anybody recall the specific reason for this? Do we need a way for
pg_ctl to figure this out, or do we need to change it in
PQconnecitonNeedsPassword()?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander <magnus@hagander.net> writes:

I took a quick look at the code, and from what I can tell this is
because PQconnectionNeedsPassword() always returns false if a
pgpass.conf has been used. There is no handling the case where pgpass
is used, but has an incorrect password.

Why should it? That code is complicated enough, I don't think it needs
to have a behavior of pretending that a wrong entry isn't there.

regards, tom lane

On Fri, Sep 24, 2010 at 16:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Magnus Hagander <magnus@hagander.net> writes:

I took a quick look at the code, and from what I can tell this is
because PQconnectionNeedsPassword() always returns false if a
pgpass.conf has been used. There is no handling the case where pgpass
is used, but has an incorrect password.

Why should it?  That code is complicated enough, I don't think it needs
to have a behavior of pretending that a wrong entry isn't there.

In that case, we should probably teach pg_ctl about this case, no?
Since it clearly gives an incorrect message to the user now...

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On 09/24/2010 10:15 AM, Magnus Hagander wrote:

On Fri, Sep 24, 2010 at 16:04, Tom Lane<tgl@sss.pgh.pa.us> wrote:

Magnus Hagander<magnus@hagander.net> writes:

I took a quick look at the code, and from what I can tell this is
because PQconnectionNeedsPassword() always returns false if a
pgpass.conf has been used. There is no handling the case where pgpass
is used, but has an incorrect password.

Why should it? That code is complicated enough, I don't think it needs
to have a behavior of pretending that a wrong entry isn't there.

In that case, we should probably teach pg_ctl about this case, no?
Since it clearly gives an incorrect message to the user now...

pg_ctl decides that the server is running iff it can connect to it. Do
you intend to provide for a different test? Setting an incorrect
password for the service account sounds like pilot error to me.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

On 09/24/2010 10:15 AM, Magnus Hagander wrote:

In that case, we should probably teach pg_ctl about this case, no?
Since it clearly gives an incorrect message to the user now...

pg_ctl decides that the server is running iff it can connect to it. Do
you intend to provide for a different test?

Seems like getting a password challenge from the server is sufficient
evidence that the server is running, whether we are able to meet the
challenge or not. Perhaps we could just twiddle pg_ctl's "is it up"
test a bit to notice whether the connect failure was of this sort.

(Of course, a "pg_ping" utility would be a better answer, but nobody's
gotten around to that in more than ten years, so I'm not holding my
breath.)

regards, tom lane

On Fri, Sep 24, 2010 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

(Of course, a "pg_ping" utility would be a better answer, but nobody's
gotten around to that in more than ten years, so I'm not holding my
breath.)

Hmm, that sounded like it could be my 9.1 mini project - then Google
showed me that SeanC wrote something already.

http://archives.postgresql.org/pgsql-patches/2003-07/msg00053.php

--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise Postgres Company

Dave Page <dpage@pgadmin.org> writes:

On Fri, Sep 24, 2010 at 4:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

(Of course, a "pg_ping" utility would be a better answer, but nobody's
gotten around to that in more than ten years, so I'm not holding my
breath.)

Hmm, that sounded like it could be my 9.1 mini project - then Google
showed me that SeanC wrote something already.
http://archives.postgresql.org/pgsql-patches/2003-07/msg00053.php

Huh, I wonder why we never adopted that? Although I'd be inclined to
do most of the heavy lifting inside libpq, myself, and this is way
more verbose than what pg_ctl wants.

regards, tom lane

On 09/24/2010 11:11 AM, Tom Lane wrote:

Andrew Dunstan<andrew@dunslane.net> writes:

On 09/24/2010 10:15 AM, Magnus Hagander wrote:

In that case, we should probably teach pg_ctl about this case, no?
Since it clearly gives an incorrect message to the user now...

pg_ctl decides that the server is running iff it can connect to it. Do
you intend to provide for a different test?

Seems like getting a password challenge from the server is sufficient
evidence that the server is running, whether we are able to meet the
challenge or not. Perhaps we could just twiddle pg_ctl's "is it up"
test a bit to notice whether the connect failure was of this sort.

pg_ctl does in fact use that sort of logic:

if ((conn = PQconnectdb(connstr)) != NULL &&
(PQstatus(conn) == CONNECTION_OK ||
PQconnectionNeedsPassword(conn)))

But of course, libpq won't set that last condition if there is a bad
password in the pgpass file, which seems a rather perverse thing to do.

cheers

andrew

Hi Ashesh

Yes, this appears to be the issue that the password in the pgpass.conf file for the postgres user had not been changed when the windows account password was reset.

Problem now resolved.

Many thanks
Mark Llewellyn

________________________________
From: Ashesh Vashi [mailto:ashesh.vashi@enterprisedb.com]
Sent: 17 September 2010 04:51
To: Llewellyn, Mark (ADP DSI)
Cc: pgsql-bugs@postgresql.org; Sujeet Rajguru
Subject: Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running

Hi Mark,

On of my college (Sujeet) has found a way to reproduce the same behaviour.
1. Installed PG 9.0 on Win XP SP3
2. Stop the Postgresql-9.0 service from service manager console
3. Create pgpass.conf in postgres (service account) user's profile with an incorrect password deliberately.
(Refer: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html)
4. Now start the postgresql-9.0 service, it will return an error and the status
shows stopped
5. However i could connect to the psql shell and get the prompt which means
the server is running.

So, please check if pgpass.conf file exists in service account (postgres) user's %APPDATA%\postgresql directory with wrong password.

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company<http://www.enterprisedb.com&gt;

On Thu, Sep 9, 2010 at 9:52 PM, Mark Llewellyn <mark_llewellyn@adp.com<mailto:mark_llewellyn@adp.com>> wrote:

The following bug has been logged online:

Bug reference: 5650
Logged by: Mark Llewellyn
Email address: mark_llewellyn@adp.com<mailto:mark_llewellyn@adp.com>
PostgreSQL version: 9.0 RC1
Operating system: Windows XP
Description: Postgres service showing as stopped when in fact it is
running
Details:

The postgresql-9.0 service is showing as *not running* in pgAdmin 3 1.12 RC1
and as *stopped* in the Windows services list, although the database is
actually running and I can connect to it and create databases, tables etc.
It also shows multiple postgresql tasks in Windows task manager.
However, because Windows XP is not aware that the postgresql-9.0 service is
running it is not possible to stop the service.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org<mailto:pgsql-bugs@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by e-mail and delete the message and any attachments from your system.

We're happy to see the problem resolved on your end. :-)

--
Thanks & Regards,

Ashesh Vashi
EnterpriseDB INDIA: Enterprise Postgres Company<http://www.enterprisedb.com&gt;

*http://www.linkedin.com/in/asheshvashi*&lt;http://www.linkedin.com/in/asheshvashi&gt;

On Mon, Sep 27, 2010 at 8:57 PM, Llewellyn, Mark (ADP DSI) <
mark_llewellyn@adp.com> wrote:

On Mon, Sep 27, 2010 at 12:44 PM, Ashesh Vashi <
ashesh.vashi@enterprisedb.com> wrote:

We're happy to see the problem resolved on your end. :-)--

However, it doesn't seem that we've actually done anything about the
underlying issue with pg_ctl.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Robert Haas wrote:

On Mon, Sep 27, 2010 at 12:44 PM, Ashesh Vashi <
ashesh.vashi@enterprisedb.com> wrote:

We're happy to see the problem resolved on your end. :-)--

However, it doesn't seem that we've actually done anything about the
underlying issue with pg_ctl.

I will look at that in the next few weeks.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Magnus Hagander wrote:

On Fri, Sep 17, 2010 at 05:51, Ashesh Vashi
<ashesh.vashi@enterprisedb.com> wrote:

Hi Mark,

On of my college (Sujeet) has found a way to reproduce the same behaviour.
1. Installed PG 9.0 on Win XP SP3
2. Stop the Postgresql-9.0 service from service manager console
3. Create pgpass.conf in postgres (service account) user's profile with an
incorrect password deliberately.
(Refer: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html)
4. Now start the postgresql-9.0 service, it will return an error and the
status
?? shows stopped
5. However i could connect to the psql shell and get the prompt which means
??? the server is running.

I took a quick look at the code, and from what I can tell this is
because PQconnectionNeedsPassword() always returns false if a
pgpass.conf has been used. There is no handling the case where pgpass
is used, but has an incorrect password.

Does anybody recall the specific reason for this? Do we need a way for
pg_ctl to figure this out, or do we need to change it in
PQconnecitonNeedsPassword()?

I was not able to reproduce this failure on my BSD system using GIT
head:

$ psql test
psql: FATAL: password authentication failed for user "postgres"
password retrieved from file "/u/postgres/.pgpass"

$ pg_ctl status
pg_ctl: server is running (PID: 710)
/usr/var/local/pgsql/bin/postgres "-i"

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Fri, Nov 12, 2010 at 03:49, Bruce Momjian <bruce@momjian.us> wrote:

Magnus Hagander wrote:

On Fri, Sep 17, 2010 at 05:51, Ashesh Vashi
<ashesh.vashi@enterprisedb.com> wrote:

Hi Mark,

On of my college (Sujeet) has found a way to reproduce the same behaviour.
1. Installed PG 9.0 on Win XP SP3
2. Stop the Postgresql-9.0 service from service manager console
3. Create pgpass.conf in postgres (service account) user's profile with an
incorrect password deliberately.
(Refer: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html)
4. Now start the postgresql-9.0 service, it will return an error and the
status
?? shows stopped
5. However i could connect to the psql shell and get the prompt which means
??? the server is running.

I took a quick look at the code, and from what I can tell this is
because PQconnectionNeedsPassword() always returns false if a
pgpass.conf has been used. There is no handling the case where pgpass
is used, but has an incorrect password.

Does anybody recall the specific reason for this? Do we need a way for
pg_ctl to figure this out, or do we need to change it in
PQconnecitonNeedsPassword()?

I was not able to reproduce this failure on my BSD system using GIT
head:

       $ psql test
       psql: FATAL:  password authentication failed for user "postgres"
       password retrieved from file "/u/postgres/.pgpass"

       $ pg_ctl status
       pg_ctl: server is running (PID: 710)
       /usr/var/local/pgsql/bin/postgres "-i"

The problem is not in pg_ctl status, it's in pg_ctl start. They're
different codepaths - status never tries to actually connect, it just
checks if the process is alive.

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander wrote:

On Fri, Nov 12, 2010 at 03:49, Bruce Momjian <bruce@momjian.us> wrote:

Magnus Hagander wrote:

On Fri, Sep 17, 2010 at 05:51, Ashesh Vashi
<ashesh.vashi@enterprisedb.com> wrote:

Hi Mark,

On of my college (Sujeet) has found a way to reproduce the same behaviour.
1. Installed PG 9.0 on Win XP SP3
2. Stop the Postgresql-9.0 service from service manager console
3. Create pgpass.conf in postgres (service account) user's profile with an
incorrect password deliberately.
(Refer: http://www.postgresql.org/docs/8.4/interactive/libpq-pgpass.html)
4. Now start the postgresql-9.0 service, it will return an error and the
status
?? shows stopped
5. However i could connect to the psql shell and get the prompt which means
??? the server is running.

I took a quick look at the code, and from what I can tell this is
because PQconnectionNeedsPassword() always returns false if a
pgpass.conf has been used. There is no handling the case where pgpass
is used, but has an incorrect password.

Does anybody recall the specific reason for this? Do we need a way for
pg_ctl to figure this out, or do we need to change it in
PQconnecitonNeedsPassword()?

I was not able to reproduce this failure on my BSD system using GIT
head:

? ? ? ?$ psql test
? ? ? ?psql: FATAL: ?password authentication failed for user "postgres"
? ? ? ?password retrieved from file "/u/postgres/.pgpass"

? ? ? ?$ pg_ctl status
? ? ? ?pg_ctl: server is running (PID: 710)
? ? ? ?/usr/var/local/pgsql/bin/postgres "-i"

The problem is not in pg_ctl status, it's in pg_ctl start. They're
different codepaths - status never tries to actually connect, it just
checks if the process is alive.

Uh, I still cannot reproduce the failure:

$ psql postgres
psql: FATAL: password authentication failed for user "postgres"
password retrieved from file "/u/postgres/.pgpass"

$ pg_ctl stop
waiting for server to shut down.... done
server stopped

$ pg_ctl -l /dev/null start
server starting

(Got to love that new 9.0 pgpass error message.)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce@momjian.us> writes:

Uh, I still cannot reproduce the failure:

I would imagine you need -w option on the start. The whole issue
here is whether start's wait-for-server-start code works.

regards, tom lane

Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

Uh, I still cannot reproduce the failure:

I would imagine you need -w option on the start. The whole issue
here is whether start's wait-for-server-start code works.

Thanks, I am now able to reproduce this. I was able to get this to
report the .pgpass problem:

$ psql postgres
psql: FATAL: password authentication failed for user "postgres"
password retrieved from file "/u/postgres/.pgpass"

$ pg_ctl stop
waiting for server to shut down.... done
server stopped

$ pg_ctl -w -l /dev/null start
waiting for server to start....FATAL: password authentication failed
for user "postgres"
password retrieved from file "/u/postgres/.pgpass"
.FATAL: password authentication failed for user "postgres"
password retrieved from file "/u/postgres/.pgpass"
.FATAL: password authentication failed for user "postgres"
password retrieved from file "/u/postgres/.pgpass"
.^C

I basically report the connection error string if it starts with "FATAL:".

I originally tried to check for an ERRCODE_INVALID_PASSWORD error field
(see // comments), but it seems there is no way to access this, i.e.
PQgetResult(conn) on a connection failure is always NULL.

Anyway, perhaps FATAL is a better test because it will report any major
failure, not just a .pgpass one.

Patch attached.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +