Unable to authenticate freeradius using PostgreSQL
Ross McOwat
ross.mcowat at first-utility.com
Wed Nov 29 11:52:36 CET 2006
OK, I've made progress. I now appear to be connecting to my Postgres
database, as I'm receiving syntax errors in the database log file when
trying a freeRadius authentication. The debug radius output is as
follows (I now have queries against the radius_xlat line):
rad_recv: Access-Request packet from host 10.150.19.134:1115, id=5,
length=49
User-Name = "ROSStest4"
User-Password = "nntlpa401"
Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 0
modcall[authorize]: module "preprocess" returns ok for request 0
modcall[authorize]: module "chap" returns noop for request 0
modcall[authorize]: module "mschap" returns noop for request 0
rlm_realm: No '@' in User-Name = "ROSStest4", looking up realm NULL
rlm_realm: No such realm "NULL"
modcall[authorize]: module "suffix" returns noop for request 0
rlm_eap: No EAP-Message, not doing EAP
modcall[authorize]: module "eap" returns noop for request 0
users: Matched DEFAULT at 156
modcall[authorize]: module "files" returns ok for request 0
radius_xlat: 'ROSStest4'
rlm_sql (sql): sql_set_user escaped user --> 'ROSStest4'
radius_xlat: 'SELECT id, UserName, Attribute, Value, Op
#?authorize_check_query = '
rlm_sql (sql): Reserving sql socket id: 4
rlm_sql_postgresql: query: SELECT id, UserName, Attribute, Value, Op
#?authorize_check_query =
rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
rlm_sql_postgresql: affected rows =
rlm_sql_postgresql: Postgresql check_error: PGRES_FATAL_ERROR, returning
SQL_DOWN
rlm_sql (sql): Attempting to connect rlm_sql_postgresql #4
rlm_sql (sql): Connected new DB handle, #4
rlm_sql_postgresql: query: SELECT id, UserName, Attribute, Value, Op
#?authorize_check_query =
rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
rlm_sql_postgresql: affected rows =
rlm_sql_postgresql: Postgresql check_error: PGRES_FATAL_ERROR, returning
SQL_DOWN
rlm_sql (sql): failed after re-connect
rlm_sql_getvpdata: database query error
rlm_sql (sql): SQL query error; rejecting user
rlm_sql (sql): Released sql socket id: 4
modcall[authorize]: module "sql" returns fail for request 0
modcall: group authorize returns fail for request 0
Finished request 0
Going to the next request
--- Walking the entire request list ---
Waking up in 6 seconds...
rad_recv: Access-Request packet from host 10.150.19.134:1115, id=5,
length=49
Discarding duplicate request from client ROSSpc:1115 - ID: 5
--- Walking the entire request list ---
Waking up in 2 seconds...
--- Walking the entire request list ---
Cleaning up request 0 ID 5 with timestamp 456d62ae
Nothing to do. Sleeping until we see a request.
rad_recv: Access-Request packet from host 10.150.19.134:1115, id=5,
length=49
User-Name = "ROSStest4"
User-Password = "nntlpa401"
Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 1
modcall[authorize]: module "preprocess" returns ok for request 1
modcall[authorize]: module "chap" returns noop for request 1
modcall[authorize]: module "mschap" returns noop for request 1
rlm_realm: No '@' in User-Name = "ROSStest4", looking up realm NULL
rlm_realm: No such realm "NULL"
modcall[authorize]: module "suffix" returns noop for request 1
rlm_eap: No EAP-Message, not doing EAP
modcall[authorize]: module "eap" returns noop for request 1
users: Matched DEFAULT at 156
modcall[authorize]: module "files" returns ok for request 1
radius_xlat: 'ROSStest4'
rlm_sql (sql): sql_set_user escaped user --> 'ROSStest4'
radius_xlat: 'SELECT id, UserName, Attribute, Value, Op
#?authorize_check_query = '
rlm_sql (sql): Reserving sql socket id: 3
rlm_sql_postgresql: query: SELECT id, UserName, Attribute, Value, Op
#?authorize_check_query =
rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
rlm_sql_postgresql: affected rows =
rlm_sql_postgresql: Postgresql check_error: PGRES_FATAL_ERROR, returning
SQL_DOWN
rlm_sql (sql): Attempting to connect rlm_sql_postgresql #3
rlm_sql (sql): Connected new DB handle, #3
rlm_sql_postgresql: query: SELECT id, UserName, Attribute, Value, Op
#?authorize_check_query =
rlm_sql_postgresql: Status: PGRES_FATAL_ERROR
rlm_sql_postgresql: affected rows =
rlm_sql_postgresql: Postgresql check_error: PGRES_FATAL_ERROR, returning
SQL_DOWN
rlm_sql (sql): failed after re-connect
rlm_sql_getvpdata: database query error
rlm_sql (sql): SQL query error; rejecting user
rlm_sql (sql): Released sql socket id: 3
modcall[authorize]: module "sql" returns fail for request 1
modcall: group authorize returns fail for request 1
Finished request 1
Going to the next request
--- Walking the entire request list ---
Waking up in 6 seconds...
--- Walking the entire request list ---
Cleaning up request 1 ID 5 with timestamp 456d62b5
Nothing to do. Sleeping until we see a request.
The following is what is logged in my /var/log/pgsql trace when trying
the above query:
LOG: statement: SELECT id, UserName, Attribute, Value, Op #
authorize_check_query =
ERROR: syntax error at end of input at character 69 (repeated 4
times)
So I suspect now that my user data is incorrect. The output from my
database files is as follows:
radius=# select * from radcheck;
id | username | attribute | value | op
----+-----------+-----------+-----------+----
1 | ROSStest3 | Password | bdwnlc274 | ==
2 | ROSStest4 | Password | nntlpa401 | ==
(2 rows)
radius=# select * from radreply;
id | username | attribute | value | op
----+-----------+-------------------+---------------+----
1 | ROSStest3 | Framed-IP-Address | 10.150.19.134 | :=
2 | ROSStest4 | Framed-IP-Address | 10.150.19.134 | :=
(2 rows)
I have changed the 'value' and 'op' columns around, as the
authentication sql query within postgresql.conf appears to try and
retrieve 'value' before 'op'. Using the supplied database schema, the
op column appears BEFORE value. Not sure if this is significant or not?
Or possibly the actual data format I've entered is incorrect? I suspect
(hope) it's something as simple as that.
-----Original Message-----
From:
freeradius-users-bounces+ross.mcowat=first-utility.com at lists.freeradius.
org
[mailto:freeradius-users-bounces+ross.mcowat=first-utility.com at lists.fre
eradius.org] On Behalf Of Alan DeKok
Sent: 28 November 2006 16:53
To: FreeRadius users mailing list
Subject: Re: Unable to authenticate freeradius using PostgreSQL
"Ross McOwat" <ross.mcowat at first-utility.com> wrote:
> rlm_sql (sql): sql_set_user escaped user --> 'ROSStest4'
> radius_xlat: ''
> rlm_sql (sql): Reserving sql socket id: 4
> rlm_sql (sql): SQL query error; rejecting user
It looks like you haven't configured the SQL queries needed by the
server. The "radius_xlat" line SHOULD be printing out the query it's
going to use.
The default postgresql.conf has queries that work for Postgres. Are
you using those, or have you edited the file?
Alan DeKok.
--
http://deployingradius.com - The web site of the book
http://deployingradius.com/blog/ - The blog
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
More information about the Freeradius-Users
mailing list