How Do I Enable TCP Connections to PostgreSQL?

Note

If you use the Network Security Toolkit Web User Interface (NST WUI) to setup PostgreSQL, you will have a simple check box which allows you to choose whether or not to enable TCP connections and what systems are permitted to connect.

By default, the setup_postgresql script starts the PostgreSQL database such that it only accepts Unix-domain connections. This means you will not be able to connect to the database from external machines.

To enable the PostgreSQL server to accept TCP connections, you must modify two configuration files and then restart the server.

You must add the -i option to the /etc/sysconfig/pgsql/postgresql configuration file which is used to start the server.

Figure 6.1. Configuring PostgreSQL for TCP Connections

PGDATA=/mnt/ram4/var/lib/pgsql/data
PGPORT=5432
PGOPTS="-i"

Simply adding the -i option is not enough. You must also update /var/lib/pgsql/data/pg_hba.conf and add configuration lines indicating what host(s) you wish to permit access via TCP connections. The following shows the two lines that need to be added to the end of this file to enable the localhost and all of the machines on the 192.168.100 network access.

Figure 6.2. Allowing External Hosts To Access PostgreSQL

host   all all 127.0.0.1   255.255.255.255 md5
host   all all 192.168.100.0 255.255.255.0   md5

Once you've updated the configuration files, you will need to restart the PostgreSQL server.

Figure 6.3. Restarting the PostgreSQL Server

[root@probe root]# /etc/rc.d/init.d/postgresql restart
Stopping postgresql service:                               [  OK  ]
Starting postgresql service:                               [  OK  ]
[root@probe root]#