Thread: BUG #5163: Admin can't connect and won't use port 5432

BUG #5163: Admin can't connect and won't use port 5432

From
"Charles"
Date:
The following bug has been logged online:

Bug reference:      5163
Logged by:          Charles
Email address:      cnicol@mediasensorcorp.com
PostgreSQL version: 8.4.1
Operating system:   Vista Home Premium
Description:        Admin can't connect and won't use port 5432
Details:

The installer forced me to use port 5433.
Admin launched, I could see the DB in the object pane, but I could not start
it. I had to use port 5433.

So how can I make it work with the correct port and start it?

It won't work.
I tried to open the port on firewall, but it doesnt work with the firewall
off.
I already tried the install with firewall off trick.
Not sure why it created a new User account on my login screen called
postgress either. Was that necessary?

Baffled. I am blaming MS and VISTA at this point.

Re: BUG #5163: Admin can't connect and won't use port 5432

From
Craig Ringer
Date:
Charles wrote:
> The following bug has been logged online:
>
> Bug reference:      5163
> Logged by:          Charles
> Email address:      cnicol@mediasensorcorp.com
> PostgreSQL version: 8.4.1
> Operating system:   Vista Home Premium
> Description:        Admin can't connect and won't use port 5432
> Details:
>
> The installer forced me to use port 5433.

If you had an older copy of PostgreSQL still installed and it was using
port 5432 already, the installer would pick the next available port.

> So how can I make it work with the correct port and start it?

Uninstall or disable whatever's using port 5432. Then edit
postgresql.conf and change the port setting from 5433 to 5432 and
restart the PostgreSQL service.

> Not sure why it created a new User account on my login screen called
> postgress either. Was that necessary?

Security. PostgreSQL is a "sealed server" where the user is absolutely
never meant to mess with the data directory at all. The user has no
reason to access the data directory or be able to access it. So
postgresql creates a separate user account for the postgresql server and
restricts access to the data directory to that account.

This also means that if a security hole is discovered in the postgresql
server and someone is able to exploit postgresql to run code on your
machine, they can only do the things that the restricted-priveleges
postgresql account lets them do. They shouldn't be able to install a
rootkit, drop a virus on the machine, etc.

Running services under specific user accounts is absolutely standard on
all operating systems, including Windows. Many Microsoft services run
under different user accounts too, such as the IIS web server, Exchange
mail server, and in fact many standard built-in services on a normal
Windows system.

What's odd is that you noticed the new user account. Is it showing up on
your login screen? Or did you only notice because you were asked for a
password? A service account shouldn't make any difference to your use of
the machine, and it already has lots of them when it's installed.

> Baffled. I am blaming MS and VISTA at this point.

Incorrectly so in this case, I'm afraid.

--
Craig Ringer

Re: BUG #5163: Admin can't connect and won't use port 5432

From
Craig Ringer
Date:
[Please reply to the list, not just to me - use the "reply all" button]

cnicol@mediasensorcorp.com wrote:
> Tried a few time to uninstall and install. Is there additional
> unsinstall steps beyond the remove program control panel thing?

Assuming you installed using the installer, rather than using the
non-installer .zip and creating the service etc yourself, then removing
PostgreSQL using the add/remove programs should be all that is required.

>  I port
> scanned and nothing was using port 5432.

A port scan is the wrong tool for the job.

Use the operating system's built in "netstat" command to show listening
ports. You can use it via cmd.exe. Search the Internet and read the
built-in help (start with "netstat /?") for more information if you have
trouble.

> I turned the firewall off in
> order to even install it.

That is not necessary.

> Unfortunately the client app needs 5432 port to be used and I can't
> change it to 5433.

So edit postgresql.conf and change the port the server uses to port
5432, then restart the service. Not hard.

postgresql.conf lives in a subdirectory of the directory you installed
PostgreSQL in. If you use PgAdmin III you can open postgresql.conf via
its menus -  it's under "Tools" -> "Server Configuration".

--
Craig Ringer

Re: BUG #5163: Admin can't connect and won't use port 5432

From
Craig Ringer
Date:
cnicol@mediasensorcorp.com wrote:

> Netstat -a shows 0.0.0.0:5432 Listening

First: please reply to the list, not to me. I will not respond to
further mail directly to me. Use the "reply all" button in your mail client.

As for the port: Yes, it's listening, but what process is listening on
it? Get netstat to show the process IDs, then use Task Manager to match
those up with process names. See:

http://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/netstat.mspx?mfr=true

> But I don't see my IP 192.168.0.199 having that port open.

So maybe it's only listening on 127.0.0.1 (the loopback address) ? Did
you check? Netstat tells you that.

You might need to tell PostgreSQL to listen on all addresses as I think
it may default to listening only on the loopback address for security.

> I noticed when I restart the Postgre server it gives me a message in a
> CMD window that says:
> Server not started
> See nethelp MSG 3521
> And system error 5 has occured

And what happens if you use the "net help" command to display the text
for that error message? (Who knows why Microsoft did it this way rather
than just displaying the error in-place).

Also check the Windows event log to see what might be going on. You can
find the Event Viewer in the Administrative Tools section of the Control
Panel.

> When I run pgAdmin III
> In object browser 1 server is indicated.

Did you check to make sure it's connecting to the right port, after you
restarted the service?

> When I double click it asks for PW then I get an error message as Follows
> "2009-11-04 03:19:09 ESTLOG:  invalid IP mask "md5": Unknown host
> 2009-11-04 03:19:09 ESTCONTEXT:  line 73 of configuration file
> "C:/Program Files/PostgreSQL/8.4/data/pg_hba.conf"
> 2009-11-04 03:19:09 ESTFATAL:  could not load pg_hba.conf"

You've mucked up your pg_hba.conf - it looks like you've deleted the
host specification on a line. if you wanted to say "any host", use
0.0.0.0/0 instead of deleting the entry.

That text appears to be read out of the server error log. It looks like
PgAdmin is helping you out by trying to start the service and when it
fails, looking at the server error log to see why.

> Can you help me get this going?

Not unless you start using the resources you already have, no. USE THE
POSTGRESQL SERVER ERROR LOG - remember where I said it was? It is your
main tool in any troubleshooting process.

PgAdmin II has already done this for you for the immediate problem and
shown you that you've messed up your pg_hba.conf. The error even tells
you the line number and what's wrong with it. I can't actually fix it
for you, and you already have enough information to fix it yourself.

--
Craig Ringer