0

I am running postgresql 10 at ubuntu 18 host in Digital Ocean.

This is my pg_hba.conf file:

# 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
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
host    all             all             0.0.0.0/0               md5
#host all postgres 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
host    all             all             0.0.0.0/0               md5

this is log file after restart

2019-09-06 02:49:05.213 UTC [3334] LOG:  database system is ready to accept connections
2019-09-06 02:49:05.743 UTC [3342] [unknown]@[unknown] LOG:  incomplete startup packet
2019-09-06 02:49:17.164 UTC [3367] postgres@postgres FATAL:  pg_hba.conf rejects connection for host "192.X.X.2", user "postgres", database "postgres", SSL on
2019-09-06 02:49:17.232 UTC [3368] postgres@postgres FATAL:  pg_hba.conf rejects connection for host "192.X.X.2", user "postgres", database "postgres", SSL off

My postgres.conf file 

data_directory = '/var/lib/postgresql/10/main'        
hba_file = '/etc/postgresql/10/main/pg_hba.conf'      
listen_addresses = '*'

I tried postgresql restart, reload, and system reboot. It do not reflect according to the pg_hba.conf file.

At the settings above, I also try with localhost

psql -c 'SELECT version()' -h 127.0.0.1 -U postgres

It still fails to connect

It only works with 

PSQL:
JT_T
  • 176
  • 1
  • 1
  • 11
  • That means that you edited the wrong file. The `pg_hba.conf` you are showing is fine. Run `SHOW hba_file;` – Laurenz Albe Sep 06 '19 at 05:59
  • I don't understand what "*it only works with PSQL*" is supposed to mean. Directly above that line you claim that `psql` fails to connect –  Sep 06 '19 at 07:36
  • @LaurenzAlbe Is the correct file, I found out when postgres has failed it services. It normally will add 2 line at the top of the pg_hba.conf file just to block ALL connection.I just remove that 2 line, and restart the services. It will work as normal. – JT_T Sep 07 '19 at 07:18
  • @a_horse_with_no_name PSQL is connected. just that there is 2 line at the top pg_hba.conf which cause connection that out of the network is block. – JT_T Sep 07 '19 at 07:19
  • PostgreSQL doesn't do anything like automatically adding lines to `pg_hba.conf`. Must be your startup script or cluster software. – Laurenz Albe Sep 08 '19 at 14:04
  • @LaurenzAlbe I notice if postgreSQL run on docker. By default, it will have that 2 additional line. U may be right is somepoint where my local ubuntu machine does't gv me the problem. But when it come to cloud -DO, it just happen. – JT_T Sep 11 '19 at 07:37

0 Answers0