You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@httpd.apache.org by KaiGai Kohei <ka...@ak.jp.nec.com> on 2009/05/15 09:42:37 UTC

User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

I'm now trying to set up mod_authn_dbb for authentication purpose.
However, I faced to a concern for AuthDBDUserRealmQuery directive.

The example shows the query:
  AuthDBDUserRealmQuery \
      "SELECT password FROM authn WHERE user = %s AND realm = %s"

But, I would like to set up the query as follows:
  AuthDBDUserRealmQuery \
      "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount WHERE uname = %s"
                                  ^^... to be realm                to be user ... ^^

It seems to me we have no way to put the replacement of the given
realm prior to username. Am I missing anything?

If we have no reasonable workaround, I would like to suggest a new
directive: AuthDBDRealmUserQuery which specifies a query for digest
authentication with realm and user parameters in this order?

What's your opinion?

# This is an aside. I would like to include a few additional conditions
# in the query, such as remote address and so on.
# For example, we can consider a web-user who can access via a certain
# network address (like, 192.168.1.0/24), described as:
#
# SELECT md5(password) FROM uaccount \
#     WHERE uname = %s AND unetwork >>= %s::inet;

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

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by KaiGai Kohei <ka...@ak.jp.nec.com>.
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 <ka...@ak.jp.nec.com>

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by Tom Donovan <do...@bellatlantic.net>.
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-

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by KaiGai Kohei <ka...@kaigai.gr.jp>.
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,
-- 
KaiGai Kohei <ka...@kaigai.gr.jp>

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by Tom Donovan <do...@bellatlantic.net>.
KaiGai Kohei wrote:
> Tom Donovan wrote:
>> KaiGai Kohei wrote:
>>> I'm now trying to set up mod_authn_dbb for authentication purpose.
>>> However, I faced to a concern for AuthDBDUserRealmQuery directive.
>>>
>>> The example shows the query:
>>>   AuthDBDUserRealmQuery \
>>>       "SELECT password FROM authn WHERE user = %s AND realm = %s"
>>>
>>> But, I would like to set up the query as follows:
>>>   AuthDBDUserRealmQuery \
>>>       "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount 
>>> WHERE uname = %s"
>>>                                   ^^... to be realm                to 
>>> be user ... ^^
>>>
>>> It seems to me we have no way to put the replacement of the given
>>> realm prior to username. Am I missing anything?
>>>
>>
>> One common solution to the 'order of parameters' problem is to create 
>> a stored procedure in your database.  For example, if you are using 
>> MySQL 5.0+, you can create a stored procedure like this:
>>
>>   DROP PROCEDURE IF EXISTS digest;
>>   CREATE PROCEDURE digest(username VARCHAR(64), realm VARCHAR(64))
>>     SELECT md5(concat(uname ,':',realm ,':',upass)) FROM uaccount 
>> WHERE uname = username;
>>
>> Then in your conf file use:
>>
>>   AuthDBDUserRealmQuery "CALL digest(%s, %s)"
> 
> Thanks for your idea.
> 
> But it still remains a matter for me.
> The mod_authn_dbd allows to export extra fields as environment variables
> with AUTHENTICATE_<field name>. But SQL function (generically) returns
> one-dimensional value, so this idea cannot allow to return anything related
> to authenticated users except for hash-value.
> 
> What I would like to do is to fetch a security context to be assigned to 
> users.
> My mod_selinux module enables to assign a security context prior to 
> invocation
> of contents handler based on a certain environment variable. So, I would 
> like
> to fetch an extra information related to authorized user.
> For the purpose, we need more flexibility to place parameters in query.
> 
> Thanks,

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.

-tom-

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by KaiGai Kohei <ka...@kaigai.gr.jp>.
Tom Donovan wrote:
> KaiGai Kohei wrote:
>> I'm now trying to set up mod_authn_dbb for authentication purpose.
>> However, I faced to a concern for AuthDBDUserRealmQuery directive.
>>
>> The example shows the query:
>>   AuthDBDUserRealmQuery \
>>       "SELECT password FROM authn WHERE user = %s AND realm = %s"
>>
>> But, I would like to set up the query as follows:
>>   AuthDBDUserRealmQuery \
>>       "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount 
>> WHERE uname = %s"
>>                                   ^^... to be realm                to 
>> be user ... ^^
>>
>> It seems to me we have no way to put the replacement of the given
>> realm prior to username. Am I missing anything?
>>
> 
> One common solution to the 'order of parameters' problem is to create a 
> stored procedure in your database.  For example, if you are using MySQL 
> 5.0+, you can create a stored procedure like this:
> 
>   DROP PROCEDURE IF EXISTS digest;
>   CREATE PROCEDURE digest(username VARCHAR(64), realm VARCHAR(64))
>     SELECT md5(concat(uname ,':',realm ,':',upass)) FROM uaccount WHERE 
> uname = username;
> 
> Then in your conf file use:
> 
>   AuthDBDUserRealmQuery "CALL digest(%s, %s)"

