DBD framework, APR, caching

Hi everybody,

Nick Kew suggested that I should post my findings and ideas
regarding the DBD
framework, APR and caching.

Just a short paragraph on how this all came up:

I've written an authnz module for IBM DB2. The reason why I
haven't used the DBD
framework was the lack of features that I have implemented
in my module:
- stored procedure support
- caching
- password validation for a 32-character hexadecimal md5
hash

Please see Nick's blog entry 'In or Out [part 2]' at
http://bahumbug.wordpress.com/2007/03/18/in-or-out-par
t-2/

I have some ideas how these features can be implemented
within the DBD framework
and how others can benefit from them:

Password validation:

Many web applications use a 32-character hexadecimal md5
hash, since PHP returns
such a value for its md5 function. Unfortunately the
apr_password_validate
function does not validate such a value. Since almost every
authnz backend uses
this function, it would be perfect to add this validation
process to
apr_password_validate. I have attached a patch for it.

Caching:

I've just changed my caching algorithm to use APR, so it
must be fairly easy to
either add caching to the DBD framework or to write its own
caching backend.
Regarding the modularity, an own autnz caching backend would
be best.
So all other expensive providers could benefit from
caching.
See http://tinyurl.com/yvwnhs for my caching functions.
It would be nice to even add memory caching functionality to
it.
If someone has any ideas, please let me know. I don't need
to write all code by
myself, so I would be happy to help out with it.

Stored procedure support:

I think the DBD framework is the best way to implement SP
functionality for all
SQL backends.
I have added two directives to my module which expect only
the name of the
stored procedure. The procedures must have the following
parameter format:
CREATE PROCEDURE user_procedure_name ( IN VARCHAR, OUT
VARCHAR )
CREATE PROCEDURE group_procedure_name ( IN VARCHAR )
The input parameter is always the 'username'. In the user
SP, the password is
returned in the out value and in the group SP an open cursor
to the resultset is
returned.
There are other ways to implement it, but I thought that
this would be the most
performant way.

I hope that this post initiates a discussion about this
topic.
Furthermore I hope that others already have thought about
the same issues and
that they are sharing their thoughts.

Regards,
   Helmut Tessarek

Helmut Tessarek wrote:

> Caching:
> 
> I've just changed my caching algorithm to use APR, so
it must be fairly easy to
> either add caching to the DBD framework or to write its
own caching backend.
> Regarding the modularity, an own autnz caching backend
would be best.
> So all other expensive providers could benefit from
caching.
> See http://tinyurl.com/yvwnhs for my caching functions.
> It would be nice to even add memory caching
functionality to it.
> If someone has any ideas, please let me know. I don't
need to write all code by
> myself, so I would be happy to help out with it.

The LDAP authentication module has support for caching, it
may be worth 
investigating whether parts of it can be abstracted out into
a generic 
authentication cache.

The LDAP cache already supports things like shared memory,
etc - it may 
be useful to use that existing code as a base rather than
reinventing it 
from scratch.

Regards,
Graham
--

On Thu, 22 Mar 2007 11:23:18 +0200
Graham Leggett <minfrinsharp.fm> wrote:

> Helmut Tessarek wrote:
> 
> > Caching:
> > 
> > I've just changed my caching algorithm to use APR,
so it must be
> > fairly easy to either add caching to the DBD
framework or to write
> > its own caching backend. Regarding the modularity,
an own autnz
> > caching backend would be best. So all other
expensive providers
> > could benefit from caching. See http://tinyurl.com/yvwnhs for my

Don't tinyurl references expire after a few days (or weeks
or
something)?  Mailinglists, by contrast, are archived.  I
understand
you may want to retain the flexibility to invalidate that
link
yourself, but a mention of the top of your repository at
www.evermeet.cx/cvs/ surely wouldn't hurt?

> > caching functions. It would be nice to even add
memory caching
> > functionality to it. If someone has any ideas,
please let me know.
> > I don't need to write all code by myself, so I
would be happy to
> > help out with it.

That looks fairly generic now, thanks!

> The LDAP authentication module has support for caching,
it may be
> worth investigating whether parts of it can be
abstracted out into a
> generic authentication cache.
> 
> The LDAP cache already supports things like shared
memory, etc - it
> may be useful to use that existing code as a base
rather than
> reinventing it from scratch.

IME the LDAP code tends to be rather inward-looking and
isn't always
the easiest to re-use (and util_ldap_cache.h lacks the
prototyping
and documentation of a public API).  If you can find a round
tuit for
making that jump from specific to generic then great, but
until then I
think we should welcome alternatives into the ecosystem. 
This one has
a simple, clean-ish API and looks pretty easy to work with.

-- 
Nick Kew

Application Development with Apache - the Apache Modules
Book
http://www.apachetutor.or
g/

