Обсуждение: Cannot connect to postgresql on port 5432

Поиск
Список
Период
Сортировка

Cannot connect to postgresql on port 5432

От
Дата:

Hi Experts,

 

Post installation of  PostgreSQL it is not listening on an IP, only on localhost.

 

Please suggest your inputs.

 

 

Used below command for installations –

 

# apt-get install postgresql-12

postgres=# select version();

                                                     version

------------------------------------------------------------------------------------------------------------------

PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

(1 row)

 

Entries in ::: /etc/postgresql/12/main/pg_hba.conf

# Database administrative login by Unix domain socket

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

local   all             all                                     peer

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

local   replication     all                                     peer

host    replication     all             127.0.0.1/32            md5

host    replication     all             ::1/128                 md5

 

Not connected

 

postgres@srv:/opt$ netstat -nlp | grep 5432

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      25857/postgres

unix  2      [ ACC ]     STREAM     LISTENING     74234    25857/postgres       /var/run/postgresql/.s.PGSQL.5432

 

 

postgres@srv:~$ telnet 10.133.214.32 5432

Trying 10.133.214.32...

telnet: Unable to connect to remote host: Connection refused

 

 

 

Re: [MASSMAIL]Cannot connect to postgresql on port 5432

От
gilberto.castillo@etecsa.cu
Дата:
Hi

You might change postgresl.conf the line listening = '*'

El 2019-12-23 14:30, soumik.bhattacharjee@kpn.com escribió:
> Hi Experts,
> 
> Post installation of  PostgreSQL it is not listening on an IP, only on
> localhost.
> 
> Please suggest your inputs.
> 
> USED BELOW COMMAND FOR INSTALLATIONS –
> 
> # APT-GET INSTALL POSTGRESQL-12
> 
> postgres=# select version();
> 
>                                                      version
> 
> ------------------------------------------------------------------------------------------------------------------
> 
> 
> PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
> 
> (1 row)
> 
> ENTRIES IN ::: /ETC/POSTGRESQL/12/MAIN/PG_HBA.CONF
> 
> # Database administrative login by Unix domain socket
> 
> local   all             postgres                                peer
> 
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
> 
> 
> # "local" is for Unix domain socket connections only
> 
> local   all             all                                     peer
> 
> # IPv4 local connections:
> 
> host    all             all             127.0.0.1/32            md5
> 
> # IPv6 local connections:
> 
> host    all             all             ::1/128                 md5
> 
> # Allow replication connections from localhost, by a user with the
> 
> # replication privilege.
> 
> local   replication     all                                     peer
> 
> host    replication     all             127.0.0.1/32            md5
> 
> host    replication     all             ::1/128                 md5
> 
> NOT CONNECTED
> 
> POSTGRES@SRV:/OPT$ NETSTAT -NLP | GREP 5432
> 
> (Not all processes could be identified, non-owned process info
> 
> will not be shown, you would have to be root to see it all.)
> 
> tcp        0      0 127.0.0.1:5432          0.0.0.0:*
> LISTEN      25857/postgres
> 
> unix  2      [ ACC ]     STREAM     LISTENING     74234
> 25857/postgres       /var/run/postgresql/.s.PGSQL.5432
> 
> POSTGRES@SRV:~$ TELNET 10.133.214.32 5432
> 
> Trying 10.133.214.32...
> 
> telnet: Unable to connect to remote host: Connection refused

Re: Cannot connect to postgresql on port 5432

От
Ron
Дата:

What's the value of listen_addresses?

postgres=# show listen_addresses;
 listen_addresses
------------------
 localhost
(1 row)


You probably need to edit postgresql.conf and change listen_addresses from 'localhost' to the IP addresses they need to listen to.

And, of course, make sure the firewall allows bi-directional port 5432 between the relevant hosts.

On 12/23/19 1:30 PM, soumik.bhattacharjee@kpn.com wrote:

Hi Experts,

 

Post installation of  PostgreSQL it is not listening on an IP, only on localhost.

 

Please suggest your inputs.

 

 

Used below command for installations –

 

# apt-get install postgresql-12

postgres=# select version();

                                                     version

------------------------------------------------------------------------------------------------------------------

PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

(1 row)

 

Entries in ::: /etc/postgresql/12/main/pg_hba.conf

# Database administrative login by Unix domain socket

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

local   all             all                                     peer

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

local   replication     all                                     peer

host    replication     all             127.0.0.1/32            md5

host    replication     all             ::1/128                 md5

 

Not connected

 

postgres@srv:/opt$ netstat -nlp | grep 5432

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      25857/postgres

unix  2      [ ACC ]     STREAM     LISTENING     74234    25857/postgres       /var/run/postgresql/.s.PGSQL.5432

 

 