Thanks for your idea.

But it still remains a matter for me.
The mod_authn_dbd allows to export extra fields as environment variables
with AUTHENTICATE_<field name>. But SQL function (generically) returns
one-dimensional value, so this idea cannot allow to return anything related
to authenticated users except for hash-value.

What I would like to do is to fetch a security context to be assigned to users.
My mod_selinux module enables to assign a security context prior to invocation
of contents handler based on a certain environment variable. So, I would like
to fetch an extra information related to authorized user.
For the purpose, we need more flexibility to place parameters in query.

Thanks,
-- 
KaiGai Kohei <ka...@kaigai.gr.jp>

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by Tom Donovan <do...@bellatlantic.net>.
KaiGai Kohei wrote:
> I'm now trying to set up mod_authn_dbb for authentication purpose.
> However, I faced to a concern for AuthDBDUserRealmQuery directive.
> 
> The example shows the query:
>   AuthDBDUserRealmQuery \
>       "SELECT password FROM authn WHERE user = %s AND realm = %s"
> 
> But, I would like to set up the query as follows:
>   AuthDBDUserRealmQuery \
>       "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount WHERE uname = %s"
>                                   ^^... to be realm                to be user ... ^^
> 
> It seems to me we have no way to put the replacement of the given
> realm prior to username. Am I missing anything?
> 

One common solution to the 'order of parameters' problem is to create a stored procedure in your 
database.  For example, if you are using MySQL 5.0+, you can create a stored procedure like this:

   DROP PROCEDURE IF EXISTS digest;
   CREATE PROCEDURE digest(username VARCHAR(64), realm VARCHAR(64))
     SELECT md5(concat(uname ,':',realm ,':',upass)) FROM uaccount WHERE uname = username;

Then in your conf file use:

   AuthDBDUserRealmQuery "CALL digest(%s, %s)"

-tom-

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by KaiGai Kohei <ka...@ak.jp.nec.com>.
I filed the patch as:
  https://issues.apache.org/bugzilla/show_bug.cgi?id=47295

Similar implementation shall also be possible on mod_dbd_session.
If necessary, I'll implement it next to the mod_authn_dbd.

--------
This patch adds a two new directives (AuthDBDUserPWQueryFmt,
AuthDBDUserRealmQueryFmt) on mod_authn_dbd.

These options allow to deploy various kind of query parameters (not only
username, password and realm) in discretionary order.
Needless to say, you can use the existing directives, if here is no concerns.

This patch enables to apply mod_authn_dbd on the following cases also.

1. Hardwired parameter order is not suitable for the database.

SELECT md5(uname || ':' || %s || ':' upass) FROM uaccount WHERE uname = %s;

If we want to execute the query (the 1st %s should be realm, and the 2nd %s
should be username) for digest authentication, the hardwired parameter order
is not suitable for the current AuthDBDUserRealmQuery option.

The new AuthDBDUserRealmQueryFmt allows to specify the order as follows:

AuthDBDUserRealmQueryFmt \
    "SELECT md5(uname || ':' || $(realm) || ':' upass) \
         FROM uaccount WHERE uname = $(username)"

2. Additional conditions more than username/password.

When we want to restrict available users depending on remote address or
other factors, the current directive does not support it.

This patch allows to put $(remote_addr) other than username, password and
realm, as a proof of the concept. It can be used to implement a user who
is available only from local networks, for example.

Thanks,

