Re: Remote Connection Help - Mailing list pgsql-general
| From | Mark Johnson |
|---|---|
| Subject | Re: Remote Connection Help |
| Date | |
| Msg-id | CADZ4tWNLXab538iu6seDFFr_SnszA_pLVE+M37Ej9YTYcbcMhA@mail.gmail.com Whole thread Raw |
| In response to | RE: Remote Connection Help ("Jason L. Amerson" <drjason@alphagenius.org>) |
| Responses |
RE: Remote Connection Help
("Jason L. Amerson" <drjason@alphagenius.org>)
Re: Remote Connection Help (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-general |
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:
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
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 5432tcp 0 0 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@alphagenius.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@sss.pgh.pa.us>
Sent: Thursday, November 21, 2019 01:18 PM
To: Jason L. Amerson <drjason@alphagenius.org>
Cc: 'Steve Crawford' <scrawford@pinpointresearch.com>; 'Adrian Klaver' <adrian.klaver@aklaver.com>; 'PostgreSQL' <pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help
"Jason L. Amerson" <drjason@alphagenius.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
pgsql-general by date: