Remote Connection Help

El 21/11/19 a las 15:21, Jason L. Amerson escribi�:

I am at a loss for what to do. I have read article after article about
how to allow remote connections on my PostgreSQL server and none of
what the articles say do, worked for me. I have edited the
�postgresql.conf� file and changed �listen_address = �localhost� to
listen_address = �*�. I have even tried it commented out and
uncommented and I get the same results. I also edited the
�pg_hba.conf� file and added the following at the end of the file:

host all all 0.0.0.0/0 md5

host all all ::/0 md5

For testing connection purposes I'm used to change md5 to trust, this
way you won't have troubles with passwords nor users.

After that I restart the server, try to connect remotely, and I get
nowhere. I have even added the following rules to my iptables:

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d xx.xx.xx.xx
--dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

iptables -A OUTPUT -p tcp -s xx.xx.xx.xx --sport 5432 -d 0/0 --dport
1024:65535 -m state --state ESTABLISHED -j ACCEPT

Have you tried turning of iptables service? If you turn it off and try
to connect remotely after that, you can test if the problem is in
iptables or not.

On 21/11/2019 14:30, Ekaterina Amez wrote:

El 21/11/19 a las 15:21, Jason L. Amerson escribi�:

I am at a loss for what to do. I have read article after article
about how to allow remote connections on my PostgreSQL server and
none of what the articles say do, worked for me. I have edited the
�postgresql.conf� file and changed �listen_address = �localhost� to
listen_address = �*�.

I think that's "listen_addresses" on recent versions of postgresql.

Did you stop and restart the service after you edited the config file?
Check the logs for errors too, maybe.

I have even tried it commented out and uncommented and I get the same
results. I also edited the �pg_hba.conf� file and added the following
at the end of the file:

host all all 0.0.0.0/0 md5

host all all ::/0 md5

The first line in pg_hba.conf that matches a connection will take
effect; later lines won't.

For testing connection purposes I'm used to change md5 to trust, this
way you won't have troubles with passwords nor users.

Don't do this on a machine that's reachable from the open internet,
ever. It's asking to get your box compromised.

My computer that is running the server is Ubuntu and it has a static
IP. I am trying to connect remotely with computers running Windows 10
using the static IP. When I run pgAdmin from my Windows 10 machine,
or use the command line to connect, I get the following error:

unable to connect to server:

could not connect to server: Connection refused (Ox0000274D/10061) Is
the server running on host " xx.xx.xx.xx" and accepting

TCP/IP connections on port 5432'

I would like to be able to connect to my Ubuntu PostgreSQL server
from all Windows 10 machines as well as from a client on my Android
phone while away from home. That is my goal now. I am hoping that
someone can help me to get this working. This is been very frustrating.

Can you connect to your server on it's external address at all? i.e. if
it's external IP address is 10.11.12.13, can you run "psql -h
10.11.12.13" on your ubuntu box and connect / log in?

If you can then postgresql is configured correctly and you can focus on
where the issue on the network is.

If not, then the problem is the local machine, either postgresql
configuration or _maybe_ local network configuration silliness.

Cheers,
� Steve

Steve,

I cannot connect to the server by "psql -h xx.xx.xx.xx." I can connect to my
Ubuntu machine from other computers using SSH and I can connect to
PostgreSQL if I SSH first. But I cannot connect directly to PostgreSQL
either through a client machine or if I run "psql -h xx.xx.xx.xx" while
using my Ubuntu machine.

Jason L. Amerson

From: Jason L. Amerson <drjason@alphagenius.org>
Sent: Thursday, November 21, 2019 09:22 AM
To: PostgreSQL <pgsql-general@lists.postgresql.org>
Subject: Remote Connection Help

I am at a loss for what to do. I have read article after article about how
to allow remote connections on my PostgreSQL server and none of what the
articles say do, worked for me. I have edited the "postgresql.conf" file and
changed "listen_address = 'localhost' to listen_address = '*'. I have even
tried it commented out and uncommented and I get the same results. I also
edited the "pg_hba.conf" file and added the following at the end of the
file:

host all all 0.0.0.0/0 md5

host all all ::/0 md5

After that I restart the server, try to connect remotely, and I get nowhere.
I have even added the following rules to my iptables:

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d xx.xx.xx.xx --dport
5432 -m state --state NEW,ESTABLISHED -j ACCEPT

