On this second article regarding PostgreSQL Database, I will show which files should be edited to enable remote connection with PostgreSQL.
First file to be edit is postgresql.conf, commonly found under data directory:
[root@dbserver /]$ cd /pgsql/13/data/
[root@dbserver data]$ ls postgresql.conf
postgresql.conf
[root@dbserver data]$
Open the file and edit listen_addresses parameter. Uncomment line parameter and change value for asterisk '*' to be possible connect for anywhere.
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
Another file to be edited is pg_bha.conf, also found under data directory:
[root@dbserver data]$ ls pg_hba.conf
pg_hba.conf
[root@dbserver data]$
Add the follow lines in the end of file:
# Accept from anywhere
host all all 0.0.0.0/0 md5
# Accept from trusted subnet
host all all 192.168.1.0/24 md5
Note: This is a test environment, change your configuration access accordingly with the most security settings. In production environment you must to limit from where you want to access.
After that, restart PostgreSQL service:
[root@dbserver data]$ systemctl restart postgresql.service
Now, test it from another server or PGAdmin tool:
[root@oraserver ~]$ psql -h 192.168.1.27 -p 5432 -U postgres
Password for user postgres:
Where -h parameter is PostgreSQL Database server IP, -p is port and -U is user to connect.
That is it!!
My Piece of Data 