All sorted! I copied a fresh-install postgresql.conf file from another server, and bingo, all is working fine. Must have made a mistake while editing this file - note to self, make copies of the file BEFORE editing it!
Thanks for everyones help. Ross -----Original Message----- From: [EMAIL PROTECTED] org [mailto:[EMAIL PROTECTED] eradius.org] On Behalf Of Ross McOwat Sent: 29 November 2006 10:53 To: FreeRadius users mailing list Subject: RE: Unable to authenticate freeradius using PostgreSQL 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: [EMAIL PROTECTED] org [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html