iptables -A OUTPUT -p tcp -s xx.xx.xx.xx --sport 5432 -d 0/0 --dport
1024:65535 -m state --state ESTABLISHED -j ACCEPT

My computer that is running the server is Ubuntu and it has a static IP. I
am trying to connect remotely with computers running Windows 10 using the
static IP. When I run pgAdmin from my Windows 10 machine, or use the command
line to connect, I get the following error:

unable to connect to server:

could not connect to server: Connection refused (Ox0000274D/10061) Is the
server running on host " xx.xx.xx.xx" and accepting

TCP/IP connections on port 5432'

I would like to be able to connect to my Ubuntu PostgreSQL server from all
Windows 10 machines as well as from a client on my Android phone while away
from home. That is my goal now. I am hoping that someone can help me to get
this working. This is been very frustrating.

I cannot connect to the server by “psql -h xx.xx.xx.xx.” I can connect to my
Ubuntu machine from other computers using SSH and I can connect to
PostgreSQL if I SSH first. But I cannot connect directly to PostgreSQL
either through a client machine or if I run “psql -h xx.xx.xx.xx” while
using my Ubuntu machine.

Jason L. Amerson

From: Steve Atkins <steve@blighty.com>
Sent: Thursday, November 21, 2019 09:49 AM
To: pgsql-general@lists.postgresql.org
Subject: Re: Remote Connection Help

On 21/11/2019 14:30, Ekaterina Amez wrote:

El 21/11/19 a las 15:21, Jason L. Amerson escribió:

I am at a loss for what to do. I have read article after article about how
to allow remote connections on my PostgreSQL server and none of what the
articles say do, worked for me. I have edited the “postgresql.conf” file and
changed “listen_address = ‘localhost’ to listen_address = ‘*’.

I think that's "listen_addresses" on recent versions of postgresql.

Did you stop and restart the service after you edited the config file? Check
the logs for errors too, maybe.

I have even tried it commented out and uncommented and I get the same
results. I also edited the “pg_hba.conf” file and added the following at the
end of the file:

host all all 0.0.0.0/0 md5

host all all ::/0 md5

The first line in pg_hba.conf that matches a connection will take effect;
later lines won't.

For testing connection purposes I'm used to change md5 to trust, this way
you won't have troubles with passwords nor users.

Don't do this on a machine that's reachable from the open internet, ever.
It's asking to get your box compromised.

My computer that is running the server is Ubuntu and it has a static IP. I
am trying to connect remotely with computers running Windows 10 using the
static IP. When I run pgAdmin from my Windows 10 machine, or use the command
line to connect, I get the following error:

unable to connect to server:

could not connect to server: Connection refused (Ox0000274D/10061) Is the
server running on host " xx.xx.xx.xx" and accepting

TCP/IP connections on port 5432'

I would like to be able to connect to my Ubuntu PostgreSQL server from all
Windows 10 machines as well as from a client on my Android phone while away
from home. That is my goal now. I am hoping that someone can help me to get
this working. This is been very frustrating.

Can you connect to your server on it's external address at all? i.e. if it's
external IP address is 10.11.12.13, can you run "psql -h 10.11.12.13" on
your ubuntu box and connect / log in?

If you can then postgresql is configured correctly and you can focus on
where the issue on the network is.

If not, then the problem is the local machine, either postgresql
configuration or _maybe_ local network configuration silliness.

Cheers,
Steve

On 11/21/19 7:15 AM, Jason L. Amerson wrote:

Steve,

I cannot connect to the server by �psql -h xx.xx.xx.xx.� I can connect
to my Ubuntu machine from other computers using SSH and I can connect to
PostgreSQL if I SSH first. But I cannot connect directly to PostgreSQL
either through a client machine or if I run �psql -h xx.xx.xx.xx� while
using my Ubuntu machine.

1) Is the Postgres server listening on port 5432?

2) What is the full remote connection string you are using?

3) What is the remote machine, a hosted virtual machine, your own
physical machine, etc?

Jason L. Amerson

*From:*Jason L. Amerson <drjason@alphagenius.org>
*Sent:* Thursday, November 21, 2019 09:22 AM
*To:* PostgreSQL <pgsql-general@lists.postgresql.org>
*Subject:* Remote Connection Help

