Remote Connection Help

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
39 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Remote Connection Help

Jason L. Amerson

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.

 

Reply | Threaded
Open this post in threaded view
|

Re: Remote Connection Help

Ekaterina Amez
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.


 

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.

 

Reply | Threaded
Open this post in threaded view
|

Re: Remote Connection Help

Steve Atkins


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


Reply | Threaded
Open this post in threaded view
|

RE: Remote Connection Help

Jason L. Amerson
In reply to this post by Jason L. Amerson

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 <[hidden email]>
Sent: Thursday, November 21, 2019 09:22 AM
To: PostgreSQL <[hidden email]>
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.

 

Reply | Threaded
Open this post in threaded view
|

RE: Remote Connection Help

Jason L. Amerson
In reply to this post by Steve Atkins

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 <[hidden email]>
Sent: Thursday, November 21, 2019 09:49 AM
To: [hidden email]
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

 

Reply | Threaded
Open this post in threaded view
|

Re: Remote Connection Help

Adrian Klaver-4
In reply to this post by Jason L. Amerson
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 <[hidden email]>
> *Sent:* Thursday, November 21, 2019 09:22 AM
> *To:* PostgreSQL <[hidden email]>
> *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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

Re: [SPAM] Remote Connection Help

Moreno Andreo
In reply to this post by Jason L. Amerson
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.-
Reply | Threaded
Open this post in threaded view
|

RE: Remote Connection Help

Jason L. Amerson
In reply to this post by Adrian Klaver-4
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 <[hidden email]>
Sent: Thursday, November 21, 2019 11:11 AM
To: Jason L. Amerson <[hidden email]>; 'PostgreSQL'
<[hidden email]>
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 <[hidden email]>
> *Sent:* Thursday, November 21, 2019 09:22 AM
> *To:* PostgreSQL <[hidden email]>
> *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
[hidden email]





Reply | Threaded
Open this post in threaded view
|

RE: ***SPAM*** Re: [SPAM] Remote Connection Help

Jason L. Amerson
In reply to this post by Moreno Andreo

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 <[hidden email]>
Sent: Thursday, November 21, 2019 11:27 AM
To: [hidden email]
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.-

Reply | Threaded
Open this post in threaded view
|

Re: ***SPAM*** Re: [SPAM] Remote Connection Help

Andrew Kerber
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 <[hidden email]> 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 <[hidden email]>
Sent: Thursday, November 21, 2019 11:27 AM
To: [hidden email]
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.'
Reply | Threaded
Open this post in threaded view
|

Re: Remote Connection Help

Steve Crawford
In reply to this post by Jason L. Amerson


On Thu, Nov 21, 2019 at 8:48 AM Jason L. Amerson <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

RE: Remote Connection Help

Jason L. Amerson

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 <[hidden email]>
Sent: Thursday, November 21, 2019 12:05 PM
To: Jason L. Amerson <[hidden email]>
Cc: Adrian Klaver <[hidden email]>; PostgreSQL <[hidden email]>
Subject: Re: Remote Connection Help

 

 

 

On Thu, Nov 21, 2019 at 8:48 AM Jason L. Amerson <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Remote Connection Help

Tom Lane-2
"Jason L. Amerson" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

RE: Remote Connection Help

Jason L. Amerson
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 <[hidden email]>
Sent: Thursday, November 21, 2019 01:18 PM
To: Jason L. Amerson <[hidden email]>
Cc: 'Steve Crawford' <[hidden email]>; 'Adrian Klaver' <[hidden email]>; 'PostgreSQL' <[hidden email]>
Subject: Re: Remote Connection Help

"Jason L. Amerson" <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

Re: Remote Connection Help

Mark Johnson-2
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 <[hidden email]> 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 <[hidden email]>
Sent: Thursday, November 21, 2019 01:18 PM
To: Jason L. Amerson <[hidden email]>
Cc: 'Steve Crawford' <[hidden email]>; 'Adrian Klaver' <[hidden email]>; 'PostgreSQL' <[hidden email]>
Subject: Re: Remote Connection Help

"Jason L. Amerson" <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

Re: Remote Connection Help

Tom Lane-2
In reply to this post by Jason L. Amerson
"Jason L. Amerson" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

RE: Remote Connection Help

Jason L. Amerson
In reply to this post by Mark Johnson-2

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 <[hidden email]>
Sent: Thursday, November 21, 2019 02:02 PM
To: Jason L. Amerson <[hidden email]>
Cc: Tom Lane <[hidden email]>; Steve Crawford <[hidden email]>; Adrian Klaver <[hidden email]>; PostgreSQL <[hidden email]>
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          0.0.0.0:*               LISTEN

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

 

 

 

On Thu, Nov 21, 2019 at 1:41 PM Jason L. Amerson <[hidden email]> 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 <[hidden email]>
Sent: Thursday, November 21, 2019 01:18 PM
To: Jason L. Amerson <[hidden email]>
Cc: 'Steve Crawford' <[hidden email]>; 'Adrian Klaver' <[hidden email]>; 'PostgreSQL' <[hidden email]>
Subject: Re: Remote Connection Help

"Jason L. Amerson" <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Remote Connection Help

Tom Lane-2
In reply to this post by Mark Johnson-2
Mark Johnson <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

RE: Remote Connection Help

Jason L. Amerson
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 <[hidden email]>
Sent: Thursday, November 21, 2019 02:53 PM
To: Mark Johnson <[hidden email]>
Cc: Jason L. Amerson <[hidden email]>; Steve Crawford
<[hidden email]>; Adrian Klaver <[hidden email]>;
PostgreSQL <[hidden email]>
Subject: Re: Remote Connection Help

Mark Johnson <[hidden email]> 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



Reply | Threaded
Open this post in threaded view
|

RE: Remote Connection Help

Jason L. Amerson
In reply to this post by Tom Lane-2
pg_settings show localhost.

Jason L. Amerson


-----Original Message-----
From: Tom Lane <[hidden email]>
Sent: Thursday, November 21, 2019 02:42 PM
To: Jason L. Amerson <[hidden email]>
Cc: 'Steve Crawford' <[hidden email]>; 'Adrian Klaver'
<[hidden email]>; 'PostgreSQL'
<[hidden email]>
Subject: Re: Remote Connection Help

"Jason L. Amerson" <[hidden email]> 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





12