Ubuntu 20.04
Sponsored Link

PostgreSQL 12 : Remote Connection
2020/09/01
 
It's possible to connect to PostgreSQL Server only from Localhost by default like here but if you'd like to connect to PostgreSQL from Remote hosts, change settings like follows.
[1] There are many authentication methods on PostgreSQL, though.
On this examaple, Configure MD5 password method.
root@dlp:~#
vi /etc/postgresql/12/main/postgresql.conf
# line 59: uncomment and change

listen_addresses = '
*
'
root@dlp:~#
vi /etc/postgresql/12/main/pg_hba.conf
# add to the end

# 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
# specify network range you allow to connect on [ADDRESS] section
# if allow all, specify [0.0.0.0/0]
host    all             all             10.0.0.0/24             md5

root@dlp:~#
systemctl restart postgresql

[2] To connect to a PostgreSQL Database with MD5 password, set password for each PostgreSQL user.
# connect to own database

ubuntu@dlp:~$
psql -d testdb

psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
Type "help" for help.

# set or change own password
testdb=> \password
Enter new password:
Enter it again:
testdb=> \q

# also possible to set or change password for any users with PostgreSQL admin user

postgres@dlp:~$
psql -c "alter user ubuntu with password 'password';"

ALTER ROLE
[3] Verify settings to connect to PostgreSQL Database with MD5 password from remote hosts.
root@node1:~#
psql -h dlp.srv.world -d testdb -U ubuntu

Password for user cent:   # password
psql (12.4 (Ubuntu 12.4-0ubuntu0.20.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

testdb=> # connected
Matched Content