I am at a loss for what to do. I have read article after article about
how to allow remote connections on my PostgreSQL server and none of what
the articles say do, worked for me. I have edited the �postgresql.conf�
file and changed �listen_address = �localhost� to listen_address = �*�.
I have even tried it commented out and uncommented and I get the same
results. I also edited the �pg_hba.conf� file and added the following at
the end of the file:

host all all 0.0.0.0/0 md5

host all all ::/0 md5

After that I restart the server, try to connect remotely, and I get
nowhere. I have even added the following rules to my iptables:

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d xx.xx.xx.xx
--dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

iptables -A OUTPUT -p tcp -s xx.xx.xx.xx --sport 5432 -d 0/0 --dport
1024:65535 -m state --state ESTABLISHED -j ACCEPT

My computer that is running the server is Ubuntu and it has a static IP.
I am trying to connect remotely with computers running Windows 10 using
the static IP. When I run pgAdmin from my Windows 10 machine, or use the
command line to connect, I get the following error:

unable to connect to server:

could not connect to server: Connection refused (Ox0000274D/10061) Is
the server running on host " xx.xx.xx.xx" and accepting

TCP/IP connections on port 5432'

I would like to be able to connect to my Ubuntu PostgreSQL server from
all Windows 10 machines as well as from a client on my Android phone
while away from home. That is my goal now. I am hoping that someone can
help me to get this working. This is been very frustrating.

--
Adrian Klaver
adrian.klaver@aklaver.com

1) I am not sure if Postgres server is listening on port 5432. How do I
check?

2) I have tried "psql -h xx.xx.xx.xx" and "psql -h xx.xx.xx.xx -U postgres."
I even tried to telnet to it using the static IP and port 5432 but it would
not connect. I can connect to it remotely using the static IP with SSH.

3) It is my own physical machine. It is running Ubuntu and has a static IP.

Jason L. Amerson

-----Original Message-----
From: Adrian Klaver <adrian.klaver@aklaver.com>
Sent: Thursday, November 21, 2019 11:11 AM
To: Jason L. Amerson <drjason@alphagenius.org>; 'PostgreSQL'
<pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

On 11/21/19 7:15 AM, Jason L. Amerson wrote:

Steve,

I cannot connect to the server by "psql -h xx.xx.xx.xx." I can connect
to my Ubuntu machine from other computers using SSH and I can connect
to PostgreSQL if I SSH first. But I cannot connect directly to
PostgreSQL either through a client machine or if I run "psql -h
xx.xx.xx.xx" while using my Ubuntu machine.

1) Is the Postgres server listening on port 5432?

2) What is the full remote connection string you are using?

3) What is the remote machine, a hosted virtual machine, your own physical
machine, etc?

Jason L. Amerson

*From:*Jason L. Amerson <drjason@alphagenius.org>
*Sent:* Thursday, November 21, 2019 09:22 AM
*To:* PostgreSQL <pgsql-general@lists.postgresql.org>
*Subject:* Remote Connection Help

I am at a loss for what to do. I have read article after article about
how to allow remote connections on my PostgreSQL server and none of
what the articles say do, worked for me. I have edited the

"postgresql.conf"

file and changed "listen_address = 'localhost' to listen_address = '*'.
I have even tried it commented out and uncommented and I get the same
results. I also edited the "pg_hba.conf" file and added the following
at the end of the file:

host all all 0.0.0.0/0 md5

host all all ::/0 md5

After that I restart the server, try to connect remotely, and I get
nowhere. I have even added the following rules to my iptables:

iptables -A INPUT -p tcp -s 0/0 --sport 1024:65535 -d xx.xx.xx.xx
--dport 5432 -m state --state NEW,ESTABLISHED -j ACCEPT

iptables -A OUTPUT -p tcp -s xx.xx.xx.xx --sport 5432 -d 0/0 --dport
1024:65535 -m state --state ESTABLISHED -j ACCEPT

My computer that is running the server is Ubuntu and it has a static IP.
I am trying to connect remotely with computers running Windows 10
using the static IP. When I run pgAdmin from my Windows 10 machine, or
use the command line to connect, I get the following error:

unable to connect to server:

could not connect to server: Connection refused (Ox0000274D/10061) Is
the server running on host " xx.xx.xx.xx" and accepting

