Re: Freeradius+Postfresqk+MAC problem
Y id Jul 14 08:40:35 linux postgres[23869]: [6-3] LOCATION: exec_simple_query, postgres.c:974 Jul 14 08:43:35 linux postgres[23868]: [4-1] LOG: 0: statement: SELECT id, UserName, Attribute, Value, Op FROM radchec k WHERE username = '00-20-e0-8d-05-9f' ORDER BY id Jul 14 08:43:35 linux postgres[23868]: [4-2] LOCATION: pg_parse_query, postgres.c:464 Jul 14 08:43:35 linux postgres[23868]: [5-1] LOG: 0: duration: 5.143 ms Jul 14 08:43:35 linux postgres[23868]: [5-2] LOCATION: exec_simple_query, postgres.c:960 Jul 14 08:43:35 linux postgres[23868]: [6-1] LOG: 0: duration: 5.143 ms statement: SELECT id, UserName, Attribute, Va lue, Op FROM radcheck WHERE username = Jul 14 08:43:35 linux postgres[23868]: [6-2] '00-20-e0-8d-05-9f' ORDER BY id Jul 14 08:43:35 linux postgres[23868]: [6-3] LOCATION: exec_simple_query, postgres.c:974 ons, 2004-07-14 kl. 01:13 skrev Gary McKinney: Yes - if everything is configured properly in the radiusd.conf file then you should authenticate properly... What does radiusd -X show??? (I had deleted the original message - DUH!) gm too lazy to search the archives at the moment... - Original Message - From: Christoffer Dahl Petersen To: [EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 12:03 PM Subject: Re: Freeradius+Postfresqk+MAC problem Hi again 1. It doesn't seem to be casesensitive. 2. I have tried to turn on the debug option on the pgsql, and I can see that the query is accepted and the db is returning a result set (with the information requested). I'm not sure that I understand how the Freeradius works with a db as backend, could you (or anyone else) confirm that I'm on the right track: As you can see in my earlier mail I have comment out authorize_reply_query and authenticate_query, which only leave the authorize_check_query back, when this query is tried against the db, it should return a result set, if the MAC is allowed to access my net. So if result set != null Access-Accept Is that right? - Christoffer tir, 2004-07-13 kl. 12:46 skrev Gary McKinney: Hmmm, Looks like most everything is correct - from what you have sent here... A couple of things: 1. Is postgresql case sensitive ( I play with MySQL)??? If so check the case (caps or lower case) of the record field names to make sure the schema's match for the database and queries. 2. Check the debug logs for the database to see exactly what is being done on the database side! From what I see here it looks like the Freeradius is doing it's job properly... As an aside note: When you had the users file setup and the Auth-Type := Accept you were basically telling Freeradius to "accept" any default caller unconditionally - that is what the "Accept" means {grin}... gm...
Re: Freeradius+Postfresqk+MAC problem
>Here is a dump of my database:>[EMAIL PROTECTED] 172.16.0.10]# psql -U radius>radius=> select * from radcheck;>id | username | attribute | op | value>+---+++-> 1 | 00-04-23-4d-c4-3d | User-Password | == | 123456> 2 | 00-20-e0-8d-05-94 | User-Password | == | 123456>(2 rows) How about deleteing all User-Password row instead of Auth-Type := Accept ? Or User-Password := 123456 ? You can have a try. ;)
Re: Freeradius+Postfresqk+MAC problem
ï Yes - if everything is configured properly in the radiusd.conf file then you should authenticate properly... What does radiusd -X show??? (I had deleted the original message - DUH!) gm too lazy to search the archives at the moment... - Original Message - From: Christoffer Dahl Petersen To: [EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 12:03 PM Subject: Re: Freeradius+Postfresqk+MAC problem Hi again1. It doesn't seem to be casesensitive.2. I have tried to turn on the debug option on the pgsql, and I can see that the query is accepted and the db is returning a result set (with the information requested).I'm not sure that I understand how the Freeradius works with a db as backend, could you (or anyone else) confirm that I'm on the right track:As you can see in my earlier mail I have comment out authorize_reply_query and authenticate_query, which only leave the authorize_check_query back, when this query is tried against the db, it should return a result set, if the MAC is allowed to access my net. So if result set != null Access-AcceptIs that right?- Christoffertir, 2004-07-13 kl. 12:46 skrev Gary McKinney: ï Hmmm, Looks like most everything is correct - from what you have sent here... A couple of things: 1. Is postgresql case sensitive ( I play with MySQL)??? If so check the case (caps or lower case) of the record field names to make sure the schema's match for the database and queries. 2. Check the debug logs for the database to see exactly what is being done on the database side! From what I see here it looks like the Freeradius is doing it's job properly... As an aside note: When you had the users file setup and the Auth-Type := Accept you were basically telling Freeradius to "accept" any default caller unconditionally - that is what the "Accept" means {grin}... gm...
Re: Freeradius+Postfresqk+MAC problem
Hi again 1. It doesn't seem to be casesensitive. 2. I have tried to turn on the debug option on the pgsql, and I can see that the query is accepted and the db is returning a result set (with the information requested). I'm not sure that I understand how the Freeradius works with a db as backend, could you (or anyone else) confirm that I'm on the right track: As you can see in my earlier mail I have comment out authorize_reply_query and authenticate_query, which only leave the authorize_check_query back, when this query is tried against the db, it should return a result set, if the MAC is allowed to access my net. So if result set != null Access-Accept Is that right? - Christoffer tir, 2004-07-13 kl. 12:46 skrev Gary McKinney: Hmmm, Looks like most everything is correct - from what you have sent here... A couple of things: 1. Is postgresql case sensitive ( I play with MySQL)??? If so check the case (caps or lower case) of the record field names to make sure the schema's match for the database and queries. 2. Check the debug logs for the database to see exactly what is being done on the database side! From what I see here it looks like the Freeradius is doing it's job properly... As an aside note: When you had the users file setup and the Auth-Type := Accept you were basically telling Freeradius to "accept" any default caller unconditionally - that is what the "Accept" means {grin}... gm...
Re: Freeradius+Postfresqk+MAC problem
ï Hmmm, Looks like most everything is correct - from what you have sent here... A couple of things: 1. Is postgresql case sensitive ( I play with MySQL)??? If so check the case (caps or lower case) of the record field names to make sure the schema's match for the database and queries. 2. Check the debug logs for the database to see exactly what is being done on the database side! From what I see here it looks like the Freeradius is doing it's job properly... As an aside note: When you had the users file setup and the Auth-Type := Accept you were basically telling Freeradius to "accept" any default caller unconditionally - that is what the "Accept" means {grin}... gm... - Original Message - From: Christoffer Dahl Petersen To: [EMAIL PROTECTED] Sent: Tuesday, July 13, 2004 4:40 AM Subject: Freeradius+Postfresqk+MAC problem Hi!As I wrote earlier in this list, I'm trying to get Freeradius to authenticate my clients based on theirs NIC's MAC.This works great as long as I use the "users" file:DEFAULT Calling-Station-Id == "CLIENT NIC", Auth-Type := Accept Filter-ID="profile="">Now I'm trying to use a Postgresql as backend, but it won't work.Here is my radiusd.conf (the entire conf file is in the bottom of the mail):$INCLUDE ${confdir}/postgresql.confauthorize { preprocess sql}Here is my postgresql.conf:sql { driver = "rlm_sql_postgresql" server = "localhost" login = "radius" password = "123456" radius_db = "radius" acct_table1 = "radacct" acct_table2 = "radacct" authcheck_table = "radcheck" authreply_table = "radreply" groupcheck_table = "radgroupcheck" groupreply_table = "radgroupreply" usergroup_table = "usergroup" deletestalesessions = yes sqltrace = yes sqltracefile = ${logdir}/sqltrace.sql num_sql_socks = 5 sql_user_name = "%{User-Name}" SQL_User_Name = "%{User-Name}" authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \FROM ${authcheck_table} WHERE username = '%{SQL-User-Name}' ORDER BY id"# authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op \# FROM ${authreply_table} WHERE username = '%{SQL-User-Name}' ORDER BY id"# authenticate_query = "SELECT Value,Attribute FROM ${authcheck_table} \# WHERE UserName = '%{User-Name}' AND \# ( Attribute = 'User-Password' OR Attribute = 'Crypt-Password' ) ORDER BY Attribute DESC"}Here is a dump of my database:[EMAIL PROTECTED] 172.16.0.10]# psql -U radiusradius=> select * from radcheck;id | username | attribute | op | value+---+++- 1 | 00-04-23-4d-c4-3d | User-Password | == | 123456 2 | 00-20-e0-8d-05-94 | User-Password | == | 123456(2 rows)And here is what my log says:Jul 12 14:39:02 linux radiusd: ^IUser-Name = "00-20-e0-8d-05-94"Jul 12 14:39:02 linux radiusd: ^IUser-Password = "123456"Jul 12 14:39:02 linux radiusd: ^INAS-IP-Address = 172.16.0.10Jul 12 14:39:02 linux radiusd: ^INAS-Port = 0Jul 12 14:39:02 linux radiusd: rlm_sql (sql): Reserving sql socket id: 3Jul 12 14:39:02 linux radiusd: rlm_sql_postgresql: query: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE username = '00-20-e0-8d-05-94' ORDER BY idJul 12 14:39:02 linux postgres[19980]: [5-1] LOG: 0: duration: 5.637 msJul 12 14:39:02 linux postgres[19980]: [5-2] LOCATION: exec_simple_query, postgres.c:960Jul 12 14:39:02 linux postgres[19980]: [6-1] LOG: 0: duration: 5.637 ms statement: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE username =Jul 12 14:39:02 linux postgres[19980]: [6-2] '00-20-e0-8d-05-94' ORDER BY idJul 12 14:39:02 linux postgres[19980]: [6-3] LOCATION: exec_simple_query, postgres.c:974Jul 12 14:39:02 linux radiusd: rlm_sql_postgresql: Status: PGRES_TUPLES_OKJul 12 14:39:02 linux radiusd: rlm_sql_postgresql: affected rows =Jul 12 14:39:02 linux radiusd: rlm_sql (sql): No matching entry in the database for request from user [00-20-e0-8d-05-94]Jul 12 14:39:02 linux radiusd: rlm_sql (sql): Released sql socket id: 3Jul 12 14:39:02 linux radiusd: Login incorrect: [00-20-e0-8d-05-94/123456] (from client testap1 port 0)Jul 12 14:39:05 linux radiusd: rad_recv: Access-Request packet from host 172.16.0.10:6001, id=63, length=69Jul 12 14:39:05 linux radiusd: Sending Access-
Freeradius+Postfresqk+MAC problem
Hi! As I wrote earlier in this list, I'm trying to get Freeradius to authenticate my clients based on theirs NIC's MAC. This works great as long as I use the "users" file: DEFAULT Calling-Station-Id == "CLIENT NIC", Auth-Type := Accept Filter-ID="profile=""> Now I'm trying to use a Postgresql as backend, but it won't work. Here is my radiusd.conf (the entire conf file is in the bottom of the mail): $INCLUDE ${confdir}/postgresql.conf authorize { preprocess sql } Here is my postgresql.conf: sql { driver = "rlm_sql_postgresql" server = "localhost" login = "radius" password = "123456" radius_db = "radius" acct_table1 = "radacct" acct_table2 = "radacct" authcheck_table = "radcheck" authreply_table = "radreply" groupcheck_table = "radgroupcheck" groupreply_table = "radgroupreply" usergroup_table = "usergroup" deletestalesessions = yes sqltrace = yes sqltracefile = ${logdir}/sqltrace.sql num_sql_socks = 5 sql_user_name = "%{User-Name}" SQL_User_Name = "%{User-Name}" authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \ FROM ${authcheck_table} WHERE username = '%{SQL-User-Name}' ORDER BY id" # authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op \ # FROM ${authreply_table} WHERE username = '%{SQL-User-Name}' ORDER BY id" # authenticate_query = "SELECT Value,Attribute FROM ${authcheck_table} \ # WHERE UserName = '%{User-Name}' AND \ # ( Attribute = 'User-Password' OR Attribute = 'Crypt-Password' ) ORDER BY Attribute DESC" } Here is a dump of my database: [EMAIL PROTECTED] 172.16.0.10]# psql -U radius radius=> select * from radcheck; id | username | attribute | op | value +---+++- 1 | 00-04-23-4d-c4-3d | User-Password | == | 123456 2 | 00-20-e0-8d-05-94 | User-Password | == | 123456 (2 rows) And here is what my log says: Jul 12 14:39:02 linux radiusd: ^IUser-Name = "00-20-e0-8d-05-94" Jul 12 14:39:02 linux radiusd: ^IUser-Password = "123456" Jul 12 14:39:02 linux radiusd: ^INAS-IP-Address = 172.16.0.10 Jul 12 14:39:02 linux radiusd: ^INAS-Port = 0 Jul 12 14:39:02 linux radiusd: rlm_sql (sql): Reserving sql socket id: 3 Jul 12 14:39:02 linux radiusd: rlm_sql_postgresql: query: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE username = '00-20-e0-8d-05-94' ORDER BY id Jul 12 14:39:02 linux postgres[19980]: [5-1] LOG: 0: duration: 5.637 ms Jul 12 14:39:02 linux postgres[19980]: [5-2] LOCATION: exec_simple_query, postgres.c:960 Jul 12 14:39:02 linux postgres[19980]: [6-1] LOG: 0: duration: 5.637 ms statement: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE username = Jul 12 14:39:02 linux postgres[19980]: [6-2] '00-20-e0-8d-05-94' ORDER BY id Jul 12 14:39:02 linux postgres[19980]: [6-3] LOCATION: exec_simple_query, postgres.c:974 Jul 12 14:39:02 linux radiusd: rlm_sql_postgresql: Status: PGRES_TUPLES_OK Jul 12 14:39:02 linux radiusd: rlm_sql_postgresql: affected rows = Jul 12 14:39:02 linux radiusd: rlm_sql (sql): No matching entry in the database for request from user [00-20-e0-8d-05-94] Jul 12 14:39:02 linux radiusd: rlm_sql (sql): Released sql socket id: 3 Jul 12 14:39:02 linux radiusd: Login incorrect: [00-20-e0-8d-05-94/123456] (from client testap1 port 0) Jul 12 14:39:05 linux radiusd: rad_recv: Access-Request packet from host 172.16.0.10:6001, id=63, length=69 Jul 12 14:39:05 linux radiusd: Sending Access-Reject of id 63 to 172.16.0.10:6001 I really don't know what I'm doing wrong - Could anyone of give me a hint? If you need to see any other configuration files please let me know. Thanks Christoffer Me entire radiusd.conf: prefix = /usr exec_prefix = /usr sysconfdir = /etc localstatedir = /var sbindir = /usr/sbin logdir = ${localstatedir}/log/radius raddbdir = ${sysconfdir}/raddb radacctdir = ${logdir}/radacct confdir = ${raddbdir} run_dir = ${localstatedir}/run/radiusd log_file = ${logdir}/radius.log libdir = /usr/lib pidfile = ${run_dir}/radiusd.pid user = radiusd group = radiusd max_request_time = 30 delete_blocked_requests = no cleanup_delay = 5 max_requests = 1024 bind_address = * port = 0 hostname_lookups = no allow_core_dumps = yes regular_expressions = yes extended_expressions = yes log_stripped_names = no log_auth = yes log_auth_badpass = yes log_auth_goodpass = yes usercollide = no lower_user = no lower_pass = no nospace_user = no nospace_pass = no checkrad = ${sbindir}/checkrad security { max_attributes = 200 reject_delay = 1 status_server = no } proxy_requests = yes $INCLUDE ${confdir}/proxy.conf $INCLUDE ${confdir}/clients.conf thread pool { start_servers = 5 max_servers = 32 min_spare_servers = 3 max_spare_servers = 10 max_requests_per_serve