Thread: Remote connection issues
Hi list,
I have installed postgresql 8.4 on Ubuntu server 10.4. I would like to have
remote access to this database so after reading I found out that modifying
pg_hba.conf and postgresql.conf will allow me to access remotely.
The postgresql database is on 192.168.2.122. The port 5432 is open, checked it
with nmap -p1-65535 localhost. The server is comunicating with other pcs from
the subnet, I can connect to it through ssh.
Here is what I have done:
I enabled the following on the postgresql.conf file:
listen_addresses = '*'
port = 5432
My subnet is under 192.168.2.xxx so I added the following to the pg_hba.conf:
host all all 192.168.2.0/32 trust
I have also tried adding the IP of the pc which I'm connecting:
host all all 192.168.2.20/32 trust
and also like this:
host all all 192.168.2.20 255.255.255.0 trust
After doing all this things, if I try to connect remotely I got a connection
refused error.
psql -h 192.168.2.122 -d database
psql: could not connect to server: Connection refused
Is the server running on host "192.168.2.122" and accepting
TCP/IP connections on port 5432?
Another strange thing is the following one, if I do the following on the
database server: psql -h localhost -d database, I grant access but if I do the
following psql -h 192.168.2.122 -d database on the database server, I got a
connection refused error.
Any idea on what I have miss or did wrong?
Cheers,
Aritz Davila
www.axios.es
On 23/06/2011 08:24, Aritz Dávila wrote: > Hi list, > > I have installed postgresql 8.4 on Ubuntu server 10.4. I would like to have > remote access to this database so after reading I found out that modifying > pg_hba.conf and postgresql.conf will allow me to access remotely. > > The postgresql database is on 192.168.2.122. The port 5432 is open, checked it > with nmap -p1-65535 localhost. The server is comunicating with other pcs from > the subnet, I can connect to it through ssh. > > Here is what I have done: > I enabled the following on the postgresql.conf file: > listen_addresses = '*' > port = 5432 > > My subnet is under 192.168.2.xxx so I added the following to the pg_hba.conf: > host all all 192.168.2.0/32 trust Should that be /24? Mind you, that gives unchallenged access to the server from all hosts on that subnet, so maybe this isn't what you want. However, bear in mind that sequence is important in pg_hba.conf, so the first matching rule is what will be enforced. > After doing all this things, if I try to connect remotely I got a connection > refused error. > psql -h 192.168.2.122 -d database > psql: could not connect to server: Connection refused > Is the server running on host "192.168.2.122" and accepting > TCP/IP connections on port 5432? Having said all the above, this doesn't look like a pg_hba.conf error; a refusal based on that file says so explicitly in the error message. Could it be a firewall or iptables problem? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 6/23/11 3:24:12 AM, Aritz Dávila wrote: > Hi list, > > I have installed postgresql 8.4 on Ubuntu server 10.4. I would like to have > remote access to this database so after reading I found out that modifying > pg_hba.conf and postgresql.conf will allow me to access remotely. > > The postgresql database is on 192.168.2.122. The port 5432 is open, checked it > with nmap -p1-65535 localhost. The server is comunicating with other pcs from > the subnet, I can connect to it through ssh. > > Here is what I have done: > I enabled the following on the postgresql.conf file: > listen_addresses = '*' > port = 5432 > > My subnet is under 192.168.2.xxx so I added the following to the pg_hba.conf: > host all all 192.168.2.0/32 trust Ditto Raymond that you probably mean /24 here. > After doing all this things, if I try to connect remotely I got a connection > refused error. > psql -h 192.168.2.122 -d database > psql: could not connect to server: Connection refused > Is the server running on host "192.168.2.122" and accepting > TCP/IP connections on port 5432? > > Another strange thing is the following one, if I do the following on the > database server: psql -h localhost -d database, I grant access but if I do the > following psql -h 192.168.2.122 -d database on the database server, I got a > connection refused error. Given that this is Linux, I would guess that there's some SELinux stuff enabled by default that's disallowing the connection, and that it really doesn't have anything to do with PostgreSQL. I've had personal frustrations (and watched many others as well) with SELinux default configs that tend to deny lots of access by default and not really log anything telling you that they're denying it. Could also be a firewall rule or any other OS mechanism that limits/ controls access through IP. With -h localhost, you're probably connecting through the unix domain socket, which isn't controlled by any firewall I'm aware of, and seems to be ignored as always safe to allow by most SELinux configs. May want to consider disabling SELinux altogether (even if only as a temporary debugging step) and see if things start to work. -- Bill Moran
On Thursday, June 23, 2011 12:24:12 am Aritz Dávila wrote: > Hi list, > > I have installed postgresql 8.4 on Ubuntu server 10.4. I would like to have > remote access to this database so after reading I found out that modifying > pg_hba.conf and postgresql.conf will allow me to access remotely. > > The postgresql database is on 192.168.2.122. The port 5432 is open, checked > it with nmap -p1-65535 localhost. The server is comunicating with other > pcs from the subnet, I can connect to it through ssh. > > Here is what I have done: > I enabled the following on the postgresql.conf file: > listen_addresses = '*' > port = 5432 > > My subnet is under 192.168.2.xxx so I added the following to the > pg_hba.conf: host all all 192.168.2.0/32 trust > > I have also tried adding the IP of the pc which I'm connecting: > host all all 192.168.2.20/32 trust > > and also like this: > host all all 192.168.2.20 255.255.255.0 trust > > After doing all this things, if I try to connect remotely I got a > connection refused error. > psql -h 192.168.2.122 -d database > psql: could not connect to server: Connection refused > Is the server running on host "192.168.2.122" and accepting > TCP/IP connections on port 5432? > > Another strange thing is the following one, if I do the following on the > database server: psql -h localhost -d database, I grant access but if I do > the following psql -h 192.168.2.122 -d database on the database server, I > got a connection refused error. > > Any idea on what I have miss or did wrong? Did you do a pg_ctl reload, or in the case of listen_addresses pg_ctl restart? > > Cheers, > > Aritz Davila > www.axios.es -- Adrian Klaver adrian.klaver@gmail.com
On Thursday, June 23, 2011 12:24:12 am Aritz Dávila wrote: > Hi list, > > > Another strange thing is the following one, if I do the following on the > database server: psql -h localhost -d database, I grant access but if I do > the following psql -h 192.168.2.122 -d database on the database server, I > got a connection refused error. > Meant to add this to my previous reply. Try: psql -h 127.0.0.1 -d database to reach the localhost. > > Cheers, > > Aritz Davila > www.axios.es -- Adrian Klaver adrian.klaver@gmail.com
On Thursday, June 23, 2011 02:46:52 pm you wrote: > On 6/23/11 3:24:12 AM, Aritz Dávila wrote: > > Hi list, > > > > I have installed postgresql 8.4 on Ubuntu server 10.4. I would like to > > have remote access to this database so after reading I found out that > > modifying pg_hba.conf and postgresql.conf will allow me to access > > remotely. > > > > The postgresql database is on 192.168.2.122. The port 5432 is open, > > checked it with nmap -p1-65535 localhost. The server is comunicating > > with other pcs from the subnet, I can connect to it through ssh. > > > > Here is what I have done: > > I enabled the following on the postgresql.conf file: > > listen_addresses = '*' > > port = 5432 > > > > My subnet is under 192.168.2.xxx so I added the following to the > > pg_hba.conf: host all all 192.168.2.0/32 trust > > Ditto Raymond that you probably mean /24 here. > > > After doing all this things, if I try to connect remotely I got a > > connection refused error. > > psql -h 192.168.2.122 -d database > > psql: could not connect to server: Connection refused > > > > Is the server running on host "192.168.2.122" and accepting > > TCP/IP connections on port 5432? > > > > Another strange thing is the following one, if I do the following on the > > database server: psql -h localhost -d database, I grant access but if I > > do the following psql -h 192.168.2.122 -d database on the database > > server, I got a connection refused error. > > Given that this is Linux, I would guess that there's some SELinux stuff > enabled by default that's disallowing the connection, and that it really > doesn't have anything to do with PostgreSQL. I've had personal > frustrations (and watched many others as well) with SELinux default > configs that tend to deny lots of access by default and not really > log anything telling you that they're denying it. > > Could also be a firewall rule or any other OS mechanism that limits/ > controls access through IP. With -h localhost, you're probably > connecting through the unix domain socket, which isn't controlled > by any firewall I'm aware of, and seems to be ignored as always > safe to allow by most SELinux configs. > > May want to consider disabling SELinux altogether (even if only as > a temporary debugging step) and see if things start to work. Thx for the answers, I'll take a look at the linux systems because as I though postgre configuration seems ok. Cheers