POSTGRESQL-remote akses centos7

https://yallalabs.com/linux/how-to-enable-network-remote-access-to-postgresql-database-server/

By default, PostgreSQL Server allow access to clients localy using Unix sockets. Here, we’ll show you how to enable the network remote access to PostgreSQL Database Server.

If you did not installed yet Postgresql Server, you can check out our previous tutorials :

• How To install PostgreSQL 11 on CentOS 7 / RHEL 7
• How To Install PostgreSQL 11 on Ubuntu 18.04 LTS / 16.04 LTS

Step 1. Configuring postgresql.conf

– Edit this line in your postgresql.conf file as shown below.

# sudo vi /etc/postgresql/9.6/main/postgresql.conf #### Postgresql 9.6
# sudo vi /var/lib/pgsql/10/data/postgresql.conf ##### Postgresql 10
# sudo vi /var/lib/pgsql/11/data/postgresql.conf ##### Postgresql 11
#
# - Connection Settings -

listen_addresses = '*'                  # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all

Step 2. Configuring pg_hba.conf

– Add the following line in the pg_hba.conf file to allow access to all databases for all users with an encrypted password:

# sudo vi /etc/postgresql/9.6/main/pg_hba.conf
# sudo vi /var/lib/pgsql/10/data/pg_hba.conf ##### FOR Postgresql 10
# sudo vi /var/lib/pgsql/11/data/pg_hba.conf ##### FOR Postgresql 11
#
# TYPE   DATABASE   USER   CIDR-ADDRESS   METHOD
  Host   all        all    0.0.0.0/0      md5

Step 3. Restart PostgreSQL Server

– After making changes, we have to restart the PostgreSQL server

# sudo systemctl restart postgresql
# sudo systemctl restart postgresql-10  ### FOR Postgresql 10
# sudo systemctl restart postgresql-11 ### FOR Postgresql 11

Step 4. Adjusting Firewall

– By default, PostgreSQL server listens at the port 5432, to allow the remote access we have to open the 5432 port

# CentOS 7 / RHEL 7
# sudo firewall-cmd --permanent --add-port=5432/tcp
# sudo firewall-cmd --reload

# Ubuntu 16.04
# sudo ufw allow 5432/tcp

Step 5. Test the Remote Connection To PostgreSQL Server

– Now, login to the client machine, and perform the psql remote connection to the PostgreSQL database server as shown below.

# psql -h 19.168.1.15 -U postgres
Password for user postgres: 
psql (9.6.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=#

We hope this tutorial was enough Helpful. If you need more information, or have any questions, just comment below and we will be glad to assist you!