How Do I Enable remote access to PostgreSQL database server?

Взято отсюда

postgresql-logoBy default, PostgreSQL database server remote access disabled for security reasons. However, some time you need to provide the remote access to database server from home computer or from web server.

Step # 1: Login over ssh if server is outside your IDC

Login over ssh to remote PostgreSQL database server:

Step # 2: Enable client authentication

Once connected, you need edit the PostgreSQL configuration file, edit the PostgreSQL configuration file /var/lib/pgsql/data/pg_hba.conf (or/etc/postgresql/8.2/main/pg_hba.conf for latest 8.2 version) using a text editor such as vi.

Login as postgres user using su / sudo command, enter:

Edit the file:

OR

Append the following configuration lines to give access to 10.10.29.0/24 network:

Save and close the file. Make sure you replace 10.10.29.0/24 with actual network IP address range of the clients system in your own network.

Step # 2: Enable networking for PostgreSQL

You need to enable TCP / IP networking. Use either step #3 or #3a as per your PostgreSQL database server version.

Step # 3: Allow TCP/IP socket

If you are using PostgreSQL version 8.x or newer use the following instructions or skip to step # 3a for older version (7.x or older).

You need to open PostgreSQL configuration file /var/lib/pgsql/data/postgresql.conf or /etc/postgresql/8.2/main/postgresql.conf.

OR

Find configuration line that read as follows:

Next set IP address(es) to listen on; you can use comma-separated list of addresses; defaults to ‘localhost’, and ‘*’ is all ip address:

Or just bind to 202.54.1.2 and 202.54.1.3 IP address

Save and close the file. Skip to step # 4.

Step #3a — Information for old version 7.x or older

Following configuration only required for PostgreSQL version 7.x or older. Open config file, enter:

Bind and open TCP/IP port by setting tcpip_socket to true. Set / modify tcpip_socket to true:

Save and close the file.

Step # 4: Restart PostgreSQL Server

Type the following command:

Step # 5: Iptables firewall rules

Make sure iptables is not blocking communication, open port 5432 (append rules to your iptables scripts or file /etc/sysconfig/iptables):

Restart firewall:

Step # 6: Test your setup

Use psql command from client system. Connect to remote server using IP address 10.10.29.50 and login using vivek username and sales database, enter: