How to configure Remote access postgresql ??

[sajugovind]

It is best if we can access our database from a remote system. For MySQL on a cPanel server, we can enable this by a single click. Now the question, how we can enable the remote access for a PostgreSQL ? Dont worry we can do it from few steps as mentioned below.

Path to pgsql config: /var/lib/pgsql/data

PART 1 (edit postgresql.conf)

1. Edit postgresql.conf where

Code: [Select]

#listen_addresses = 'localhost'
change to
listen_addresses = '*'
2. Edit postgresql.conf where

Code: [Select]

#port = 5432
change to
port = 5432
as you can see if sign # it means that is not included in directive. If we remove the sign # the directive is set.

3. Edit postgresql.conf where

Code: [Select]

#max_connections = 32 (no. 32 can be different)
change to
max_connections = 32 (you can set no 32 or any other as you wish)
4. Close and save postgresql.conf

PART 2 (edit pg_hba.conf)

1.Edit pg_hba.conf where

# IPv4 local connections:  <<<<<<<can be found at the bottom of the pg_hba.conf file.>>>>>>>>>>> under that line add

host <database name> <database user> <IP from where cx want to access/24> md5

Thiss will allow that particular User to access from that IP. We can replace <database name> and <database user> with <all>  if we need to grant access to every one or every database on the server.

2. Close and save pg_hba.conf

3. If you did everything right your pg_hba.conf file at the bottom should look something like this:

Code: [Select]

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident sameuser
# IPv4 local connections:
host all all 127.0.0.1/32 ident sameuser
host database_name database_username 2xx.xx.xx.xx/24 md5
# IPv6 local connections:
host all all ::1/128 ident sameuser
PART 3

1. Restart Postgresql server. You can do it through WHM or use SSH command

service postgresql restart

2. If adding new data base you will need to add remote access permission for it. Use tutorial from PART 2, point 1. and restart postgresql server.

3. Of course, you need to open port 5432 in your firewall or nothing will work.

Thank you......