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

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.

What am I missing?

Thanks,

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

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(dot)klaver(at)gmail(dot)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(dot)klaver(at)gmail(dot)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(at)hotmail(dot)com>wrote:

> 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.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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(dot)klaver(at)gmail(dot)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(at)iol(dot)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(dot)klaver(at)gmail(dot)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(at)iol(dot)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(dot)klaver(at)gmail(dot)com

On 03/15/11 11:26 AM, Raymond O'Donnell wrote:
> 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?

he indicated he did, via 'svcadm restart' (the Solaris 10 service manager)

I wonder if he's editing the *right* postgresql.conf, there's the
possibility on Solaris of having more than one instance installed.

Guys,

Thanks for your help.

When I saw that netstat was not listening as *.5432 I decided to;

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

Then;

pg_ctl -D /var/lib/pgsql/data -l postmaster.log start

This fixed the problem.

You guys were right on the money, looks like restarting via smf was not
picking up the config changes. So I guess they need to be made elsewhere in
Solaris.

I can now talk to my PG DB via a remote host.

Big thanks the help.

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

On Tuesday, March 15, 2011 1:33:48 pm general_lee wrote:
> Guys,
>
> Thanks for your help.
>
> When I saw that netstat was not listening as *.5432 I decided to;
>
> svcadm disable svc:/application/database/postgresql_83:default_64bit
>
> Then;
>
> pg_ctl -D /var/lib/pgsql/data -l postmaster.log start
>
> This fixed the problem.
>
> You guys were right on the money, looks like restarting via smf was not
> picking up the config changes. So I guess they need to be made elsewhere in
> Solaris.

What it probably means is that this:

svc:/application/database/postgresql_83:default_64bit

is pointing at a different instance of Postgres than this:

pg_ctl -D /var/lib/pgsql/data -l postmaster.log start

My guess is if you search for postgresql.conf and pg_hba.conf you will find more
than one copy of each and that the copies that are not in /var/lib/pgsql/data
are the ones being used for the svc start.

>
> I can now talk to my PG DB via a remote host.
>
> Big thanks the help.
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

> What it probably means is that this:
>
> svc:/application/database/postgresql_83:default_64bit
>
> is pointing at a different instance of Postgres than this:
>
> pg_ctl -D /var/lib/pgsql/data -l postmaster.log start

indeed.

# svcprop svc:/application/database/postgresql_83:default_64bit | grep data
/var/postgres/8.3/data_64

so, yeah, wrong place entirely. god knows what mess you have now.

# svcprop svc:/application/database/postgresql:version_81 | grep data
postgresql/data astring /var/lib/pgsql/data

hmmmmm.