Thread: Postgres database and firewall
Hi We will be setting up a production postgres database to which an application will connect through a firewall. Can any one please tell me if there is any configuration that needs to be done on the postgres database side for firewall. Is there any documentation that I can refer to. Any help will be appreciated. Thanks Paramjeet Bhella
Bhella Paramjeet-PFCW67 wrote: > Hi > > > We will be setting up a production postgres database to which an > application will connect through a firewall. Can any one please tell me > if there is any configuration that needs to be done on the postgres > database side for firewall. Is there any documentation that I can refer > to. Any help will be appreciated. > > Thanks > Paramjeet Bhella > > If you are using NAT then you need port forwarding setup on the firewall. If not then you need to make sure it allows the pg traffic through. Your firewall docs will show how to setup that. Default port for pg is 5432 As far as pg config goes the client ip addresses need to be allowed to connect. This is setup in pg_hba.conf see chapter 21 http://www.postgresql.org/docs/8.3/interactive/client-authentication.html For connections over the internet you should configure postgresql with SSL support and use something like - hostssl mydb +usergroup 192.168.1.0/24 md5 The problems arise if you want to allow roaming users that can have varying ip addresses - try to find a solution that doesn't allow any computer on the net to connect. Will you (or can you) have VPN access to the internal network? -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
Thank you very much Shane for your response. I have one more question, the firewall usually drops the idle connections. What can we configure on the database side to keep the idle connections alive. In the postgresql.conf file I see the parameter tcp_keepalives_idle, setting this parameter would be enough to keep the idle connections alive or is there anything else I need to be aware of. Your help will be highly appreciated. Thanks Paramjeet Kaur -----Original Message----- From: Shane Ambler [mailto:pgsql@Sheeky.Biz] Sent: Thursday, March 20, 2008 12:48 AM To: Bhella Paramjeet-PFCW67 Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] Postgres database and firewall Bhella Paramjeet-PFCW67 wrote: > Hi > > > We will be setting up a production postgres database to which an > application will connect through a firewall. Can any one please tell > me if there is any configuration that needs to be done on the postgres > database side for firewall. Is there any documentation that I can > refer to. Any help will be appreciated. > > Thanks > Paramjeet Bhella > > If you are using NAT then you need port forwarding setup on the firewall. If not then you need to make sure it allows the pg traffic through. Your firewall docs will show how to setup that. Default port for pg is 5432 As far as pg config goes the client ip addresses need to be allowed to connect. This is setup in pg_hba.conf see chapter 21 http://www.postgresql.org/docs/8.3/interactive/client-authentication.htm l For connections over the internet you should configure postgresql with SSL support and use something like - hostssl mydb +usergroup 192.168.1.0/24 md5 The problems arise if you want to allow roaming users that can have varying ip addresses - try to find a solution that doesn't allow any computer on the net to connect. Will you (or can you) have VPN access to the internal network? -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz
Bhella Paramjeet-PFCW67 wrote: > Thank you very much Shane for your response. I have one more question, > the firewall usually drops the idle connections. What can we configure > on the database side to keep the idle connections alive. In the > postgresql.conf file I see the parameter tcp_keepalives_idle, setting > this parameter would be enough to keep the idle connections alive or is > there anything else I need to be aware of. Your help will be highly > appreciated. If the firewall is stopping traffic when a connection is idle for too long then you may want to look at either changing the settings on the firewall or have the client send some trivial command on a timed basis. I may be wrong (I haven't looked into this in detail) but I think tcp_keepalives_idle keeps the tcp session alive when there is no traffic it doesn't actually send traffic to keep the session active which is what the firewall would need. I do know that some systems will not allow a program to change this setting so it must be done in the system config. > Thanks > Paramjeet Kaur > > -----Original Message----- > From: Shane Ambler [mailto:pgsql@Sheeky.Biz] > Sent: Thursday, March 20, 2008 12:48 AM > To: Bhella Paramjeet-PFCW67 > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Postgres database and firewall > > Bhella Paramjeet-PFCW67 wrote: >> Hi >> >> >> We will be setting up a production postgres database to which an >> application will connect through a firewall. Can any one please tell >> me if there is any configuration that needs to be done on the postgres > >> database side for firewall. Is there any documentation that I can >> refer to. Any help will be appreciated. >> >> Thanks >> Paramjeet Bhella >> >> > > If you are using NAT then you need port forwarding setup on the > firewall. If not then you need to make sure it allows the pg traffic > through. > Your firewall docs will show how to setup that. Default port for pg is > 5432 > > As far as pg config goes the client ip addresses need to be allowed to > connect. This is setup in pg_hba.conf > > see chapter 21 > http://www.postgresql.org/docs/8.3/interactive/client-authentication.htm > l > > For connections over the internet you should configure postgresql with > SSL support and use something like - > > hostssl mydb +usergroup 192.168.1.0/24 md5 > > > The problems arise if you want to allow roaming users that can have > varying ip addresses - try to find a solution that doesn't allow any > computer on the net to connect. > > > Will you (or can you) have VPN access to the internal network? > > > -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz