Thread: pg_hba.conf is driving me nuts

pg_hba.conf is driving me nuts

From
"Klay Martens"
Date:
Hi all,
I was not quite sure if this is the right forum for this question (if it
aint.oh well).

I have had a postgresql server up and running smoothly for 1.5 years now -
no problems at all..but:
I now need to be able to administer the server remotely, as well as allow
remote logins from a WAN.
Now, I have set up all the appropriate port forwarding, made sure that there
are no problems with firewalls on the server.but the dratted thing will not
allow any connections from a remote pgAdmin.
I am fairly sure this is a result of a problem with pf_hba.conf.Can anyone
advise how to configure pg_hba.conf to permit connections from any subnet
and ip? I know this is not really a good idea, but if the connection works,
at least then I know where the problem is! As it is, I cannot be sure
exactly where the connection failure is. Very annoying!



Re: pg_hba.conf is driving me nuts

From
Tino Schwarze
Date:
Hi Klay,

On Sat, Dec 15, 2007 at 11:11:53AM +0200, Klay Martens wrote:

> I was not quite sure if this is the right forum for this question (if it
> aint.oh well).
>
> I have had a postgresql server up and running smoothly for 1.5 years now -
> no problems at all..but:
> I now need to be able to administer the server remotely, as well as allow
> remote logins from a WAN.
> Now, I have set up all the appropriate port forwarding, made sure that there
> are no problems with firewalls on the server.but the dratted thing will not
> allow any connections from a remote pgAdmin.
> I am fairly sure this is a result of a problem with pf_hba.conf.Can anyone
> advise how to configure pg_hba.conf to permit connections from any subnet
> and ip? I know this is not really a good idea, but if the connection works,
> at least then I know where the problem is! As it is, I cannot be sure
> exactly where the connection failure is. Very annoying!

What is the exact error message when you try to connect? As stated on
top of pg_hba.conf, you may permit whole subnets access like this:

host    all         all         192.168.xxx.0/24    trust

Note: This is dangerous, replace "trust" by a method of your choice! But
it's good for testing.

Bye,

Tino.

--
www.craniosacralzentrum.de
www.lebensraum11.de
www.spiritualdesign-chemnitz.de

Tino Schwarze * Parkstraße 17h * 09120 Chemnitz

Re: pg_hba.conf is driving me nuts

From
Harald Fuchs
Date:
In article <000701c83efa$8c570b40$0100000a@klaydual>,
"Klay Martens" <kmartens@wol.co.za> writes:

> I have had a postgresql server up and running smoothly for 1.5 years now -
> no problems at all..but:
> I now need to be able to administer the server remotely, as well as allow
> remote logins from a WAN.

Did you set the appropriate listen_addresses in postgresql.conf?

Re: pg_hba.conf is driving me nuts

From
"Klay Martens"
Date:
Hi..yes, I have set the "listen_addresses" to "*"...rather an obvious first
stop...As I said - I have no problems connecting locally, or on a LAN - no
sweat at all. Simply cannot get the connection to work from another network
over a WAN. I believe the problem stems from my not setting pg_hba.conf
correctly, but I would love to test this!
This is my pg_hba.conf file from my developement box:

# TYPE DATABASE USER CIDR-ADDRESS METHOD
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all 10.0.0.0/24 md5
host all all 0.0.0.0 0.0.0.0 md5

At one stage, I even looked up the source ip address reported by aports when
I was logged onto the machine remotely..even that address did not work. So I
figure there is a problem with the subnet mask (don't realy understand the
address masks all that well - I am a programmer, not a network tekkie, so it
is kind of new territory for me). What I was wondering is if there was some
way to allow any host to connect, regardless of ip and subnet which was the
behaviour I expected from the 0.0.0.0 0.0.0.0 setting - of course it did
absolutely nothing.


Re: pg_hba.conf is driving me nuts

From
Tino Schwarze
Date:
On Sat, Dec 15, 2007 at 01:39:44PM +0200, Klay Martens wrote:

> Hi..yes, I have set the "listen_addresses" to "*"...rather an obvious first
> stop...As I said - I have no problems connecting locally, or on a LAN - no
> sweat at all. Simply cannot get the connection to work from another network
> over a WAN. I believe the problem stems from my not setting pg_hba.conf
> correctly, but I would love to test this!
> This is my pg_hba.conf file from my developement box:
>
> # TYPE DATABASE USER CIDR-ADDRESS METHOD
> # IPv4 local connections:
> host all all 127.0.0.1/32 md5
> # IPv6 local connections:
> host all all 10.0.0.0/24 md5
> host all all 0.0.0.0 0.0.0.0 md5
>
> At one stage, I even looked up the source ip address reported by aports when
> I was logged onto the machine remotely..even that address did not work. So I
> figure there is a problem with the subnet mask (don't realy understand the
> address masks all that well - I am a programmer, not a network tekkie, so it
> is kind of new territory for me). What I was wondering is if there was some
> way to allow any host to connect, regardless of ip and subnet which was the
> behaviour I expected from the 0.0.0.0 0.0.0.0 setting - of course it did
> absolutely nothing.