postgres@srv:~$ telnet 10.133.214.32 5432

Trying 10.133.214.32...

telnet: Unable to connect to remote host: Connection refused

 

 

 


--
Angular momentum makes the world go 'round.

Re: Cannot connect to postgresql on port 5432

От
Pepe TD Vo
Дата:
change md5 to trust and try to connect again

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Monday, December 23, 2019, 02:30:35 PM EST, <soumik.bhattacharjee@kpn.com> wrote:


Hi Experts,

 

Post installation of  PostgreSQL it is not listening on an IP, only on localhost.

 

Please suggest your inputs.

 

 

Used below command for installations –

 

# apt-get install postgresql-12

postgres=# select version();

                                                     version

------------------------------------------------------------------------------------------------------------------

PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

(1 row)

 

Entries in ::: /etc/postgresql/12/main/pg_hba.conf

# Database administrative login by Unix domain socket

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

local   all             all                                     peer

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

local   replication     all                                     peer

host    replication     all             127.0.0.1/32            md5

host    replication     all             ::1/128                 md5

 

Not connected

 

postgres@srv:/opt$ netstat -nlp | grep 5432

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      25857/postgres

unix  2      [ ACC ]     STREAM     LISTENING     74234    25857/postgres       /var/run/postgresql/.s.PGSQL.5432

 

 

postgres@srv:~$ telnet 10.133.214.32 5432

Trying 10.133.214.32...

telnet: Unable to connect to remote host: Connection refused

 

 

 

Re: Cannot connect to postgresql on port 5432

От
Ron
Дата:
That error won't happen until after a connection is accepted.  And will throw a different error message.

On 12/23/19 1:39 PM, Pepe TD Vo wrote:
change md5 to trust and try to connect again

Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.
To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)

**Live simply **Love generously **Care deeply **Speak kindly.
*** Genuinely rich *** Faithful talent *** Sharing success


On Monday, December 23, 2019, 02:30:35 PM EST, <soumik.bhattacharjee@kpn.com> wrote:


Hi Experts,

 

Post installation of  PostgreSQL it is not listening on an IP, only on localhost.

 

Please suggest your inputs.

 

 

Used below command for installations –

 

# apt-get install postgresql-12

postgres=# select version();

                                                     version

------------------------------------------------------------------------------------------------------------------

PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

(1 row)

 

Entries in ::: /etc/postgresql/12/main/pg_hba.conf

# Database administrative login by Unix domain socket

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only

local   all             all                                     peer

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

local   replication     all                                     peer

host    replication     all             127.0.0.1/32            md5

host    replication     all             ::1/128                 md5

 

Not connected

 

postgres@srv:/opt$ netstat -nlp | grep 5432

(Not all processes could be identified, non-owned process info

will not be shown, you would have to be root to see it all.)

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      25857/postgres

unix  2      [ ACC ]     STREAM     LISTENING     74234    25857/postgres       /var/run/postgresql/.s.PGSQL.5432

 

 

postgres@srv:~$ telnet 10.133.214.32 5432

Trying 10.133.214.32...

telnet: Unable to connect to remote host: Connection refused

 

 

 


--
Angular momentum makes the world go 'round.

RE: [MASSMAIL]Cannot connect to postgresql on port 5432

От
Дата:
Thanks Gilberto, yes I did that, but won't this be a security issue?

I tried this below in pg_hba.conf , but no help.

host all all 0.0.0.0/0 md5

So I think listen_addresess ='*' is the only way out.

Thanks..


-----Original Message-----
From: gilberto.castillo@etecsa.cu <gilberto.castillo@etecsa.cu> 
Sent: maandag 23 december 2019 20:34
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
Cc: pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [MASSMAIL]Cannot connect to postgresql on port 5432
Importance: High

Hi

You might change postgresl.conf the line listening = '*'