KaiGai Kohei wrote:
> KaiGai Kohei wrote:
>> I'm now trying to set up mod_authn_dbb for authentication purpose.
>> However, I faced to a concern for AuthDBDUserRealmQuery directive.
>>
>> The example shows the query:
>>   AuthDBDUserRealmQuery \
>>       "SELECT password FROM authn WHERE user = %s AND realm = %s"
>>
>> But, I would like to set up the query as follows:
>>   AuthDBDUserRealmQuery \
>>       "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount WHERE uname = %s"
>>                                   ^^... to be realm                to be user ... ^^
>>
>> It seems to me we have no way to put the replacement of the given
>> realm prior to username. Am I missing anything?
> 
> Here, I could find a short hack.
> 
>   AuthDBDUserRealmQuery \
>       "SELECT md5(uname || ':' || $2 || ':' || upass), udomain, %s=%s AS dummy \
>            FROM uaccount WHERE uname = $1"
> 
> The first %s is replaced to '$1' as username, and the second %s is replaced
> to '$2' as a realm, but $n is not touched by mod_dbd.
> The dummy field is just put to consume the parameters in correct order, and
> it refers meaningful parameters with $n.
> 
> However, I don't think it is a straightforward approach. :-(
> 
> Chris Darroch suggested me to add an optional second argument to suggest
> the order of parameters, like:
> 
>   AuthDBDUserRealmQuery \
>     "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount \
>          WHERE uname = %s" "realm,username"
> 
> However, my preference is still an inline replacement approach, like:
> 
>   AuthDBDUserRealmQueryFmt \
>     "SELECT md5(uname || ':' || $(realm) || ':' || upass) FROM uaccount \
>          WHERE uname = $(username) and unetwork >>= $(remote_host)::inet"
> 
> Needless to say, the current behavior of AuthDBDUserRealmQuery should be
> kept as is. The new directive only suggest an another way to set up the
> query.
> 
> Chris also mentioned we should use the custome log format as much as possible.
>   http://httpd.apache.org/docs/2.2/mod/mod_log_config.html#formats
> 
> However, Tom Donovan mentioned that the upcoming mod_session_dbd module also
> applies hardwired parameters, and it requires to replace session keys and
> so on. But we could not find these parameters in the formats.
> 
> Therefore, it seems to me these identical marks should be defined independent
> from the custom log format.
> 
> Examples:
>  -- mod_auth_dbd --
>  $(username) ... replaced by %s as the given authenticated username
>  $(password) ... replaced by %s as the given authenticated password
>  $(realm)    ... replaced by %s as the realm string
>  $(remote_addr) ... replaced by %s as the remote address
>  -- mod_session_dbd --
>  $(key)      ... replaced by %s as the session key
>  $(value)    ... replaced by %s as the session value
>  $(expiry)   ... replaced by %lld as the session expity
> 
> Thanks,


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

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by KaiGai Kohei <ka...@ak.jp.nec.com>.
KaiGai Kohei wrote:
> I'm now trying to set up mod_authn_dbb for authentication purpose.
> However, I faced to a concern for AuthDBDUserRealmQuery directive.
> 
> The example shows the query:
>   AuthDBDUserRealmQuery \
>       "SELECT password FROM authn WHERE user = %s AND realm = %s"
> 
> But, I would like to set up the query as follows:
>   AuthDBDUserRealmQuery \
>       "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount WHERE uname = %s"
>                                   ^^... to be realm                to be user ... ^^
> 
> It seems to me we have no way to put the replacement of the given
> realm prior to username. Am I missing anything?

Here, I could find a short hack.

  AuthDBDUserRealmQuery \
      "SELECT md5(uname || ':' || $2 || ':' || upass), udomain, %s=%s AS dummy \
           FROM uaccount WHERE uname = $1"

The first %s is replaced to '$1' as username, and the second %s is replaced
to '$2' as a realm, but $n is not touched by mod_dbd.
The dummy field is just put to consume the parameters in correct order, and
it refers meaningful parameters with $n.

However, I don't think it is a straightforward approach. :-(

Chris Darroch suggested me to add an optional second argument to suggest
the order of parameters, like:

  AuthDBDUserRealmQuery \
    "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount \
         WHERE uname = %s" "realm,username"

However, my preference is still an inline replacement approach, like:

  AuthDBDUserRealmQueryFmt \
    "SELECT md5(uname || ':' || $(realm) || ':' || upass) FROM uaccount \
         WHERE uname = $(username) and unetwork >>= $(remote_host)::inet"

Needless to say, the current behavior of AuthDBDUserRealmQuery should be
kept as is. The new directive only suggest an another way to set up the
query.

Chris also mentioned we should use the custome log format as much as possible.
  http://httpd.apache.org/docs/2.2/mod/mod_log_config.html#formats

However, Tom Donovan mentioned that the upcoming mod_session_dbd module also
applies hardwired parameters, and it requires to replace session keys and
so on. But we could not find these parameters in the formats.

Therefore, it seems to me these identical marks should be defined independent
from the custom log format.

