Thread: Apache2 PostgreSQL http authentication
Greetings All,
I have to authenticate against an existing (constantly modified) PostgreSQL
database under Solaris 10 (X86). While my PHP scripts are an obvious
no-brainer, the rest of the contents need to be protected as well (images,
etc) so the http authentication is required. I am using the blastwave
Apache2 and PostgreSQL packages.
I am concerned about slowing down apache too much with these postgres
queries on every get and post - and I would love to have some tips from you
guys on how to avoid that (beyond the normal tuning for postgres). For now,
I can not even get it working at all so I can test it. I have posted on
blastwave and solaris groups without reply but I have been watching this
list for years and you guys always come through if you can.
When I enable mod_dbd and set up the directory access, apache complains that
it can not find the DBDriver pgsql and I read from the manual page that it
is looking for apr_dbd_pgsql.so to be available to apache. Other research
indicates libpq.so is what it is looking for. Yet other research says that
libaprutil-1.so should contain one of these two when you look at it in a ldd
command. The closest thing that my system seems to have is the libpq.so in
the /opt/csw/postgres/lib directory and even when I provide a symbolic link
from the /opt/csw/apache2/lib for this library I still get a not found
condition from the DBDriver setting. It seems Google only helps if you are
not on Solaris - and even then not so much.
Is ANYONE using blastwave packages and getting the http authentication to
work against a PostgreSQL database on Solaris 10? If so, how? I am out of
straws to grasp.
As I say, from a performance point of view, I would really like to know if
there is anything I can do to make sure that postgres is performing as
quickly as possible under apache2 so that my http authentication is not
impacted too significantly.
Thanks in advance for any help that anyone can be!
Jeff Brower
On Oct 7, 2007, at 9:14 , Jeffrey Brower wrote: > Greetings All, > > I have to authenticate against an existing (constantly modified) > PostgreSQL > database under Solaris 10 (X86). While my PHP scripts are an obvious > no-brainer, the rest of the contents need to be protected as well > (images, > etc) so the http authentication is required. I am using the blastwave > Apache2 and PostgreSQL packages. I found it trivial to install mod_auth_pgsql. http://www.giuseppetanzilli.it/mod_auth_pgsql/ As far as performance, only your testing will tell if it is sufficient. In my setup, the authentication overhead is the least of my worries. Cheers, M
Thanks for the reply! I have used this in the past on Linux systems with Apache 1 - but I had no idea if the Apache2 version would compile under Solaris (let alone the Solaris X86 version) and run dependably. I sent Giuseppe an email and asked him, but I've gotten no reply. It looks like it had been a while since his system was updated (the version for apache2 was last updated in January of 2006) but that could easily be a testament to its solid performance since then. Thank you for letting me know that someone is actually using it under Solaris 10 X86 and that it will work dependably. I have heard tale of failures using the apache supplied module so this makes me happy. I will post my results here. Thanks again! Jeff -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of A.M. Sent: Sunday, October 07, 2007 11:20 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Apache2 PostgreSQL http authentication On Oct 7, 2007, at 9:14 , Jeffrey Brower wrote: > Greetings All, > > I have to authenticate against an existing (constantly modified) > PostgreSQL database under Solaris 10 (X86). While my PHP scripts are > an obvious no-brainer, the rest of the contents need to be protected > as well (images, > etc) so the http authentication is required. I am using the blastwave > Apache2 and PostgreSQL packages. I found it trivial to install mod_auth_pgsql. http://www.giuseppetanzilli.it/mod_auth_pgsql/ As far as performance, only your testing will tell if it is sufficient. In my setup, the authentication overhead is the least of my worries. Cheers, M ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Not so trivial for me as it turns out. Once I got the apxs command ironed out, I still could not compile it as I am missing all the headers in the blastwave package: apr.h apr_hooks.h apr_strings.h httpd.h and so on. Compilation aborted on me. I hope I am not looking at rebuilding from source downloads just to get an authentication working with postgres. Certainly SOMEONE is doing http authentication under Solaris. Jeff -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jeffrey Brower Sent: Sunday, October 07, 2007 3:08 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Apache2 PostgreSQL http authentication Thanks for the reply! I have used this in the past on Linux systems with Apache 1 - but I had no idea if the Apache2 version would compile under Solaris (let alone the Solaris X86 version) and run dependably. I sent Giuseppe an email and asked him, but I've gotten no reply. It looks like it had been a while since his system was updated (the version for apache2 was last updated in January of 2006) but that could easily be a testament to its solid performance since then. Thank you for letting me know that someone is actually using it under Solaris 10 X86 and that it will work dependably. I have heard tale of failures using the apache supplied module so this makes me happy. I will post my results here. Thanks again! Jeff -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of A.M. Sent: Sunday, October 07, 2007 11:20 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Apache2 PostgreSQL http authentication On Oct 7, 2007, at 9:14 , Jeffrey Brower wrote: > Greetings All, > > I have to authenticate against an existing (constantly modified) > PostgreSQL database under Solaris 10 (X86). While my PHP scripts are > an obvious no-brainer, the rest of the contents need to be protected > as well (images, > etc) so the http authentication is required. I am using the blastwave > Apache2 and PostgreSQL packages. I found it trivial to install mod_auth_pgsql. http://www.giuseppetanzilli.it/mod_auth_pgsql/ As far as performance, only your testing will tell if it is sufficient. In my setup, the authentication overhead is the least of my worries. Cheers, M ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
On Sun, 7 Oct 2007 09:14:43 -0400 "Jeffrey Brower" <Jeff@PointHere.net> wrote: > As I say, from a performance point of view, I would really like to know if > there is anything I can do to make sure that postgres is performing as > quickly as possible under apache2 so that my http authentication is not > impacted too significantly. How often does the user information change? Can you simply create standard Apache password files from cron during non-busy hours? Sometimes the lower tech solution works best. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Using a cron task was my first thought. Unfortunately, new users are given a logon that they immediately use. I thought about shelling out and updating a password file on an on-demand basis but I am not sure if that is such a great idea either - especially since users can change their passwords and renew their logons at will as well. Jeff -----Original Message----- From: D'Arcy J.M. Cain [mailto:darcy@druid.net] Sent: Sunday, October 07, 2007 4:45 PM To: Jeff@PointHere.net Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Apache2 PostgreSQL http authentication On Sun, 7 Oct 2007 09:14:43 -0400 "Jeffrey Brower" <Jeff@PointHere.net> wrote: > As I say, from a performance point of view, I would really like to > know if there is anything I can do to make sure that postgres is > performing as quickly as possible under apache2 so that my http > authentication is not impacted too significantly. How often does the user information change? Can you simply create standard Apache password files from cron during non-busy hours? Sometimes the lower tech solution works best. -- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
Success! First you need to make sure that the blastwave package for apache development is on your machine. Use the blastwave command: pkg-get -i apache2_devel This gives you the headers you are missing from the apache binary install (as well as loading the libtool etc that the apxs command will use. Now go to http://www.giuseppetanzilli.it/mod_auth_pgsql2/ and download the source. I put it in /Documents/mod_auth_pgsql2/mod_auth_pgsql-2.0.3 Extract it in place (or you can move it someplace else, but you will have to change where you execute the next command. This is the one that does the business of compiling, installing and updating your httpd.conf file all at once. I have Solaris SunStudio installed so it works rather neatly. I have the Blastwave PostgreSQL package installed at it's default location too - so this should work for you as long as you have the same packages installed. Change directories to where ever you extracted mod_auth_pgsql and enter this command: /opt/csw/apache2/sbin/apxs -i -a -c -I /opt/csw/postgresql/include -L /opt/csw/postgresql/lib -lpq mod_auth_pgsql.c If this completed OK you are pretty much installed. Now you need to set up your authentication. This had a speed bump in it too. You need to shut down the basic authentication from apache if you are going to use the PostgreSQL authentication. This is not in any of the manuals but it seems to be required because it only works correctly this way. More on that later. In your httpd.conf you will need to add your configuration. You can also use .htaccess but I don't like using that because it is yet another file the apache server looks for on every request in every directory. My test configuration (which works) is: <Directory "/path/to/apache2/htdocs/secretstuff"> AuthName "My PostgreSQL Authenticator" AuthType Basic AuthBasicAuthoritative Off Auth_PG_host localhost Auth_PG_port 5432 Auth_PG_user mypostgresuserid Auth_PG_pwd mypostgrespassword Auth_PG_database mydatabasename Auth_PG_pwd_table mytablename Auth_PG_uid_field myuseridfieldname Auth_PG_pwd_field mypasswordfieldname Auth_PG_encrypted on Auth_PG_hash_type CRYPT Auth_PG_pwd_whereclause " and myaccountstatus = 'Active' " <LIMIT GET POST> require valid-user </LIMIT> </Directory> And that is it. A few notes are in order. The "AuthBasicAuthoritative Off" needs to be there (this is the one that is not specified as required in any manual I can find). If you use plain text passwords in the database (so that you can do things like send them to users if they forget their password), you will want to use "Auth_PG_encrypted off" and remove the "Auth_PG_hash_type CRYPT" (or what ever password encryption you use). There is also a "Auth_PG_cache_passwords" setting you can use in case the system gets a lot of traffic and the lookups slow things down. I hope this helps someone searching for the same solutions. This really does work well. Jeff Brower -----Original Message----- From: Jeffrey Brower [mailto:Jeff@PointHere.net] Sent: Sunday, October 07, 2007 4:29 PM To: Jeff@PointHere.net; pgsql-performance@postgresql.org Subject: RE: [PERFORM] Apache2 PostgreSQL http authentication Not so trivial for me as it turns out. Once I got the apxs command ironed out, I still could not compile it as I am missing all the headers in the blastwave package: apr.h apr_hooks.h apr_strings.h httpd.h and so on. Compilation aborted on me. I hope I am not looking at rebuilding from source downloads just to get an authentication working with postgres. Certainly SOMEONE is doing http authentication under Solaris. Jeff -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Jeffrey Brower Sent: Sunday, October 07, 2007 3:08 PM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Apache2 PostgreSQL http authentication Thanks for the reply! I have used this in the past on Linux systems with Apache 1 - but I had no idea if the Apache2 version would compile under Solaris (let alone the Solaris X86 version) and run dependably. I sent Giuseppe an email and asked him, but I've gotten no reply. It looks like it had been a while since his system was updated (the version for apache2 was last updated in January of 2006) but that could easily be a testament to its solid performance since then. Thank you for letting me know that someone is actually using it under Solaris 10 X86 and that it will work dependably. I have heard tale of failures using the apache supplied module so this makes me happy. I will post my results here. Thanks again! Jeff -----Original Message----- From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of A.M. Sent: Sunday, October 07, 2007 11:20 AM To: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Apache2 PostgreSQL http authentication On Oct 7, 2007, at 9:14 , Jeffrey Brower wrote: > Greetings All, > > I have to authenticate against an existing (constantly modified) > PostgreSQL database under Solaris 10 (X86). While my PHP scripts are > an obvious no-brainer, the rest of the contents need to be protected > as well (images, > etc) so the http authentication is required. I am using the blastwave > Apache2 and PostgreSQL packages. I found it trivial to install mod_auth_pgsql. http://www.giuseppetanzilli.it/mod_auth_pgsql/ As far as performance, only your testing will tell if it is sufficient. In my setup, the authentication overhead is the least of my worries. Cheers, M ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
I'd consider having a small daemon LISTENing for NOTIFYs that you send by triggers whenever the table has changed. That'llmake sure it only dumps if something actually changed. And you can also implement some ratelimiting if needed. /Magnus > ------- Original Message ------- > From: "Jeffrey Brower" <Jeff@PointHere.net> > To: "'D'Arcy J.M. Cain'" <darcy@druid.net> > Sent: 07-10-07, 22:58:29 > Subject: Re: [PERFORM] Apache2 PostgreSQL http authentication > > Using a cron task was my first thought. Unfortunately, new users are given > a logon that they immediately use. I thought about shelling out and > updating a password file on an on-demand basis but I am not sure if that is > such a great idea either - especially since users can change their passwords > and renew their logons at will as well. > > Jeff > > > -----Original Message----- > From: D'Arcy J.M. Cain [mailto:darcy@druid.net] > Sent: Sunday, October 07, 2007 4:45 PM > To: Jeff@PointHere.net > Cc: pgsql-performance@postgresql.org > Subject: Re: [PERFORM] Apache2 PostgreSQL http authentication > > On Sun, 7 Oct 2007 09:14:43 -0400 > "Jeffrey Brower" <Jeff@PointHere.net> wrote: > > As I say, from a performance point of view, I would really like to > > know if there is anything I can do to make sure that postgres is > > performing as quickly as possible under apache2 so that my http > > authentication is not impacted too significantly. > > How often does the user information change? Can you simply create standard > Apache password files from cron during non-busy hours? > Sometimes the lower tech solution works best. > > -- > D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves > http://www.druid.net/darcy/ | and a sheep voting on > +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings >
Magnus Hagander schrieb: > I'd consider having a small daemon LISTENing for NOTIFYs that you send by triggers whenever the table has changed. That'llmake sure it only dumps if something actually changed. And you can also implement some ratelimiting if needed. Do you really think such a homegrown solution will be more performant then just accessing postgres? If you have many users the lookup time in a .htaccess/.htpasswd is not for free either. Regards Tino
Tino Wildenhain wrote: > Magnus Hagander schrieb: >> I'd consider having a small daemon LISTENing for NOTIFYs that you send >> by triggers whenever the table has changed. That'll make sure it only >> dumps if something actually changed. And you can also implement some >> ratelimiting if needed. > Do you really think such a homegrown solution will be more > performant then just accessing postgres? If you have > many users the lookup time in a .htaccess/.htpasswd is not for > free either. Right, that's what it depends on. I'd measure it. In systems with not too many users (say just a couple of thousand), I've measured great improvements in speed. It depends on how you authenticate as well - if you authenticate every single http request, the difference is greater. //Magnus