httpd-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tom Donovan <donov...@bellatlantic.net>
Subject Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)
Date Sun, 17 May 2009 14:43:43 GMT
KaiGai Kohei wrote:
> Tom Donovan wrote:
>> Yes, SQL *functions* only return a single value - but if your database 
>> supports SQL *stored procedures* (like the example), they return a set 
>> of rows; including any extra values to be assigned to environment 
>> variables.  For example:
>>
>>    DROP PROCEDURE IF EXISTS digest;
>>    CREATE PROCEDURE digest(username VARCHAR(64), realm VARCHAR(64))
>>      SELECT md5(concat(uname,':', realm ,':', upass)), uctx AS 
>> CONTEXT, uexpiration AS EXPIRES
>>      FROM uaccount WHERE uname = username;
>>
>> When httpd executes the CALL statement from:
>>
>>    AuthDBDUserRealmQuery "CALL digest(%s, %s)"
>>
>> this will authenticate the user, and if successful - it will also set 
>> the two httpd environment variables AUTHENTICATE_CONTEXT and 
>> AUTHENTICATE_EXPIRES to values from the database.
>>
>> Stored procedures are available in MySQL, Oracle, and several other 
>> databases - but some databases, like PostgreSQL and SQLite, do not 
>> support them.
> 
> My target is PostgreSQL, and its function has a regional dialect. :-)
> 
> Example)
>  apache=# CREATE OR REPLACE FUNCTION digest_f (TEXT, TEXT) RETURNS 
> RECORD LANGUAGE 'sql' AS
>               'SELECT md5(uname || '':'' || $2 || '':'' || upass), 
> context FROM uaccount WHERE uname = $1';
>  CREATE FUNCTION
>  apache=# SELECT * FROM digest_f ('foo', 'sample realm') AS (hash text, 
> context text);
>                 hash               | context
>  ----------------------------------+---------
>   29dd4bc0ed3d043849fc3efbb05876cd | *:s0:c0
>  (1 row)
> 
> However, I would like to consider this kind of avoidance as an independent
> topic (or a workaround for a while). The fixed order parameters are really
> an limitation on writing a query for mod_authn_dbd, so it should be fixed.
> 
> Thanks,

I understand why you would prefer the simpler alternative of AuthDBDRealmUserQuery, but I
disagree 
with your conclusion.

Your argument appears reasonable for the current set of dbd modules, where AuthDBDUserRealmQuery
is 
the only query specified with multiple parameters - but in future Apache versions there may
be more.

For example: http://httpd.apache.org/docs/2.3/mod/mod_session_dbd.html requires five queries

(deletesession, updatesession, insertsession, selectsession and cleansession).  Three of these

queries take multiple parameters. We probably won't want to introduce variations of these
queries 
for whenever the argument order is inconvenient to a particular database.  Ditto for other
future 
dbd modules.

This is a common problem whenever a "SQL API" is used - and the commonly-used solution (stored

procedures, or "functions returning rows" in the case of PostgreSQL) seems like a better way
to go 
than adding additional options to Apache whenever a configuration requires some extra SQL
coding effort.

Nevertheless, it is a matter of opinion - and this is just my 2-cents worth...

Regards,
-tom-

Mime
View raw message