How to add hosts to pg_hba.conf and postgresql.conf?

On 03/15/11 8:50 AM, general_lee wrote:

svcadm restart svc:/application/database/postgresql_83:default_64bit

But I get a "connection refused" if I try and telnet to port 5432 from a
remote host.

firewall?

Hi,

Thanks for the reply.

Nope, not a Firewall problem.

I also get connection refused if I telnet by IP address on the Postgres
server.

I can telnet localhost 5432

But not telnet 5432

Anything else I can try here? I'm not sure my pg_hba.conf is correct, what
do you think?

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3709271.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tuesday, March 15, 2011 8:50:34 am general_lee wrote:

Hi Guys,

I'm trying to allow a remote host on our 10.3.55.X network remote access to
a Postgres Database on the same network.

We're running Solaris 10 with Postgres 83

My postgresql.conf looks like this;

listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of
addresses; # defaults to 'localhost', '*' = all port = 5432

My pg_hba.conf looks like this;

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 10.3.55.0/24 255.255.255.0 trust
# IPv6 local connections:
host all all ::1/128 trust

I restart Postgres with;

svcadm restart svc:/application/database/postgresql_83:default_64bit

But I get a "connection refused" if I try and telnet to port 5432 from a
remote host.

Is this a "connection refused" from Postgres or telnet?

What am I missing?

Thanks,

--
Adrian Klaver
adrian.klaver@gmail.com

Both the below were ran on the Postgres Server.

$ telnet 5432
Trying ...
telnet: Unable to connect to remote host: Connection refused

$ psql -h -U post_owner -d post_db
psql: could not connect to server: Connection refused
Is the server running on host "" and accepting
TCP/IP connections on port 5432?

Thanks,

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3709528.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Adrian Klaver <adrian.klaver@gmail.com> writes:

On Tuesday, March 15, 2011 8:50:34 am general_lee wrote:

But I get a "connection refused" if I try and telnet to port 5432 from a
remote host.

Is this a "connection refused" from Postgres or telnet?

Postgres doesn't ever emit an error message spelled like that. This
error means the connection request is never getting to the postmaster,
which means either (a) the postmaster isn't actually running, or at
least isn't listening on the port you think it is, or (b) there's a
packet filter or something similar blocking the connection request from
getting through.

If you can connect locally but not remotely, then it's almost certainly
(b). Check network-related settings to see about opening up port 5432.

regards, tom lane

can you post output of netstat -lnp when run on the machine
running postgresql server ?

On Tue, Mar 15, 2011 at 9:54 PM, general_lee <tim_stockford@hotmail.com>wrote:

Hi,

I've checked IP Filter is disabled...

Also this is Solaris 10 - netstat -lnp is not recognised. Here is netstat
-np without my macs :)

I'm using vnet3 so looks like I'm using the wrong netmask, tried editing
pg_hba.conf to 255.255.255.255 instead but still no joy...

Device IP Address Mask Flags Phys Addr
------ -------------------- --------------- -------- ---------------
vnet3 10.3.55.1 255.255.255.255 o
vnet3 10.3.55.181 255.255.255.255 o
vnet3 10.3.55.8 255.255.255.255 o
vnet3 10.3.55.9 255.255.255.255 o
vnet3 10.3.55.4 255.255.255.255
vnet1 10.3.200.182 255.255.255.255 SPLA
vnet3 10.3.55.182 255.255.255.255 SPLA
vnet1 224.0.0.0 240.0.0.0 SM
vnet3 224.0.0.0 240.0.0.0 SM

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3711224.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 03/15/2011 09:36 AM, general_lee wrote:

Both the below were ran on the Postgres Server.

$ telnet 5432
Trying ...
telnet: Unable to connect to remote host: Connection refused

$ psql -h -U post_owner -d post_db
psql: could not connect to server: Connection refused
Is the server running on host "" and accepting
TCP/IP connections on port 5432?

With the above connection string you are not actually connecting to a
host. From the error message 'Is the server running on host ""...'

Thanks,

--
Adrian Klaver
adrian.klaver@gmail.com

On 15/03/2011 16:36, general_lee wrote:

Both the below were ran on the Postgres Server.

$ telnet 5432
Trying ...
telnet: Unable to connect to remote host: Connection refused

$ psql -h -U post_owner -d post_db
psql: could not connect to server: Connection refused
Is the server running on host "" and accepting
TCP/IP connections on port 5432?

Don't you need to have a host in there? - e.g.

$ psql -h 127.0.0.1 <etc....>

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

To confirm, 10.3.55.182 is the Postgres Server

$ telnet 10.3.55.182 5432
Trying ...
telnet: Unable to connect to remote host: Connection refused

$ telnet localhost 5432
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

$ psql -h 10.3.55.182 -U post_owner -d post_db
psql: could not connect to server: Connection refused
Is the server running on host "" and accepting
TCP/IP connections on port 5432?

My pg_hba.conf looks like this;

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 10.3.55.0/24 255.255.255.255 trust
# IPv6 local connections:
host all all ::1/128 trust

My postgresql.conf looks like this;

# - Connection Settings -

listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
#port = 5432
max_connections = 100

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3711674.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 03/15/11 10:33 AM, Raymond O'Donnell wrote:

Don't you need to have a host in there? - e.g.

$ psql -h 127.0.0.1 <etc....>