Examples:
 -- mod_auth_dbd --
 $(username) ... replaced by %s as the given authenticated username
 $(password) ... replaced by %s as the given authenticated password
 $(realm)    ... replaced by %s as the realm string
 $(remote_addr) ... replaced by %s as the remote address
 -- mod_session_dbd --
 $(key)      ... replaced by %s as the session key
 $(value)    ... replaced by %s as the session value
 $(expiry)   ... replaced by %lld as the session expity

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

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by KaiGai Kohei <ka...@kaigai.gr.jp>.
Chris Darroch wrote:
> KaiGai Kohei wrote:
> 
>> I think a new directive with formats support is preferable to
>> keep compatibility with existing directives.
> 
>   We definitely need compatibility with existing directives.
> That's why I figured the extra parameter would be optional --
> if you only provided one parameter, the query, it would be handled
> as it is now (i.e., user and realm interpolated, in that order).

Indeed, apache/httpd allows us to handle an extra parameter easily.
The reason why I wondered the extra parameter approch was that I think
the custom log format caracters (like %a, %u) should be included within
the query string. But it is not a fundamental thing. :-)

>> For example:
>>    AuthDBDUserRealmQueryFmt \
>>        "SELECT md5(uname || ':' || %R || ':' || upass) FROM uaccount \
>>             WHERE uname = %u AND uaddr >>= %a::inet"
>>
>> When the directive is given, mod_authn_dbd can register the type and
>> order of the charater to be replaced. Then it can set up as a paramter
>> list on query execution phase.
> 
>   I think you'll find that trying to change the format specifiers
> (e.g., "%u", "%a::inet", etc.) is not the way to go.  These are
> handled by APR-util, not mod_authn_dbd.  At least in APR trunk,
> there's a large set of format specifiers supported and these were
> hashed through some time ago (e.g., "%pa" for date/timestamps, etc.)
> on the APR lists.  I don't think it's a good idea to start adding
> more DB query format specifier parsing in httpd.

I din't have a plan to add support for whole of custom log format
and all mod_authn_dbd should do is replacing them to '%s' and register
the appeared order. However, it is not a fundamental thing.

