Re: Apache2 PostgreSQL http authentication

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