$5 gets you $20, the OP was obfuscating his hosts/IPs.

I'm trying to allow a remote host on our 10.3.55.X network remote access
to
a Postgres Database on the same network.

We're running Solaris 10 with Postgres 83

My postgresql.conf looks like this;

listen_addresses = '*' # what IP address(es) to listen
on;
# comma-separated list of
addresses;
# defaults to 'localhost', '*' =
all
port = 5432

My pg_hba.conf looks like this;

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 10.3.55.0/24 255.255.255.0 trust
# IPv6 local connections:
host all all ::1/128 trust

I restart Postgres with;

svcadm restart svc:/application/database/postgresql_83:default_64bit

But I get a "connection refused" if I try and telnet to port 5432 from a
remote host.

I'll give you pretty good odds your postmaster is not listening
on '*' like you want it to.

What does 'netstat -a | grep LISTEN' tell you?

Benny

--
"Hairy ape nads." -- Colleen, playing Neverwinter Nights

Here you go.

Explains why locahost is OK, but how do I get PM to listen on *.5432

Thanks for your help

# netstat -a | grep LISTEN
localhost.5999 *.* 0 0 49152 0 LISTEN
*.ssh *.* 0 0 49152 0 LISTEN
*.telnet *.* 0 0 49152 0 LISTEN
localhost.smtp *.* 0 0 49152 0 LISTEN
localhost.submission *.* 0 0 49152 0 LISTEN
*.sunrpc *.* 0 0 49152 0 LISTEN
*.32796 *.* 0 0 49152 0 LISTEN
*.lockd *.* 0 0 49152 0 LISTEN
perjt02-55.5666 *.* 0 0 49152 0 LISTEN
localhost.5432 *.* 0 0 49152 0 LISTEN
*.ssh *.* 0
0 49152 0 LISTEN
*.telnet *.* 0
0 49152 0 LISTEN

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3712646.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 03/15/2011 10:37 AM, general_lee wrote:

To confirm, 10.3.55.182 is the Postgres Server

$ telnet 10.3.55.182 5432
Trying ...
telnet: Unable to connect to remote host: Connection refused

$ telnet localhost 5432
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.

$ psql -h 10.3.55.182 -U post_owner -d post_db
psql: could not connect to server: Connection refused
Is the server running on host "" and accepting
TCP/IP connections on port 5432?

My pg_hba.conf looks like this;

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 10.3.55.0/24 255.255.255.255 trust

Not sure that it makes a difference, but you only need to do one of either:
10.3.55.0/24
10.3.55.0 255.255.255.0

# IPv6 local connections:
host all all ::1/128 trust

My postgresql.conf looks like this;

# - Connection Settings -

listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
#port = 5432
max_connections = 100

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3711674.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Adrian Klaver
adrian.klaver@gmail.com

On 03/15/11 10:24 AM, general_lee wrote:

Hi,

I've checked IP Filter is disabled...

Also this is Solaris 10 - netstat -lnp is not recognised. Here is netstat
-np without my macs :)

netstat -an

shows listening ports.

To confirm, the below would indicate PM is not listening on *.5432

How do I change this?

# netstat -a | grep LISTEN
localhost.5999 *.* 0 0 49152 0 LISTEN
*.ssh *.* 0 0 49152 0 LISTEN
*.telnet *.* 0 0 49152 0 LISTEN
localhost.smtp *.* 0 0 49152 0 LISTEN
localhost.submission *.* 0 0 49152 0 LISTEN
*.sunrpc *.* 0 0 49152 0 LISTEN
*.32796 *.* 0 0 49152 0 LISTEN
*.lockd *.* 0 0 49152 0 LISTEN
perjt02-55.5666 *.* 0 0 49152 0 LISTEN
localhost.5432 *.* 0 0 49152 0 LISTEN
*.ssh *.* 0
0 49152 0 LISTEN
*.telnet *.* 0
0 49152 0 LISTEN

--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-add-hosts-to-pg-hba-conf-and-postgresql-conf-tp3708421p3713212.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 15/03/2011 18:14, general_lee wrote:

To confirm, the below would indicate PM is not listening on *.5432

How do I change this?

Did you restart Postgres after changing listen_addresses in postgresql.conf?

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

Explains why locahost is OK, but how do I get PM to listen on *.5432

Well, you believe you already have, by telling PostgreSQL to
listen on '*'. There might be a few different reason why it's
not:

1) Are you editing the correct postgresql.conf file? Do you have
multiple ones on the filesystem somewhere?

2) Are you editing the correct directive? It's 'listen_addresses'
on both my 8.x and 9.x servers. It looks like the right option
in your original posting, have you re-typed the option name just
to be sure a control or unprintable character wasn't accidentally
inserted?

Does PostgreSQL log anything when you restart it that would suggest
which file it's looking at, or if it's complaining about the
configuration it's reading?

Benny

--
"Hairy ape nads." -- Colleen, playing Neverwinter Nights

On 03/15/2011 11:14 AM, general_lee wrote:

To confirm, the below would indicate PM is not listening on *.5432

How do I change this?

Are you sure you are looking at the correct configuration files? Is it
possible there is more than one set on the machine and the cluster you
are restarting is using a different one from the one you are working on?

--
Adrian Klaver
adrian.klaver@gmail.com