On Wed, 21 Mar 2007 22:59:41 +0100
Helmut Tessarek <tessarekevermeet.cx> wrote:

> Hi everybody,
> 
> Nick Kew suggested that I should post my findings and
ideas regarding
> the DBD framework, APR and caching.

Indeed.  I think we stand to gain insights, and perhaps also
code,
from discussing it here.

> Please see Nick's blog entry 'In or Out [part 2]' at
> http://bahumbug.wordpress.com/2007/03/18/in-or-out-par
t-2/
> 
> I have some ideas how these features can be implemented
within the
> DBD framework and how others can benefit from them:
> 
> Password validation:

Is that the same issue as
http://issues.apache.org/bugzilla/show_bug.cgi?id=41925

> Caching:

Check.  See my followup to Graham.

> Stored procedure support:

My working assumption with DBD is that creating stored
procedures
is part of database design and initialisation, and not
something
I'd want the apache user to have privilege to mess with! 
Are you
talking about something like per-connection stored
procedures?
 
> I hope that this post initiates a discussion about this
topic.

Thanks for posting!

-- 
Nick Kew

Application Development with Apache - the Apache Modules
Book
http://www.apachetutor.or
g/

On Thu, March 22, 2007 2:10 pm, Nick Kew wrote:

> IME the LDAP code tends to be rather inward-looking and
isn't always
> the easiest to re-use (and util_ldap_cache.h lacks the
prototyping
> and documentation of a public API).  If you can find a
round tuit for
> making that jump from specific to generic then great,
but until then I
> think we should welcome alternatives into the
ecosystem.  This one has
> a simple, clean-ish API and looks pretty easy to work
with.

We should definitely welcome the new alternatives, but in
the long term it
would be good to have a solution that solves both the LDAP
and SQL cache
problems, or in other words - the cache API needs to be non
LDAP and non
SQL specific respectively.

Whether we ultimately use the LDAP cache for SQL, or the SQL
cache for
LDAP doesn't matter, as long as the new solution is better
than the
existing ones.

Regards,
Graham
--

Nick Kew wrote:

> Is that the same issue as
> http://issues.apache.org/bugzilla/show_bug.cgi?id=41925

No. The bug describes encryption but I was talking about
validation.
Furthermore the bug shows off a completely other part of the
API.

> My working assumption with DBD is that creating stored
procedures
> is part of database design and initialisation, and not
something
> I'd want the apache user to have privilege to mess
with!  Are you
> talking about something like per-connection stored
procedures?

Writing stored procedures is usually part of the DBA, but if
the database server
is not on the same machine as the web server, stored
procedures are most of the
time faster than SQL statements invoked from the client.
Even when using SQL
statements the Apache admin has to know the tablenames and
the structure of the
tables. And he has to have the privilege to access them.
In that case he either can write the SP by himself or ask
the DBA to do that for
him.

Another point is that the SQL statement could get pretty
large when several
tables are involved for retrieving the passwords or groups.
Not everybody is
able to create their own tables for authentication. Many
people want to use
their already existing tables and then it's much easier to
have just a SP which
does the job.

Having stored procedure support does not force the Apache
admin to use it. It
just gives him the possibility to do so.

Regards,
   Helmut

Am Donnerstag, den 22.03.2007, 14:03 +0100 schrieb Helmut
Tessarek:
> Writing stored procedures is usually part of the DBA,
but if the database server
> is not on the same machine as the web server, stored
procedures are most of the
> time faster than SQL statements invoked from the
client.

The main point with stored procedures is IMHO that thanks to
embedded
SQL they are _the_ way to write readable transaction code
involving
multiple DML statements.

Anyway, st. p. support is good.

Sincerely,
Joachim

Helmut Tessarek wrote:
> Password validation:
> 
> Many web applications use a 32-character hexadecimal
md5 hash, since PHP returns
> such a value for its md5 function. Unfortunately the
apr_password_validate
> function does not validate such a value. Since almost
every authnz backend uses
> this function, it would be perfect to add this
validation process to
> apr_password_validate. I have attached a patch for it.
> 

One problem with your patch is that it wouldn't distinguish
between a 
32-character password (plain-text passwords work on Windows)
and an md5 
hash.

I think it would be better to use a prefix, like .  Not
perfect, 
but the  prefix has already set a precedent for this
in apr.  If 
the database contains only the 32-char hash, you could use
an SQL stmt 
like this:

  "SELECT '' || Password AS Password FROM Users
WHERE UserName = ?"

Helmut Tessarek wrote:
> On 24.03.2007 14:54, Tom Donovan wrote:
>> If procedures only require input parameters and
return a result set
>> (like the example procedure GROUP_SP in
mod_auth_ibmdb2) - they can be
>> invoked by apr_dbd_select with the SQL statement:
>>
>>   "CALL GROUP_SP(?)"
> 
> If this is the case then DBD does support stored
procedures. The other question
> is how are errors handled when using SPs? I guess I
will have to start writing
> the apr_dbd_ibmdb2 driver... 
> 
SP errors seem to be handled the same as with ordinary
SELECT statements.