>   That's why I figured you might stick with APR-util's specifiers
> in the query itself, but then provide a comma-delimited list of
> specifiers in the optional argument to the AuthDBD* directives.
> These would be follow mod_log_config and would represent the various
> request-related data one could interpolate into the query.
> (E.g., "u" for user, "H" for protocol, "a" for remote address, etc.)
> That would provide a vast number of possibilities (more, certainly,
> than anyone really needs) but would at least stick to existing
> format specifiers as defined for both APR-util DBD and httpd now.
> 
>> Should I submit a patch to support the feature?
> 
>   Patches are always welcome (although I'm the first to admit
> that I'm too slow at reviewing and committing them).

The attached patch is a proof of the concept.
It allows to specify the order of parameters, as follows:

   AuthDBDUserRealmQuery \
       "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount \
        WHERE uname = %s"  "realm,user"
                           ^^^^^^^^^^^^
The second argument is optional. If nothing was given, mod_authn_dbd
considers the parameter should be extracted with the default order
("user" for basic authentication, and "user,realm" for digest one).

Currently, it only supports "user", "password", "realm" and "remote_addr".
Its coverage is now far from custom log format, and I'm not clear whether
different stuffs should have similar configuration (may be confusable?),
so this patches uses different token.

Any comments please.
Thanks,
-- 
KaiGai Kohei <ka...@kaigai.gr.jp>

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by Chris Darroch <ch...@pearsoncmg.com>.
KaiGai Kohei wrote:

> I think a new directive with formats support is preferable to
> keep compatibility with existing directives.

   We definitely need compatibility with existing directives.
That's why I figured the extra parameter would be optional --
if you only provided one parameter, the query, it would be handled
as it is now (i.e., user and realm interpolated, in that order).

> For example:
>    AuthDBDUserRealmQueryFmt \
>        "SELECT md5(uname || ':' || %R || ':' || upass) FROM uaccount \
>             WHERE uname = %u AND uaddr >>= %a::inet"
> 
> When the directive is given, mod_authn_dbd can register the type and
> order of the charater to be replaced. Then it can set up as a paramter
> list on query execution phase.

   I think you'll find that trying to change the format specifiers
(e.g., "%u", "%a::inet", etc.) is not the way to go.  These are
handled by APR-util, not mod_authn_dbd.  At least in APR trunk,
there's a large set of format specifiers supported and these were
hashed through some time ago (e.g., "%pa" for date/timestamps, etc.)
on the APR lists.  I don't think it's a good idea to start adding
more DB query format specifier parsing in httpd.

   That's why I figured you might stick with APR-util's specifiers
in the query itself, but then provide a comma-delimited list of
specifiers in the optional argument to the AuthDBD* directives.
These would be follow mod_log_config and would represent the various
request-related data one could interpolate into the query.
(E.g., "u" for user, "H" for protocol, "a" for remote address, etc.)
That would provide a vast number of possibilities (more, certainly,
than anyone really needs) but would at least stick to existing
format specifiers as defined for both APR-util DBD and httpd now.

> Should I submit a patch to support the feature?

   Patches are always welcome (although I'm the first to admit
that I'm too slow at reviewing and committing them).

Chris.

-- 
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263  E4DE C8E3 FA36 366A 375B


Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by KaiGai Kohei <ka...@kaigai.gr.jp>.
Chris Darroch wrote:
> KaiGai Kohei wrote:
> 
>> But, I would like to set up the query as follows:
>>   AuthDBDUserRealmQuery \
>>       "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount 
>> WHERE uname = %s"
>>                                   ^^... to be realm                to 
>> be user ... ^^
>>
>> It seems to me we have no way to put the replacement of the given
>> realm prior to username. Am I missing anything?
> 
>   I don't think so ... unless there's some way to rewrite the query
> so the username is the first parameter, I don't see any option with
> the existing code.

Hmm...
In this case, the realm is only used in md5(), not appeared in WHERE
clause, so it is hard to reorder them.

>> If we have no reasonable workaround, I would like to suggest a new
>> directive: AuthDBDRealmUserQuery which specifies a query for digest
>> authentication with realm and user parameters in this order?
>>
>> What's your opinion?
>>
>> # This is an aside. I would like to include a few additional conditions
>> # in the query, such as remote address and so on.
>> # For example, we can consider a web-user who can access via a certain
>> # network address (like, 192.168.1.0/24), described as:
>> #
>> # SELECT md5(password) FROM uaccount \
>> #     WHERE uname = %s AND unetwork >>= %s::inet;
> 
>   I wonder if we could keep the existing config directives but
> allow them to access an optional additional parameter (or set of
> parameters).  You could then write:
> 
> AuthDBDUserRealmQuery \
>    "SELECT %s FROM uaccount WHERE uname = %s AND foo = %s" \
>    R,u,a
> 
> where R,u,a meant realm, user, and remote IP address parameters
> were to be passed in that order.  (We'd likely want to follow
> http://httpd.apache.org/docs/2.2/mod/mod_log_config.html#formats
> as much as possible for the available parameters.)  Just an idea
> at the moment ... alas, no matching implementation.  Sigh.  :-/

I think a new directive with formats support is preferable to
keep compatibility with existing directives.

For example:
   AuthDBDUserRealmQueryFmt \
       "SELECT md5(uname || ':' || %R || ':' || upass) FROM uaccount \
            WHERE uname = %u AND uaddr >>= %a::inet"

When the directive is given, mod_authn_dbd can register the type and
order of the charater to be replaced. Then it can set up as a paramter
list on query execution phase.

Should I submit a patch to support the feature?

Thanks,
-- 
KaiGai Kohei <ka...@kaigai.gr.jp>

Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)

Posted by Chris Darroch <ch...@pearsoncmg.com>.
KaiGai Kohei wrote:

> But, I would like to set up the query as follows:
>   AuthDBDUserRealmQuery \
>       "SELECT md5(uname || ':' || %s || ':' || upass) FROM uaccount WHERE uname = %s"
>                                   ^^... to be realm                to be user ... ^^
> 
> It seems to me we have no way to put the replacement of the given
> realm prior to username. Am I missing anything?

   I don't think so ... unless there's some way to rewrite the query
so the username is the first parameter, I don't see any option with
the existing code.

> If we have no reasonable workaround, I would like to suggest a new
> directive: AuthDBDRealmUserQuery which specifies a query for digest
> authentication with realm and user parameters in this order?
> 
> What's your opinion?
> 
> # This is an aside. I would like to include a few additional conditions
> # in the query, such as remote address and so on.
> # For example, we can consider a web-user who can access via a certain
> # network address (like, 192.168.1.0/24), described as:
> #
> # SELECT md5(password) FROM uaccount \
> #     WHERE uname = %s AND unetwork >>= %s::inet;

   I wonder if we could keep the existing config directives but
allow them to access an optional additional parameter (or set of
parameters).  You could then write:

AuthDBDUserRealmQuery \
    "SELECT %s FROM uaccount WHERE uname = %s AND foo = %s" \
    R,u,a

where R,u,a meant realm, user, and remote IP address parameters
were to be passed in that order.  (We'd likely want to follow
http://httpd.apache.org/docs/2.2/mod/mod_log_config.html#formats
as much as possible for the available parameters.)  Just an idea
at the moment ... alas, no matching implementation.  Sigh.  :-/

Chris.

-- 
GPG Key ID: 366A375B
GPG Key Fingerprint: 485E 5041 17E1 E2BB C263  E4DE C8E3 FA36 366A 375B