pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedApache2 PostgreSQL http authentication
10+ messages / 5 participants
[nested] [flat]
* Apache2 PostgreSQL http authentication
@ 2007-10-07 13:14 Jeffrey Brower <Jeff@PointHere.net>
0 siblings, 2 replies; 10+ messages in thread
From: Jeffrey Brower @ 2007-10-07 13:14 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Apache2 PostgreSQL http authentication
@ 2007-10-07 15:19 A.M. <agentm@themactionfaction.com>
parent: Jeffrey Brower <Jeff@PointHere.net>
1 sibling, 1 reply; 10+ messages in thread
From: A.M. @ 2007-10-07 15:19 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Apache2 PostgreSQL http authentication
@ 2007-10-07 19:07 Jeffrey Brower <Jeff@PointHere.net>
parent: A.M. <agentm@themactionfaction.com>
0 siblings, 1 reply; 10+ messages in thread
From: Jeffrey Brower @ 2007-10-07 19:07 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Apache2 PostgreSQL http authentication
@ 2007-10-07 20:28 Jeffrey Brower <Jeff@PointHere.net>
parent: Jeffrey Brower <Jeff@PointHere.net>
0 siblings, 1 reply; 10+ messages in thread
From: Jeffrey Brower @ 2007-10-07 20:28 UTC (permalink / raw)
To: Jeff@PointHere.net; pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Apache2 PostgreSQL http authentication
@ 2007-10-07 20:44 D'Arcy J.M. Cain <darcy@druid.net>
parent: Jeffrey Brower <Jeff@PointHere.net>
1 sibling, 1 reply; 10+ messages in thread
From: D'Arcy J.M. Cain @ 2007-10-07 20:44 UTC (permalink / raw)
To: Jeff@PointHere.net; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Apache2 PostgreSQL http authentication
@ 2007-10-07 20:58 Jeffrey Brower <Jeff@PointHere.net>
parent: D'Arcy J.M. Cain <darcy@druid.net>
0 siblings, 0 replies; 10+ messages in thread
From: Jeffrey Brower @ 2007-10-07 20:58 UTC (permalink / raw)
To: 'D'Arcy J.M. Cain' <darcy@druid.net>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Apache2 PostgreSQL http authentication
@ 2007-10-08 00:29 Jeffrey Brower <Jeff@PointHere.net>
parent: Jeffrey Brower <Jeff@PointHere.net>
0 siblings, 0 replies; 10+ messages in thread
From: Jeffrey Brower @ 2007-10-08 00:29 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Apache2 PostgreSQL http authentication
@ 2007-10-08 06:12 Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 10+ messages in thread
From: Magnus Hagander @ 2007-10-08 06:12 UTC (permalink / raw)
To: Jeff@PointHere.net; +Cc: 'D'Arcy J.M. Cain' <darcy@druid.net>; pgsql-performance
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.
/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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Apache2 PostgreSQL http authentication
@ 2007-10-08 17:09 Tino Wildenhain <tino@wildenhain.de>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 10+ messages in thread
From: Tino Wildenhain @ 2007-10-08 17:09 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: Jeff@PointHere.net, "'D'Arcy J.M. Cain'" <darcy@druid.net>; pgsql-performance
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.
Regards
Tino
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Apache2 PostgreSQL http authentication
@ 2007-10-08 17:12 Magnus Hagander <magnus@hagander.net>
parent: Tino Wildenhain <tino@wildenhain.de>
0 siblings, 0 replies; 10+ messages in thread
From: Magnus Hagander @ 2007-10-08 17:12 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; +Cc: Jeff@PointHere.net, "'D'Arcy J.M. Cain'" <darcy@druid.net>; pgsql-performance
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2007-10-08 17:12 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-07 13:14 Apache2 PostgreSQL http authentication Jeffrey Brower <Jeff@PointHere.net>
2007-10-07 15:19 ` A.M. <agentm@themactionfaction.com>
2007-10-07 19:07 ` Jeffrey Brower <Jeff@PointHere.net>
2007-10-07 20:28 ` Jeffrey Brower <Jeff@PointHere.net>
2007-10-08 00:29 ` Jeffrey Brower <Jeff@PointHere.net>
2007-10-07 20:44 ` D'Arcy J.M. Cain <darcy@druid.net>
2007-10-07 20:58 ` Jeffrey Brower <Jeff@PointHere.net>
2007-10-08 06:12 Re: Apache2 PostgreSQL http authentication Magnus Hagander <magnus@hagander.net>
2007-10-08 17:09 ` Tino Wildenhain <tino@wildenhain.de>
2007-10-08 17:12 ` Magnus Hagander <magnus@hagander.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox