Thread: PG on a web-server...issues..and some general questions on PG deployment

Hi All,

What are the issues one should take into consideration, when deploying
PG on a webserver? I am listing some that come to my mind...please add
others and correct me where I am wrong.

1. All connections to be made through a nobody user being called
through servlets. Postgres user for only maintainence purposes.

2. 10-15 pooled connections being called by servlets/beans, high
number of connections through non-use of pooling wherever available to
be avoided.

Nothing else comes to my mind...suggestions?

Some questions:

1. Any other security measures that should be taken? Any pointers to
any resources?

2. I am giving connection details in my servlets and they are visible
in HTML source of my pages. Is this a cause of concern? Should I write
wrapper servlets (hardcoded with connection details for each site)
that call my generic database access servlets?

3. Is PHP/Postgres module capable of server side connection pooling?

4. How do I get postgres operational statistics from the /proc
directory of RH Linux 6.2? Or what method do I use to know when PG is
finding load heavy? I want to create a proactive method which should
tell me my PG related load probs in ample time for me to code other
alternatives.

5. How good is PG on large databases deployed for datawarehousing
(larger than 2 GB filesize on ext3 filesystem)? I read a FAQ which
states that PG performance degrades rapidly when database size
increases. It was a comparison between MySQL, PG & Oracle. Any
comments?

6. Do people deploy PG databases, maybe second daemons or daemons on
second server for PG logs themselves, instead of processing text logs?
;-)) Wonder if it is even sensible...or am I talking pure non-sense?
;-))

With best regards.
Sanjay.

Comments inline

Adam Lang
Systems Engineer
Rutgers Casualty Insurance Company
http://www.rutgersinsurance.com
----- Original Message -----
From: "Sanjay Arora" <sk@pobox.com>
To: <pgsql-interfaces@postgresql.org>
Sent: Thursday, December 21, 2000 5:23 PM
Subject: [INTERFACES] PG on a web-server...issues..and some general
questions on PG deployment


> Hi All,
>
> What are the issues one should take into consideration, when deploying
> PG on a webserver? I am listing some that come to my mind...please add
> others and correct me where I am wrong.
>
> 1. All connections to be made through a nobody user being called
> through servlets. Postgres user for only maintainence purposes.

I'm not overly experienced with Linux, but I've never been enamored with the
"nobody" group and user.  Many other applications by default use that also.
I typically make a dedicated user for that.

>
> 2. 10-15 pooled connections being called by servlets/beans, high
> number of connections through non-use of pooling wherever available to
> be avoided.

I guess the pooling number really matters on the load you get.  I've always
seen people mention 10-15 as the number they use though.

>
> Nothing else comes to my mind...suggestions?

If you have it on the same server as the web, you wouldn't need to start the
postgres daemon with -I, correct?  That should eliminate some possible
security issues.

>
> Some questions:
>
> 1. Any other security measures that should be taken? Any pointers to
> any resources?
>
> 2. I am giving connection details in my servlets and they are visible
> in HTML source of my pages. Is this a cause of concern? Should I write
> wrapper servlets (hardcoded with connection details for each site)
> that call my generic database access servlets?

Definately do not let the connection values be seen by the outside world.
How do they show up in your HTML though?  If you are connecting to a
database you are doing server side scripting, which means the conenction
stuff should be processed and shouldn't display unless you tell it to.  I
have not used jsp, but that would sound really wierd.  Unless you just mean
you have the connection info in the base document and if someone got ahold
of that before it was processed, that the info would be available.

What I do for php is is create a directory outside of my www root and use it
for my include files.  For any conenction info, I include the text file that
has the connection info.  That way, if someone gets ahold of thebase
html/php document, conenction info is not in it.

>
> 3. Is PHP/Postgres module capable of server side connection pooling?

Yes.


The stuff following I can not answer.
>
> 4. How do I get postgres operational statistics from the /proc
> directory of RH Linux 6.2? Or what method do I use to know when PG is
> finding load heavy? I want to create a proactive method which should
> tell me my PG related load probs in ample time for me to code other
> alternatives.
>
> 5. How good is PG on large databases deployed for datawarehousing
> (larger than 2 GB filesize on ext3 filesystem)? I read a FAQ which
> states that PG performance degrades rapidly when database size
> increases. It was a comparison between MySQL, PG & Oracle. Any
> comments?
>
> 6. Do people deploy PG databases, maybe second daemons or daemons on
> second server for PG logs themselves, instead of processing text logs?
> ;-)) Wonder if it is even sensible...or am I talking pure non-sense?
> ;-))
>
> With best regards.
> Sanjay.
>
>

On Fri, 22 Dec 2000, Adam Lang wrote:

> > 3. Is PHP/Postgres module capable of server side connection pooling?
> 
> Yes.
 The PHP docs are very clear to not referr to it is pooling, but
persistant connections.  They are very different things.  PHP as a whole
is not capable of pooling, because a separate instance of PHP runs in each
httpd process, and those instances can not shared database connections
between them.

Tom