authenticating a user via rlm_sql

[tnt at kalik.co.yu]

Operator for Cleartext-Password is :=

Ivan Kalik
Kalik Informatika ISP


Dana 16/12/2007, "Stuart Kendrick" <skendric at fhcrc.org> piše:

>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
>-
>List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>