authenticating a user via rlm_sql

[Stuart Kendrick]

hi,

i'm trying to migrate from a flat 'users' file to postgres, and i'm seeing "No 
'known good' password found for the user" from rlm_pap.  freeradius-2.0.0-pre2


with an empty postgres database, i see radtest/debug traffic like this:

guru> ./radtest steve testing localhost 1234 testing123
[...]
rad_recv: Access-Accept packet from host 127.0.0.1 port 1234, id=172, length=20



rad_recv: Access-Request packet from host 127.0.0.1 port 52838, id=56, length=57
         User-Name = "steve"
         User-Password = "testing"
         NAS-IP-Address = 140.107.74.123
         NAS-Port = 1234
[...]
++[files] returns ok
         expand: %{User-Name} -> steve
rlm_sql (sql): sql_set_user escaped user --> 'steve'
rlm_sql (sql): Reserving sql socket id: 4
[...]
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 0 , fields = 5
[...]
rlm_sql (sql): Released sql socket id: 4
rlm_sql (sql): User steve not found
[...]
rlm_pap: login attempt with password testing
rlm_pap: Using clear text password.
rlm_pap: User authenticated successfully
++[pap] returns ok
Login OK: [steve/testing] (from client localhost port 1234)
Sending Access-Accept of id 56 to 127.0.0.1 port 52838
[...]


that makes sense ... 'steve' does not exist in the postgres database, but is 
defined in '/etc/radbb/users'


so now i go add user 'foozle' to my database:

radius=> select * from radcheck;
  id | username |     attribute      | op | value
----+----------+--------------------+----+-------
   1 | foozle   | Cleartext-Password | == | foo
(1 row)

radius=> select * from radreply;
  id | username |  attribute   | op | value
----+----------+--------------+----+-------
   1 | foozle   | Fall-Through | =  | Yes
(1 row)

radius=> select * from radcheck;
  id | username |     attribute      | op | value
----+----------+--------------------+----+-------
   1 | foozle   | Cleartext-Password | == | foo
(1 row)

radius=> select * from radusergroup;
  username | groupname | priority
----------+-----------+----------
  foozle   | HutchNet  |        0
(1 row)

radius=> select * from radgroupcheck;
  id | groupname | attribute | op | value
----+-----------+-----------+----+-------
(0 rows)

radius=> select * from radgroupreply;
  id | groupname |        attribute        | op |  value
----+-----------+-------------------------+----+----------
   1 | HutchNet  | Tunnel-Type             | := | VLAN
   2 | HutchNet  | Tunnel-Medium-Type      | := | 802
   3 | HutchNet  | Tunnel-Private-Group-ID | := | HutchNet
(3 rows)

radius=>



and test using:
vishnu> ./radtest foozle foo localhost 1234 testing123
[...]
rad_recv: Access-Reject packet from host 127.0.0.1 port 1234, id=166, length=20


[...]
++[files] returns noop
         expand: %{User-Name} -> foozle
rlm_sql (sql): sql_set_user escaped user --> 'foozle'
rlm_sql (sql): Reserving sql socket id: 4
         expand: SELECT id, UserName, Attribute, Value, Op               FROM rad
check         WHERE Username = '%{SQL-User-Name}'               ORDER BY id -> S
ELECT id, UserName, Attribute, Value, Op                FROM radcheck          W
HERE Username = 'foozle'        ORDER BY id
rlm_sql_postgresql: query: SELECT id, UserName, Attribute, Value, Op           F
ROM radcheck    WHERE Username = 'foozle'               ORDER BY id
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 1 , fields = 5
         expand: SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Na
me}' ORDER BY priority -> SELECT GroupName FROM radusergroup WHERE UserName='foo
zle' ORDER BY priority
rlm_sql_postgresql: query: SELECT GroupName FROM radusergroup WHERE UserName='fo
ozle' ORDER BY priority
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 1 , fields = 1
         expand: SELECT id, GroupName, Attribute, Value, op     FROM radgroupchec
k     WHERE GroupName = '%{Sql-Group}'     ORDER BY id -> SELECT id, GroupName,
Attribute, Value, op     FROM radgroupcheck     WHERE GroupName = 'HutchNet'
  ORDER BY id
rlm_sql_postgresql: query: SELECT id, GroupName, Attribute, Value, op     FROM r
adgroupcheck     WHERE GroupName = 'HutchNet'     ORDER BY id
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 0 , fields = 5
rlm_sql (sql): User found in group HutchNet
         expand: SELECT id, GroupName, Attribute, Value, op     FROM radgrouprepl
y     WHERE GroupName = '%{Sql-Group}'     ORDER BY id -> SELECT id, GroupName,
Attribute, Value, op     FROM radgroupreply     WHERE GroupName = 'HutchNet'
  ORDER BY id
rlm_sql_postgresql: query: SELECT id, GroupName, Attribute, Value, op     FROM r
adgroupreply     WHERE GroupName = 'HutchNet'     ORDER BY id
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 3 , fields = 5
rlm_sql (sql): Released sql socket id: 4
++[sql] returns ok
++[expiration] returns noop
++[logintime] returns noop
rlm_pap: WARNING! No "known good" password found for the user.  Authentication m
ay fail because of this.
++[pap] returns noop
auth: No authenticate method (Auth-Type) configuration found for the request: Re
jecting the user
auth: Failed to validate the user.


ok, so why isn't "++[sql] returns ok" good enough?  seems to me that rlm_sql is 
happy ... it found the user, it agrees that the associated password is correct 
... but radiusd keeps on going, to rlm_pap, which does not find a match ... and 
radiusd returns Access-Reject

looking at my /etc/raddb config files ... i've made the following changes from 
default:

radiusd.conf:
log_destination = syslog
user = nobody
group = nobody
log_auth = yes
log_auth_badpass = yes
log_auth_goodpass = yes
proxy_requests = no
$INCLUDE  ${confdir}/sql/postgresql/counter.conf

sql.conf:
database = "postgres"
sqltrace = yes

sites-enabled/default:
         #
         #  Look in an SQL database.  The schema of the database
         #  is meant to mirror the "users" file.
         #
         #  See "Authorization Queries" in sql.conf
         sql


	- suggestions on how to dig a little deeper into why radiusd continues
	  to rlm_pap?

	- sqltrace.sql remains stubbornly empty ... have any tips on populating
	  it?  i've tried running radiusd as 'root' (this to test file system
	  permissions on 'sqltrace.sql' ... but even then, this file remains
	  empty


tia,

--sk

stuart kendrick
fhcrc