SQL SELECT statement
Hello, I would like to update Cisco-AVPair attribute using a SQL request : update reply { Cisco-AVPair += "%{sql:SELECT value FROM radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair' ORDER BY id}" } But only the first row is returned. Charlye
On May 18, 2020, at 4:42 AM, Charly <netflow@geosciences.ensmp.fr> wrote:
Hello,
I would like to update Cisco-AVPair attribute using a SQL request :
update reply { Cisco-AVPair += "%{sql:SELECT value FROM radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair' ORDER BY id}" }
But only the first row is returned.
Yes. The internal APIs have no way of returning multiple values from an SQL SELECT statement. This will be fixed in v4. Alan DeKok.
Thank you Alan. Do you know if i can use another way to do it ? Le 18/05/2020 à 15:00, Alan DeKok a écrit :
On May 18, 2020, at 4:42 AM, Charly <netflow@geosciences.ensmp.fr> wrote:
Hello,
I would like to update Cisco-AVPair attribute using a SQL request :
update reply { Cisco-AVPair += "%{sql:SELECT value FROM radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair' ORDER BY id}" }
But only the first row is returned. Yes. The internal APIs have no way of returning multiple values from an SQL SELECT statement.
This will be fixed in v4.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
For user authentication, i use SQL and LDAP modules but Cisco-AVPair values are only stored in SQL. My config is : sql update control { SQL-Test = "%{%{sql:SELECT id FROM radcheck WHERE username = '%{User-Name}'}:-notfound}" } if (&control:SQL-Test == "notfound") { update reply { Cisco-AVPair += "%{sql:SELECT value FROM radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair' ORDER BY id}" } # # The ldap module reads passwords from the LDAP database. LDAP if ((ok || updated) && User-Password) { update control { Auth-Type := LDAP } } } Maybe it exists another way to do it ? Le 18/05/2020 à 17:26, Alan DeKok a écrit :
On May 18, 2020, at 11:23 AM, Charly <netflow@geosciences.ensmp.fr> wrote:
Thank you Alan. Do you know if i can use another way to do it ? If you're using the standard schema, why not just use the standard SQL qeuries? i.e. use the SQL module the way it's designed.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On May 18, 2020, at 11:50 AM, Charly <netflow@geosciences.ensmp.fr> wrote:
For user authentication, i use SQL and LDAP modules but Cisco-AVPair values are only stored in SQL. My config is :
sql
Which runs the normal sql queries.
update control { SQL-Test = "%{%{sql:SELECT id FROM radcheck WHERE username = '%{User-Name}'}:-notfound}" }
Why? The SQL module automatically processes the radcheck table.
if (&control:SQL-Test == "notfound") { update reply { Cisco-AVPair += "%{sql:SELECT value FROM radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair' ORDER BY id}" }
Again... why? The SQL module automatically processes the radreply table.
Maybe it exists another way to do it ?
To do *what*? The SQL module automatically processes the radcheck and radreply tables. You're trying to do something special here, but I don't know what. And you're not explaining anything. Just "I configured a bunch of stuff, but it doesn't do what I want'. Well, what do you want it to do? Use words. Explain. I can't read your mind. And because you're mis-using the configuration, I don't know what you expect it to do, either. Alan DeKok.
- i have local users stored in SQL database - i have LDAP users - in SQL database, i store Cisco-AVPair values for both of them The SQL module doesn't process the radreply when user is not found in the radcheck table (=> LDAP users) Le 18/05/2020 à 18:04, Alan DeKok a écrit :
On May 18, 2020, at 11:50 AM, Charly <netflow@geosciences.ensmp.fr> wrote:
For user authentication, i use SQL and LDAP modules but Cisco-AVPair values are only stored in SQL. My config is :
sql Which runs the normal sql queries.
update control { SQL-Test = "%{%{sql:SELECT id FROM radcheck WHERE username = '%{User-Name}'}:-notfound}" }
Why? The SQL module automatically processes the radcheck table.
if (&control:SQL-Test == "notfound") { update reply { Cisco-AVPair += "%{sql:SELECT value FROM radreply WHERE username = '%{User-Name}' AND attribute = 'Cisco-AVPair' ORDER BY id}" }
Again... why? The SQL module automatically processes the radreply table.
Maybe it exists another way to do it ? To do *what*? The SQL module automatically processes the radcheck and radreply tables.
You're trying to do something special here, but I don't know what. And you're not explaining anything. Just "I configured a bunch of stuff, but it doesn't do what I want'.
Well, what do you want it to do? Use words. Explain. I can't read your mind. And because you're mis-using the configuration, I don't know what you expect it to do, either.
Alan DeKok.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On May 19, 2020, at 3:38 AM, Charly <netflow@geosciences.ensmp.fr> wrote:
- i have local users stored in SQL database - i have LDAP users - in SQL database, i store Cisco-AVPair values for both of them
That's fine.
The SQL module doesn't process the radreply when user is not found in the radcheck table (=> LDAP users)
That's how the SQL module is defined to work... see https://wiki.freeradius.org/modules/Rlm_sql You can't just put random SQL queries into the server and expect them to re-implement the entire SQL module. What you need to do is to re-phrase your problem: if user is in ldap then look user up in SQL with a fixed name else look user up in SQL with their user name This is relatively simple. If you're not using realms / 802.1X, you can do: authorize { ... ldap if (ok) { update request { Stripped-User-Name := "LDAP" } } sql ... And then edit the file mods-config/sql/main/mysql/queries.conf, to set: sql_user_name = "%{Stripped-User-Name}:-%{%{User-Name}}" This will cause the SQL module to use Stripped-User-Name if it exists, otherwise it uses User-Name. And then the LDAP users can have a default entry in SQL, with user name "LDAP". You can, of course, change that to anything else you want. Alan DeKok.
participants (2)
-
Alan DeKok -
Charly