Friday, March 6, 2009

Remote access to PostgreSQL Server

Error: "could not connect to Server: Connection refused"

The resolution is a five step process : -

1. Firstly, check if your firewall allows the port (default is 5432) to establish a connection.

2. By default, PostgreSQL does not allow remote incoming connections. This has been implemented in this way, because of security concerns. You need to enable incoming TCP/IP connection requests from the client. This can be done by adding

listen_addresses = '*'

in the configuration file postgresql.conf. This configuration is valid for postgresql 8.x.

3. You will have to inform Postgresql who can connect to the server. This has to be done by modifying the configuration file pg_hba.conf

host all all trust
host all all trust

This would allow any user on the local network to access all the databases on the server in "trust" mode.

4. After you have saved the configuration files you will have to restart the server. Remember, these configuration files are read on server startup.

5. Test your config. Go to the command prompt and run the following command.

>psql -h serverIP -U postgresUser -d postgresdb

And, you should be set for remote access to postgresql server.

No comments: