RE: Remote Connection Help

I went back and added the line you suggested to my “pg_hba” file so the end of mine now looks like this:

host all all 0.0.0.0/0 md5

host all all ::1/128 md5

When I run “netstat -nlt | grep 5432”, I still only get “tcp 127.0.0.1:5432.” As I mentioned before, I also see "127.0.0.1" on various ports including 5432 but I have a listing for tcp6 that has my static IP using port 32305. Is it supposed to be like that? Also, her is the weird thing, I have two “postgresql.conf” and “pg_hba” files in two different locations. I have one in “/usr/local/pgsql/data” and another set at “/etc/postgresql/9.4/main.” I just discovered this situation. I edited both sets of files to have the same setting and still nothing. It seems that something very screwy is going on.

Jason L. Amerson

From: Mark Johnson <remi9898(at)gmail(dot)com>
Sent: Thursday, November 21, 2019 02:02 PM
To: Jason L. Amerson <drjason(at)alphagenius(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; Steve Crawford <scrawford(at)pinpointresearch(dot)com>; Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>; PostgreSQL <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Remote Connection Help

As I recall, if the listening address is set to '*' but is showing localhost, then the problem you describe is likely due to missing an IPv6 address in pg_hba.conf. For me, I just added a line to pg_hba.conf like this:

host all all ::1/128 md5

So, even though my client app is on the db server and the connection string has an IPv4 address the connection request still gets to PostgreSQL as IPv6 and fails until I added the line shown above.

Did your netstat output have two lines for the port numbers used by PostgreSQL or just one of them? My computer has two like this,

$ netstat -nlt | grep 5432
tcp 0 0 127.0.0.1:5432 <http://127.0.0.1:5432> 0.0.0.0:* LISTEN

tcp6 0 0 ::1:5432 :::* LISTEN

On Thu, Nov 21, 2019 at 1:41 PM Jason L. Amerson <drjason(at)alphagenius(dot)org <mailto:drjason(at)alphagenius(dot)org> > wrote:

Yes "listen_addresses" is not commented. I did notice when I did the netstat, for tcp, it was all "127.0.0.1" on various ports including 5432 but I have a listing for tcp6 that has my static IP using port 32305. Would that make a difference?

Jason L. Amerson

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us> >
Sent: Thursday, November 21, 2019 01:18 PM
To: Jason L. Amerson <drjason(at)alphagenius(dot)org <mailto:drjason(at)alphagenius(dot)org> >
Cc: 'Steve Crawford' <scrawford(at)pinpointresearch(dot)com <mailto:scrawford(at)pinpointresearch(dot)com> >; 'Adrian Klaver' <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com> >; 'PostgreSQL' <pgsql-general(at)lists(dot)postgresql(dot)org <mailto:pgsql-general(at)lists(dot)postgresql(dot)org> >
Subject: Re: Remote Connection Help

"Jason L. Amerson" <drjason(at)alphagenius(dot)org <mailto:drjason(at)alphagenius(dot)org> > writes:
> I connected to PostgreSQL locally. I ran “show listen_addresses;” and it returned “localhost.” I ran “show port;” and it returned “5432.” I am now confused. I edited the “postgresql.conf” file and change the setting to ‘*’. Then I restarted the server with “service postgresql restart.” I was in root since I had to edit the config files. I thought maybe I edited the wrong file, like maybe there were two in two different locations or something. I ran “show confg_file;” and it returned “/usr/local/psql/data/postgresql.conf.” That is the same file I edited from the start. To be sure, I edited the file by using “nano /usr/local/psql/data/postgresql.conf.” I went down and found that I did have it as “listen_addresses = ‘*’ yet when I run “show listen_addresses”, it shows “localhost.” I am confused. When I run “netstat -nlt”, the results show that it is listening to “127.0.0.1:5432.”

According to what you wrote here, you did everything right, so it's something you failed to mention.

One thing I'm wondering is whether you removed the comment symbol (#) from the listen_addresses line when you edited it. As installed, postgresql.conf is pretty much all comments.

You might get more insight from

select * from pg_settings where name = 'listen_addresses';

particularly the source, sourcefile, sourceline fields.

regards, tom lane