Thread: Access to databas from the Internet
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? Regards, Lukasz
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 > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Lukasz > Sent: Tuesday, September 19, 2006 3:11 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Access to databas from the Internet > > > 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? > > Regards, > Lukasz > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend >
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: > 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? > > Regards, > Lukasz >
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
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.