Re: Access to databas from the Internet

On 19/9/2006 22:41, "Lukasz" <fatmouse(at)poczta(dot)fm> wrote:

> Hello,
>
> I would like to install a PostgreSQL. I know how to manage the database
> itself, creae databases, user, groups and so on. But I don't know how
> to allow other users, who are outside LAN to connect to database
> through Internet.
>
> For example, my external IP is xxx.xxx.xxx.xxx, and my IP in the local
> network is yyy.yyy.yyy.yyy. I want to install PostgreSQL on the
> computer with yyy.yyy... What and where I need to configure to have
> access to my database from Internet?

I will assume that you want to allow normal psql client access and not
through a web server.

There is two places you will need to configure.

One is your router - you will need to setup port forwarding . The default
port for connecting to the PostgreSQL server is 5432 so the router will need
to forward any incoming requests on tcp port 5432 to tcp port 5432 at
server address yyy.yyy.yyy.yyy (your PostgreSQL server address)
If you have configured a different port then adjust accordingly.
This is a common configuration option and shouldn't be hard to find.

Second you will need to configure PostgreSQL to accept connections from
outside your network. This is done in pg_hba.conf which is in your data
folder by default.
If you currently connect to the server from another machine on your network
you will have a line similar to
host all all yyy.yyy.yyy.yyy/24 md5

To allow PostgreSQL to accept outside connections you will add another line
such as
host all all zzz.zzz.zzz.zzz/32 md5

Where zzz.zzz.zzz.zzz is the ip address of the remote computer that wants to
connect.
The pg_hba.conf file has notes explaining these entries or you can read up
the docs at
http://www.postgresql.org/docs/8.1/static/client-authentication.html

Basically this entry says you are willing to accept network connections from
another computer and how they are allowed to connect and which databases
they can connect to.
If you allow connections from anywhere then anyone has the chance of getting
into your database.
It is preferable to only allow connections from a specific ip address but if
they don't have a static ip address you won't be able to do that. If they
have a dynamic ip address one suggestion is try and limit them to
connections from the isp they are connecting from instead of just any
computer in the world.

--

Shane Ambler
Postgres(at)007Marketing(dot)com

Get Sheeky @ http://Sheeky.Biz