httpd-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From KaiGai Kohei <kai...@ak.jp.nec.com>
Subject Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)
Date Mon, 18 May 2009 00:34:29 GMT
Tom Donovan wrote:
> 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.

It seems to me you overlook my another concern that mod_authn_dbd
does not allow to use variable parameters such as remote address.

> 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.

It cannot be a reason why we enforce a fixed order and fixed type
parameters for other dbd modules. It is not a complex feature to put
parameters based on extra options, so the upcoming mod_session_dbd
should also have an extra option to specify the order/type of parameters.
(Look at my patch, it is less than 200 lines.)

> 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.

It is not a problem in SQL API. If we can pay a bit of efforts to
specify the order/types of parameters, this problem will go away.
I don't think we should spare our efforts and give burdens to users.
Needless to say, I can pay my efforts to solve the problem.

Thanks,
-- 
OSS Platform Development Division, NEC
KaiGai Kohei <kaigai@ak.jp.nec.com>

Mime
View raw message