Access to databas from the Internet

Hello Lukasz!

You need some port forwarding onto your router/firewall. You have to forward
incoming connections on port 5432 (or the port postmaster is listening on)
from IP xxx.xxx.xxx.xxx to IP yyy.yyy.yyy.yyy with the according port. If
your router is a Linux machine, take a look into the iptables rules and also
the HowTos at http://www.netfilter.org/ how to create a port forwarding. Be
aware that this will also attackers from the internet will enable them to
use exploits onto your server! So make sure, that this machine is properly
secured.

Best regards,
Matthias

On 19/9/2006 22:41, "Lukasz" <fatmouse@poczta.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@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

am Tue, dem 19.09.2006, um 6:11:12 -0700 mailte Lukasz folgendes:

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

Can you connect from outside to you external IP with SSH? If yes, then
you can use a so called 'SSH-Tunnel'.

See: http://pgedit.com/, Chapter 'SSH Tunneling'

HTH, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

You have to either configure your router to redirect the public port to the private port. (By
default 5432). Or put your database server in the DMZ (not recommended).

Lukasz wrote:

Shane Ambler napisal(a):

On 19/9/2006 22:41, "Lukasz" <fatmouse@poczta.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@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

I will connect to my PostgreSQL by an Java applet, as also, from time
to time, by PGAdmin.

On 20/9/2006 16:55, "Lukasz" <fatmouse@poczta.fm> wrote:

Shane Ambler napisal(a):

On 19/9/2006 22:41, "Lukasz" <fatmouse@poczta.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.

I will connect to my PostgreSQL by an Java applet, as also, from time
to time, by PGAdmin.

They will both connect the same as psql - through port 5432. If you wanted
them to connect to a web server in your network to access the db then you
would use port 80.

--

Shane Ambler
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

Shane Ambler napisal(a):

On 20/9/2006 16:55, "Lukasz" <fatmouse@poczta.fm> wrote:

Shane Ambler napisal(a):

On 19/9/2006 22:41, "Lukasz" <fatmouse@poczta.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.

I will connect to my PostgreSQL by an Java applet, as also, from time
to time, by PGAdmin.

They will both connect the same as psql - through port 5432. If you wanted
them to connect to a web server in your network to access the db then you
would use port 80.

--

Shane Ambler
Postgres@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

PostgreSQL finally installed, runs on Windows based server, everything
works great. I tried to give local and remote access to MS Access
database, but there was no good solution for me. I converted the
database to pgslq and now I'm a happy PostgreSQL user :)

Thank You guys for help.