The DB2 CLI is very similar (almost identical) to ODBC.

I've got an ODBC DBD driver I've used for several weeks now.
 I'm still 
struggling to code the apr 1.3 version of it, but if you (or
anyone) 
would like to look over the apr 1.2 version - help yourself
to 
http:
//www.tomdonovan.net/download/dbd_odbc.zip

Maybe it will help you to write your apr_dbd_ibmdb2 driver.

-tom-

On 30.03.2007 05:24, Tom Donovan wrote:
> One problem with your patch is that it wouldn't
distinguish between a
> 32-character password (plain-text passwords work on
Windows) and an md5
> hash.

This is correct, but plain text passwords are only supported
on Windows because
of the lack of the crypt functionality on Windows.
So it would be unfair not to allow other algorithms for
UNIX, but I think I have
another solution to this.
Another possibility woulde be either:
1) only do my check for non Windows platforms
or (which would be best for both worlds)
2) do the check, and if it does not validate, use plaintext
on Windows

I have attached the new patch (for 2).

In this case my patch does not change anything, except that
it also validates
32-char md5 hashes on all systems.

> I think it would be better to use a prefix, like .
 Not perfect,
> but the  prefix has already set a precedent for
this in apr.  If
> the database contains only the 32-char hash, you could
use an SQL stmt
> like this:
> 
>  "SELECT '' || Password AS Password FROM
Users WHERE UserName = ?"

When using a prefix, then you would have to change either
the rows in the table
or you would have to use a statement as you have suggested.
If you change the statement then only 32-char md5 hashes can
be used for
authentication.
The good thing when using apr_password_validate is that you
can use all the
different hashes in one table.

> SP errors seem to be handled the same as with ordinary
SELECT statements.

Oops, I think my question was not clear. My fault. I know
how to handle errors
in respect of coding. I meant how to handle them logically.

In DB2 you can define SQLSTATES within a SP for different
exceptions. Or you
could get a warning, even if you get the correct result set.
This makes it a
little bit hard to handle errors and warnings...
But since it is already possible to use SPs, these questions
are obsolete.

> I've got an ODBC DBD driver I've used for several weeks
now.  I'm still
> struggling to code the apr 1.3 version of it, but if
you (or anyone)
> would like to look over the apr 1.2 version - help
yourself to
> http:
//www.tomdonovan.net/download/dbd_odbc.zip
> Maybe it will help you to write your apr_dbd_ibmdb2
driver.

Thanks, I will have a look at it. Well I guess that I won't
have to change very
much of it to use it for DB2, because as you said, DB2 CLI
is almost the same as
the ODBC API. It could even be possible that you don't have
to change anything
at all.
See the unified ODBC driver for PHP. Before the native
driver for DB2 was
written, the unified ODBC driver was used to access DB2
natively.

Regards,
   Helmut

Helmut Tessarek wrote:
> On 30.03.2007 05:24, Tom Donovan wrote:
>> One problem with your patch is that it wouldn't
distinguish between a
>> 32-character password (plain-text passwords work on
Windows) and an md5
>> hash.
> 
> This is correct, but plain text passwords are only
supported on Windows because
> of the lack of the crypt functionality on Windows.
> So it would be unfair not to allow other algorithms for
UNIX, but I think I have
> another solution to this.
> Another possibility woulde be either:
> 1) only do my check for non Windows platforms
> or (which would be best for both worlds)
> 2) do the check, and if it does not validate, use
plaintext on Windows
> 
> I have attached the new patch (for 2).
> 
> In this case my patch does not change anything, except
that it also validates
> 32-char md5 hashes on all systems.
> 
>> I think it would be better to use a prefix, like
.  Not perfect,
>> but the  prefix has already set a precedent
for this in apr.  If
>> the database contains only the 32-char hash, you
could use an SQL stmt
>> like this:
>>
>>  "SELECT '' || Password AS Password FROM
Users WHERE UserName = ?"
> 
> When using a prefix, then you would have to change
either the rows in the table
> or you would have to use a statement as you have
suggested.
> If you change the statement then only 32-char md5
hashes can be used for
> authentication.
> The good thing when using apr_password_validate is that
you can use all the
> different hashes in one table.

IMO, these are the wrong approaches, changing APR to support
this, that is.

I would highly prefer we just add a 'password format'
command to
mod_authnz_dbd.  Just like mod_authn_dbi had:
http://mod-auth.sourceforge.net/docs/m
od_authn_dbi/#authndbipasswordformat

-Paul