El 2019-12-23 14:30, soumik.bhattacharjee@kpn.com escribió:
> Hi Experts,
> 
> Post installation of  PostgreSQL it is not listening on an IP, only on 
> localhost.
> 
> Please suggest your inputs.
> 
> USED BELOW COMMAND FOR INSTALLATIONS –
> 
> # APT-GET INSTALL POSTGRESQL-12
> 
> postgres=# select version();
> 
>                                                      version
> 
> ----------------------------------------------------------------------
> --------------------------------------------
> 
> 
> PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, 
> compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
> 
> (1 row)
> 
> ENTRIES IN ::: /ETC/POSTGRESQL/12/MAIN/PG_HBA.CONF
> 
> # Database administrative login by Unix domain socket
> 
> local   all             postgres                                peer
> 
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
> 
> 
> # "local" is for Unix domain socket connections only
> 
> local   all             all                                     peer
> 
> # IPv4 local connections:
> 
> host    all             all             127.0.0.1/32            md5
> 
> # IPv6 local connections:
> 
> host    all             all             ::1/128                 md5
> 
> # Allow replication connections from localhost, by a user with the
> 
> # replication privilege.
> 
> local   replication     all                                     peer
> 
> host    replication     all             127.0.0.1/32            md5
> 
> host    replication     all             ::1/128                 md5
> 
> NOT CONNECTED
> 
> POSTGRES@SRV:/OPT$ NETSTAT -NLP | GREP 5432
> 
> (Not all processes could be identified, non-owned process info
> 
> will not be shown, you would have to be root to see it all.)
> 
> tcp        0      0 127.0.0.1:5432          0.0.0.0:*
> LISTEN      25857/postgres
> 
> unix  2      [ ACC ]     STREAM     LISTENING     74234
> 25857/postgres       /var/run/postgresql/.s.PGSQL.5432
> 
> POSTGRES@SRV:~$ TELNET 10.133.214.32 5432
> 
> Trying 10.133.214.32...
> 
> telnet: Unable to connect to remote host: Connection refused

Re: [MASSMAIL]Cannot connect to postgresql on port 5432

От
"Peter M. Groen"
Дата:
It also possible to change the '*' to the ip-address of your NIC.
Or use a comma separated list for all ip-addresses you want to allow. 

listen_addresses='<your_ip_address>, 127.0.0.1'

pg_hba is only used for client-addresses and connection types...
-- 
Open Systems Development B.V.
Peter M. Groen
Managing Director
Rooseveltstraat 18-P
2321 BM  Leiden
+31 6 12 08 95 62
email : peter@osdev.nl
skype : peter_m_groen

On Mon, 2019-12-23 at 19:44 +0000, soumik.bhattacharjee@kpn.com wrote:
> Thanks Gilberto, yes I did that, but won't this be a security issue?
> 
> I tried this below in pg_hba.conf , but no help.
> 
> host all all 0.0.0.0/0 md5
> 
> So I think listen_addresess ='*' is the only way out.
> 
> Thanks..
> 
> 
> -----Original Message-----
> From: gilberto.castillo@etecsa.cu <gilberto.castillo@etecsa.cu> 
> Sent: maandag 23 december 2019 20:34
> To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
> Cc: pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org
> Subject: Re: [MASSMAIL]Cannot connect to postgresql on port 5432
> Importance: High
> 
> Hi
> 
> You might change postgresl.conf the line listening = '*'
> 
> El 2019-12-23 14:30, soumik.bhattacharjee@kpn.com escribió:
> > Hi Experts,
> > 
> > Post installation of  PostgreSQL it is not listening on an IP, only
> > on 
> > localhost.
> > 
> > Please suggest your inputs.
> > 
> > USED BELOW COMMAND FOR INSTALLATIONS –
> > 
> > # APT-GET INSTALL POSTGRESQL-12
> > 
> > postgres=# select version();
> > 
> >                                                      version
> > 
> > -----------------------------------------------------------------
> > -----
> > --------------------------------------------
> > 
> > 
> > PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, 
> > compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
> > 
> > (1 row)
> > 
> > ENTRIES IN ::: /ETC/POSTGRESQL/12/MAIN/PG_HBA.CONF
> > 
> > # Database administrative login by Unix domain socket
> > 
> > local   all             postgres                                pee
> > r
> > 
> > #
> > TYPE  DATABASE        USER            ADDRESS                 METHO
> > D
> > 
> > 
> > # "local" is for Unix domain socket connections only
> > 
> > local   all             all                                     pee
> > r
> > 
> > # IPv4 local connections:
> > 
> > host    all             all             127.0.0.1/32            md5
> > 
> > # IPv6 local connections:
> > 
> > host    all             all             ::1/128                 md5
> > 
> > # Allow replication connections from localhost, by a user with the
> > 
> > # replication privilege.
> > 
> > local   replication     all                                     pee
> > r
> > 
> > host    replication     all             127.0.0.1/32            md5
> > 
> > host    replication     all             ::1/128                 md5
> > 
> > NOT CONNECTED
> > 
> > POSTGRES@SRV:/OPT$ NETSTAT -NLP | GREP 5432
> > 
> > (Not all processes could be identified, non-owned process info
> > 
> > will not be shown, you would have to be root to see it all.)
> > 
> > tcp        0      0 127.0.0.1:5432          0.0.0.0:*
> > LISTEN      25857/postgres
> > 
> > unix  2      [ ACC ]     STREAM     LISTENING     74234
> > 25857/postgres       /var/run/postgresql/.s.PGSQL.5432
> > 
> > POSTGRES@SRV:~$ TELNET 10.133.214.32 5432
> > 
> > Trying 10.133.214.32...
> > 
> > telnet: Unable to connect to remote host: Connection refused

