Fedora 40
Sponsored Link

PostgreSQL 16 : Remote Connection2024/05/15
 
It's possible to connect to PostgreSQL Server only from Localhost by default like here,
however 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 example, Configure scram-sha-256 password method.
[root@www ~]#
vi /var/lib/pgsql/data/postgresql.conf
# line 60 : uncomment and change
# if listen only IPv4, specify '0.0.0.0'

listen_addresses = '
*
'
[root@www ~]#
vi /var/lib/pgsql/data/pg_hba.conf
# add to last line

.....
.....
# 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            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
# 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             scram-sha-256
[root@www ~]#
systemctl restart postgresql

[2] If Firewalld is running, allow PostgreSQL service.
[root@www ~]#
firewall-cmd --add-service=postgresql

success
[root@www ~]#
firewall-cmd --runtime-to-permanent

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

[fedora@www ~]$
psql -d testdb 

psql (16.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 fedora with password 'password';"

ALTER ROLE
[4] Verify settings to connect to PostgreSQL Database with password from remote hosts.
[root@node01 ~]#
psql -h www.srv.world -d testdb -U fedora 

Password for user fedora:   # password
psql (16.1)
Type "help" for help.

testdb=> # connected
Matched Content