Home » Database » Miscellaneous » How to enable or allow remote access to PostgreSQL database server.

How to enable or allow remote access to PostgreSQL database server.

in , - on 00:02:00 - No comments
For enabling remote access to PostgreSQL database server,
Remote access to PostgreSQL database
There are 2 main PostgreSQL configuration files that need to be changed to allow remote connections.
1. postgresql.conf
2. pg_hba.conf

postgresql.conf

It will be located in PostgreSQL installed directory,

In Linux, You may find file at location below,
  1. /var/lib/pgsql/data/
  2. /etc/postgresql/9.1/main/
In Windows, You may find file at location below,
  1. C:/ProgramData/PostgreSQL/9.3/postgresql.conf

For exact location of "postgresql.conf" file, run the below command in psql command prompt.
SHOW config_file;
Open the file and search for "listen_addresses" and set listen_addresses parameter to,
listen_addresses = '*'

pg_hba.conf

It will be located in several directory, depending on how postgresql is installed

In Linux, You may find file at location below,
  1. /home,
  2. /var/lib/pgsql, 
  3. /var/lib/postgresql/[version]/, 
  4. /opt/postgres/  
  5. /etc/postgresql/[version]/main/
In Windows, You may find file at location below,
  1. C:/ProgramData/PostgreSQL/9.3/pg_hba.conf
For exact location of "pg_hba.conf" file, run the below command in psql command prompt.
SHOW hba_file;

If you want PostgreSQL to accept incoming connection from any IP address then add entry like below, 
 host             all             all             0.0.0.0/0             md5

If you want PostgreSQL to accept incoming connection from particular IP address, say 192.168.82.2 then add entry in pg_hba.conf file like below,
host             all             all            192.168.82.2/24       md5

Reload Configuration

After the changes are done, you have to reload the configuration as a superuser. run below command,
SELECT pg_reload_conf();
Done.

If PostgreSQL is still not accepting remote connection then try restarting the PostgreSQL service like below.

 In Linux, If your PostgreSQL service name is "postgresql" then execute below command
service postgresql restart
In Windows, You can find service at 
Start Menu ---> Search "Run" ---> Type "Services.msc" ---> Search "PosgreSQL"  
 ---> Right click and select Restart Service.

Explain SQL Injection with example?

When to use Builder design pattern

How Recursion works internally with example

Difference between process and thread

Burning Rope Puzzle - Measure 45 Minutes

Union and Intersection of Two Sorted Arrays

Merge two sorted arrays in Java

Enjoy !!!! 

If you find any issue in post or face any error while implementing, Please comment.
Categories: ,

Post a Comment