TCP/IP connections on port 5432'

I would like to be able to connect to my Ubuntu PostgreSQL server from
all Windows 10 machines as well as from a client on my Android phone
while away from home. That is my goal now. I am hoping that someone
can help me to get this working. This is been very frustrating.

--
Adrian Klaver
adrian.klaver@aklaver.com

I have removed the rules in the iptables and restarted it and got nothing.
iptables is turned off and still nothing.

Jason L. Amerson

From: Moreno Andreo <moreno.andreo@evolu-s.it>
Sent: Thursday, November 21, 2019 11:27 AM
To: pgsql-general@lists.postgresql.org
Subject: ***SPAM*** Re: [SPAM] Remote Connection Help

Il 21/11/19 15:21, Jason L. Amerson ha scritto:

could not connect to server: Connection refused (Ox0000274D/10061) Is the
server running on host " xx.xx.xx.xx" and accepting

TCP/IP connections on port 5432'

Connection refused means somthing has blocked it. If it was all OK and
simply Postgres was not listening, you should've received a "connection
timed out"(10060) message.
Have you tried adjusting/tearing off iptables and check what happens, as
also Ekaterina pointed out?

And, just as a side note, I normally don't activate IPv6 if it's not
necessary (it has not been necessary in the last 10 years :-) ), 'cause I've
run in some troubles that have been cleared getting rid of IPv6)
so I'll try editing postgresql.conf as
listen = '127.0.0.1'

HTH,
Moreno.-

If you just removed the iptables rules, then every port is blocked. If you
turned off iptables (service iptables stop, chkconfig iptables off,) then
iptables is disabled.

On Thu, Nov 21, 2019 at 10:50 AM Jason L. Amerson <drjason@alphagenius.org>
wrote:

I have removed the rules in the iptables and restarted it and got nothing.
iptables is turned off and still nothing.

Jason L. Amerson

*From:* Moreno Andreo <moreno.andreo@evolu-s.it>
*Sent:* Thursday, November 21, 2019 11:27 AM
*To:* pgsql-general@lists.postgresql.org
*Subject:* ***SPAM*** Re: [SPAM] Remote Connection Help

Il 21/11/19 15:21, Jason L. Amerson ha scritto:

could not connect to server: Connection refused (Ox0000274D/10061) Is the
server running on host " xx.xx.xx.xx" and accepting

TCP/IP connections on port 5432'

Connection refused means somthing has blocked it. If it was all OK and
simply Postgres was not listening, you should've received a "connection
timed out"(10060) message.
Have you tried adjusting/tearing off iptables and check what happens, as
also Ekaterina pointed out?

And, just as a side note, I normally don't activate IPv6 if it's not
necessary (it has not been necessary in the last 10 years :-) ), 'cause
I've run in some troubles that have been cleared getting rid of IPv6)
so I'll try editing postgresql.conf as
listen = '127.0.0.1'

HTH,
Moreno.-

--
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

On Thu, Nov 21, 2019 at 8:48 AM Jason L. Amerson <drjason@alphagenius.org>
wrote:

1) I am not sure if Postgres server is listening on port 5432. How do I
check?

2) I have tried "psql -h xx.xx.xx.xx" and "psql -h xx.xx.xx.xx -U
postgres."
I even tried to telnet to it using the static IP and port 5432 but it would
not connect. I can connect to it remotely using the static IP with SSH.

3) It is my own physical machine. It is running Ubuntu and has a static IP.

Jason L. Amerson

....

Can you connect locally? I.e. on the machine running PostgreSQL? If so, you
can run:

show listen_addresses;

and

show port;

to verify the settings. On some distros (including Ubuntu) you can have
PostgreSQL running on a non-standard port due to an upgrade or installation
of multiple versions.

Other thoughts. Did you restart PostgreSQL after changing settings? Are you
sure that you are editing the postgresql.conf file associated with your
running instance? Is there anything on the *client* machine or between the
client machine and your PostgreSQL server that could be blocking ports?
Have you used netstat or lsof to verify that PostgreSQL is listening on
5432?

Cheers,
Steve

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.”

Jason L. Amerson

