Access PostgreSQL from outside

After you installed PostgreSQL on a debian server, by default it is not accessible from outside. From other servers! It is just accessible from localhost. To change that you need to make it listen to a special ip address and port. On Debian 6 you have to switch to this directory:

/etc/postgresql/8.4/main

Here are the config files for PostgreSQL. You have to edit the “postgresql.conf” file. There is one line called “listen_addresses”. This line you have to enhance:

listen_addresses = 'localhost, <YOUR_SERVER_IP>' # what IP address(es) to listen on;
 # comma-separated list of addresses;
 # defaults to 'localhost', '*' = all
 # (change requires restart)

And with this lines you are controlling the port and the connections.

port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)

Now you have to restart the postgres service:

/etc/init.d/postgresql restart

That alone is not enough. Now PostgresSQL is listen but you can anyway not access it 🙂

For security reasons you have to edit the “pg_hba.conf” file, too. Here you say which IP address is trustful enough to access the service. Here you have to add a line like this:

host    all         all         192.168.0.19/24        md5

That means that all ip from your network can access all databases and all users.