RE: [MASSMAIL]Cannot connect to postgresql on port 5432

От
Дата:
Thanks Peter, as of now working with '*'.

Will add the IP's of the Microservices hosted in private cloud which will act as client .


-----Original Message-----
From: Peter M. Groen <peter@osdev.nl> 
Sent: dinsdag 24 december 2019 11:07
To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>; gilberto.castillo@etecsa.cu
Cc: pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org
Subject: Re: [MASSMAIL]Cannot connect to postgresql on port 5432

It also possible to change the '*' to the ip-address of your NIC.
Or use a comma separated list for all ip-addresses you want to allow. 

listen_addresses='<your_ip_address>, 127.0.0.1'

pg_hba is only used for client-addresses and connection types...
--
Open Systems Development B.V.
Peter M. Groen
Managing Director
Rooseveltstraat 18-P
2321 BM  Leiden
+31 6 12 08 95 62
email : peter@osdev.nl
skype : peter_m_groen

On Mon, 2019-12-23 at 19:44 +0000, soumik.bhattacharjee@kpn.com wrote:
> Thanks Gilberto, yes I did that, but won't this be a security issue?
> 
> I tried this below in pg_hba.conf , but no help.
> 
> host all all 0.0.0.0/0 md5
> 
> So I think listen_addresess ='*' is the only way out.
> 
> Thanks..
> 
> 
> -----Original Message-----
> From: gilberto.castillo@etecsa.cu <gilberto.castillo@etecsa.cu>
> Sent: maandag 23 december 2019 20:34
> To: Bhattacharjee, Soumik <soumik.bhattacharjee@kpn.com>
> Cc: pgsql-admin@lists.postgresql.org; pgsql-admin@postgresql.org
> Subject: Re: [MASSMAIL]Cannot connect to postgresql on port 5432
> Importance: High
> 
> Hi
> 
> You might change postgresl.conf the line listening = '*'
> 
> El 2019-12-23 14:30, soumik.bhattacharjee@kpn.com escribió:
> > Hi Experts,
> > 
> > Post installation of  PostgreSQL it is not listening on an IP, only 
> > on localhost.
> > 
> > Please suggest your inputs.
> > 
> > USED BELOW COMMAND FOR INSTALLATIONS –
> > 
> > # APT-GET INSTALL POSTGRESQL-12
> > 
> > postgres=# select version();
> > 
> >                                                      version
> > 
> > -----------------------------------------------------------------
> > -----
> > --------------------------------------------
> > 
> > 
> > PostgreSQL 12.1 (Debian 12.1-1.pgdg100+1) on x86_64-pc-linux-gnu, 
> > compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
> > 
> > (1 row)
> > 
> > ENTRIES IN ::: /ETC/POSTGRESQL/12/MAIN/PG_HBA.CONF
> > 
> > # Database administrative login by Unix domain socket
> > 
> > local   all             postgres                                pee
> > r
> > 
> > #
> > TYPE  DATABASE        USER            ADDRESS                 METHO
> > D
> > 
> > 
> > # "local" is for Unix domain socket connections only
> > 
> > local   all             all                                     pee
> > r
> > 
> > # IPv4 local connections:
> > 
> > host    all             all             127.0.0.1/32            md5
> > 
> > # IPv6 local connections:
> > 
> > host    all             all             ::1/128                 md5
> > 
> > # Allow replication connections from localhost, by a user with the
> > 
> > # replication privilege.
> > 
> > local   replication     all                                     pee
> > r
> > 
> > host    replication     all             127.0.0.1/32            md5
> > 
> > host    replication     all             ::1/128                 md5
> > 
> > NOT CONNECTED
> > 
> > POSTGRES@SRV:/OPT$ NETSTAT -NLP | GREP 5432
> > 
> > (Not all processes could be identified, non-owned process info
> > 
> > will not be shown, you would have to be root to see it all.)
> > 
> > tcp        0      0 127.0.0.1:5432          0.0.0.0:*
> > LISTEN      25857/postgres
> > 
> > unix  2      [ ACC ]     STREAM     LISTENING     74234
> > 25857/postgres       /var/run/postgresql/.s.PGSQL.5432
> > 
> > POSTGRES@SRV:~$ TELNET 10.133.214.32 5432
> > 
> > Trying 10.133.214.32...
> > 
> > telnet: Unable to connect to remote host: Connection refused