Обсуждение: 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?
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.
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.
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,
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.htmlSent from the PostgreSQL - general mailing list archive at Nabble.com.
--Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
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
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.
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 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.
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.
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
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.klaver@gmail.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.