From: Steve Crawford <scrawford@pinpointresearch.com>
Sent: Thursday, November 21, 2019 12:05 PM
To: Jason L. Amerson <drjason@alphagenius.org>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>; PostgreSQL <pgsql-general@lists.postgresql.org>
Subject: Re: Remote Connection Help

On Thu, Nov 21, 2019 at 8:48 AM Jason L. Amerson <drjason@alphagenius.org <mailto:drjason@alphagenius.org> > wrote:

1) I am not sure if Postgres server is listening on port 5432. How do I
check?

2) I have tried "psql -h xx.xx.xx.xx" and "psql -h xx.xx.xx.xx -U postgres."
I even tried to telnet to it using the static IP and port 5432 but it would
not connect. I can connect to it remotely using the static IP with SSH.

3) It is my own physical machine. It is running Ubuntu and has a static IP.

Jason L. Amerson

....

Can you connect locally? I.e. on the machine running PostgreSQL? If so, you can run:

show listen_addresses;

and

show port;

to verify the settings. On some distros (including Ubuntu) you can have PostgreSQL running on a non-standard port due to an upgrade or installation of multiple versions.

Other thoughts. Did you restart PostgreSQL after changing settings? Are you sure that you are editing the postgresql.conf file associated with your running instance? Is there anything on the *client* machine or between the client machine and your PostgreSQL server that could be blocking ports? Have you used netstat or lsof to verify that PostgreSQL is listening on 5432?

Cheers,

Steve

"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

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

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 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:

"Jason L. Amerson" <drjason@alphagenius.org> writes:

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?

Hm, well, *something* is overriding the setting. What did you
find in pg_settings?

regards, tom lane

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@gmail.com>
Sent: Thursday, November 21, 2019 02:02 PM
To: Jason L. Amerson <drjason@alphagenius.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Steve Crawford <scrawford@pinpointresearch.com>; Adrian Klaver <adrian.klaver@aklaver.com>; PostgreSQL <pgsql-general@lists.postgresql.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&gt; 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 <mailto: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 <mailto:tgl@sss.pgh.pa.us> >
Sent: Thursday, November 21, 2019 01:18 PM
To: Jason L. Amerson <drjason@alphagenius.org <mailto:drjason@alphagenius.org> >
Cc: 'Steve Crawford' <scrawford@pinpointresearch.com <mailto:scrawford@pinpointresearch.com> >; 'Adrian Klaver' <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> >; 'PostgreSQL' <pgsql-general@lists.postgresql.org <mailto:pgsql-general@lists.postgresql.org> >
Subject: Re: Remote Connection Help

"Jason L. Amerson" <drjason@alphagenius.org <mailto: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

Mark Johnson <remi9898@gmail.com> writes:

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.

No, the contents of pg_hba.conf don't directly impact the listen_addresses
setting. Also, if that's where the problem is, I'd expect a failure
complaining about "no pg_hba.conf entry for <connection info>". The
reported "connection refused" message suggests strongly that the
postmaster isn't actually listening on the desired port, which also
implicates listen_addresses rather than anything else. (I think it
could also be caused by a firewall filter, if the firewall is configured
to send back a TCP RST rather than just drop the packet. But if "show
listen_addresses" isn't showing what we expect, that's the first thing
to fix.)

The OP may well need to adjust pg_hba.conf too, but he's not got
that far yet :-(

regards, tom lane

pg_settings still show localhost. I went back and added the line that
someone 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 be-fore, 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
some-thing very screwy is going on.

Jason L. Amerson

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

Mark Johnson <remi9898@gmail.com> writes:

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.

No, the contents of pg_hba.conf don't directly impact the listen_addresses
setting. Also, if that's where the problem is, I'd expect a failure
complaining about "no pg_hba.conf entry for <connection info>". The
reported "connection refused" message suggests strongly that the postmaster
isn't actually listening on the desired port, which also implicates
listen_addresses rather than anything else. (I think it could also be
caused by a firewall filter, if the firewall is configured to send back a
TCP RST rather than just drop the packet. But if "show listen_addresses"
isn't showing what we expect, that's the first thing to fix.)

The OP may well need to adjust pg_hba.conf too, but he's not got that far
yet :-(

regards, tom lane

pg_settings show localhost.

Jason L. Amerson

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, November 21, 2019 02:42 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:

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?

Hm, well, *something* is overriding the setting. What did you find in
pg_settings?

regards, tom lane