What does the logfile say? Does PostgreSQL get a connection at all? It
should list the IP of the connecting host and a reason.

Netmasks in short (and incomplete): IPv4 address is 32bit
(0.0.0.0-255.255.255.255), netmask says how many bits neet to match,
counted from left. E.g. netmask /8 says: first 8 bits have to match,
that is, first number must be equal. /16 says: first 16 bits have to
match - first and second number must be equal. /32 means: IP has to
match.

HTH,

Tino.

--
www.craniosacralzentrum.de
www.lebensraum11.de
www.spiritualdesign-chemnitz.de

Tino Schwarze * Parkstraße 17h * 09120 Chemnitz

Re: pg_hba.conf is driving me nuts

From
Julio Leyva
Date:
also make sure you started postgesql with -i option, so you can connect from the lan

Julioc.
----------------------------------------
> From: kmartens@wol.co.za
> To: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] pg_hba.conf is driving me nuts
> Date: Sat, 15 Dec 2007 13:39:44 +0200
>
> Hi..yes, I have set the "listen_addresses" to "*"...rather an obvious first
> stop...As I said - I have no problems connecting locally, or on a LAN - no
> sweat at all. Simply cannot get the connection to work from another network
> over a WAN. I believe the problem stems from my not setting pg_hba.conf
> correctly, but I would love to test this!
> This is my pg_hba.conf file from my developement box:
>
> # TYPE DATABASE USER CIDR-ADDRESS METHOD
> # IPv4 local connections:
> host all all 127.0.0.1/32 md5
> # IPv6 local connections:
> host all all 10.0.0.0/24 md5
> host all all 0.0.0.0 0.0.0.0 md5
>
> At one stage, I even looked up the source ip address reported by aports when
> I was logged onto the machine remotely..even that address did not work. So I
> figure there is a problem with the subnet mask (don't realy understand the
> address masks all that well - I am a programmer, not a network tekkie, so it
> is kind of new territory for me). What I was wondering is if there was some
> way to allow any host to connect, regardless of ip and subnet which was the
> behaviour I expected from the 0.0.0.0 0.0.0.0 setting - of course it did
> absolutely nothing.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

Re: pg_hba.conf is driving me nuts

From
Peter Eisentraut
Date:
Julio Leyva wrote:
> also make sure you started postgesql with -i option, so you can connect
> from the lan

He said he had set listen_addresses to '*', which is equivalent.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: pg_hba.conf is driving me nuts

From
"Usama Dar"
Date:



Now, I have set up all the appropriate port forwarding, made sure that there
are no problems with firewalls on the server.

Just to make sure there really is no issue with firewall did you try something like telnet on postgres host and port , like telnet buggy.pg.net 5432, you should get something like

telnet buggy.pg.net 5432
Trying buggy.pg.net...
Connected to buggy.pg.net.unknowndomain (some ip)
Escape character is '^]'.

i mean inorder for you to be able to directly connect to the postgres  over WAN, you will need to open the postgres port  and you should be able to remotely access it, just like you would open ports for ssh and ftp and other utilities.


I am fairly sure this is a result of a problem with pf_hba.conf.Can anyone
advise how to configure pg_hba.conf to permit connections from any subnet
and ip?

If there is no issue with firewall and the port is open the following should do it

host    all         all         0.0.0.0/0             trust

if you want to avoid opening the port for security reasons use ssh tunneling, or port forwarding something like following

ssh -L 2525:localhost:5432 user@remotehost

this will map the local 2525 port onto the remote hosts 5432 port, now you can simply connect to the postgres like a local instance

psql -h localhost -p 2525 -U postgres




--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Re: pg_hba.conf is driving me nuts

From
olivier boissard
Date:
Peter Eisentraut a écrit :
> Julio Leyva wrote:
>
>> also make sure you started postgesql with -i option, so you can connect
>> from the lan
>>
>
> He said he had set listen_addresses to '*', which is equivalent.
>
>
to allow every one :

host    all         all         0.0.0.0           0.0.0.0 trust


and set in postgresql.conf :
log_connections = on
to look if connections are opened

Are you running from a linux server ?
Sometimes we must look at /etc/hosts.deny and /etc/hosts.allow to make
sure remote hosts are not rejected.

maybe you can use nmap to see if port is open .


Olivier