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@xxxxxxxxx>
Sent: Thursday, November 21, 2019 02:02 PM
To: Jason L. Amerson <drjason@xxxxxxxxxxxxxxx>
Cc: Tom Lane <tgl@xxxxxxxxxxxxx>; Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx>; Adrian Klaver <adrian.klaver@xxxxxxxxxxx>; PostgreSQL <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
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: 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 0.0.0.0:* LISTEN tcp6 0 0 ::1:5432 :::* LISTEN 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@xxxxxxxxxxxxx>
Sent: Thursday, November 21, 2019 01:18 PM
To: Jason L. Amerson <drjason@xxxxxxxxxxxxxxx>
Cc: 'Steve Crawford' <scrawford@xxxxxxxxxxxxxxxxxxxx>; 'Adrian Klaver' <adrian.klaver@xxxxxxxxxxx>; 'PostgreSQL' <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Remote Connection Help
"Jason L. Amerson" <drjason@xxxxxxxxxxxxxxx> 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
|
