Mailing-List: contact dev-help@httpd.apache.org; run by ezmlm
Precedence: bulk
Reply-To: dev@httpd.apache.org
Received-SPF: neutral (athena.apache.org: local policy)
Message-ID: <4A10176F.6000702@kaigai.gr.jp>
Date: Sun, 17 May 2009 22:55:59 +0900
From: KaiGai Kohei <kaigai@kaigai.gr.jp>
User-Agent: Thunderbird 2.0.0.21 (Windows/20090302)
MIME-Version: 1.0
To: dev@httpd.apache.org
Subject: Re: User/Realm order in AuthDBDUserRealmQuery (mod_authn_dbd)
References: <4A0D1CED.1070004@ak.jp.nec.com>
 <4A0EC170.4020900@bellatlantic.net> <4A0FAE0C.7040904@kaigai.gr.jp>
 <4A10082D.4010706@bellatlantic.net>
In-Reply-To: <4A10082D.4010706@bellatlantic.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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 <kaigai@kaigai.gr.jp>