help with changing to use named instance of sql module
Greetings FR-users, Running freeradius 3.0.21+dfsg-2.2+deb11u1. The default FR configs (obviously!) work wonderfully. Thank you for making a robust system. I am attempting to deviate from those defaults and am hitting a snag. I would like to use multiple databases (within the sql module), thus I am attempting to change: sql { } to sql foo { } After successfully adding "foo", I can then add "bar".
From running -X it appears that I am connecting to "foo" correctly:
(0) foo: EXPAND %{User-Name} (0) foo: --> mzagrabe (0) foo: SQL-User-Name set to 'mzagrabe' rlm_sql (foo): Reserved connection (0) (0) foo: EXPAND SELECT id, UserName, Attribute, Value, Op FROM radius_check WHERE Username = '%{SQL-User-Name}' ORDER BY id (0) foo: --> SELECT id, UserName, Attribute, Value, Op FROM radius_check WHERE Username = 'mzagrabe' ORDER BY id (0) foo: Executing select query: SELECT id, UserName, Attribute, Value, Op FROM radius_check WHERE Username = 'mzagrabe' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 1 , fields = 5 (0) foo: User found in radcheck table (0) foo: Conditional check items matched, merging assignment check items (0) foo: Crypt-Password := "REDACTED" (0) foo: EXPAND SELECT id, UserName, Attribute, Value, Op FROM radius_reply WHERE Username = '%{SQL-User-Name}' ORDER BY id (0) foo: --> SELECT id, UserName, Attribute, Value, Op FROM radius_reply WHERE Username = 'mzagrabe' ORDER BY id (0) foo: Executing select query: SELECT id, UserName, Attribute, Value, Op FROM radius_reply WHERE Username = 'mzagrabe' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 0 , fields = 5 rlm_sql (foo): Reserved connection (1) rlm_sql (foo): Released connection (1) Need 6 more connections to reach 10 spares rlm_sql (foo): Opening additional connection (5), 1 of 27 pending slots used rlm_sql_postgresql: Connecting using parameters: dbname='foo' host='foo-dev.example.com' user='radius_user' password='REDACTED' application_name='FreeRADIUS 3.0.21 - radiusd (foo)' Connected to database 'foo' on 'foo-dev.example.com' server version 130011, protocol version 3, backend PID 1378924 (0) foo: EXPAND SELECT GroupName FROM radius_user_group WHERE UserName='%{SQL-User-Name}' ORDER BY priority (0) foo: --> SELECT GroupName FROM radius_user_group WHERE UserName='mzagrabe' ORDER BY priority (0) foo: Executing select query: SELECT GroupName FROM radius_user_group WHERE UserName='mzagrabe' ORDER BY priority rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 2 , fields = 1 (0) foo: User found in the group table (0) foo: EXPAND SELECT id, GroupName, Attribute, Value, op FROM radius_group_check WHERE GroupName = '%{foo-SQL-Group}' ORDER BY id (0) foo: --> SELECT id, GroupName, Attribute, Value, op FROM radius_group_check WHERE GroupName = 'network-admin' ORDER BY id (0) foo: Executing select query: SELECT id, GroupName, Attribute, Value, op FROM radius_group_check WHERE GroupName = 'network-admin' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 0 , fields = 5 (0) foo: Group "network-admin": Conditional check items matched (0) foo: Group "network-admin": Merging assignment check items (0) foo: EXPAND SELECT id, GroupName, Attribute, Value, op FROM radius_group_reply WHERE GroupName = '%{foo-SQL-Group}' ORDER BY id (0) foo: --> SELECT id, GroupName, Attribute, Value, op FROM radius_group_reply WHERE GroupName = 'network-admin' ORDER BY id (0) foo: Executing select query: SELECT id, GroupName, Attribute, Value, op FROM radius_group_reply WHERE GroupName = 'network-admin' ORDER BY id rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 0 , fields = 5 (0) foo: Group "network-admin": Merging reply items rlm_sql (foo): Released connection (0) (0) [foo] = ok (0) [expiration] = noop (0) [logintime] = noop (0) [pap] = updated (0) if ("%{client:group}" == 'two-factor-authentication-group') { (0) EXPAND %{client:group} (0) --> network-infrastructure (0) if ("%{client:group}" == 'two-factor-authentication-group') -> FALSE (0) else { (0) update control { (0) Proxy-To-Realm := 'default-authentication-realm' (0) } # update control = noop (0) } # else = noop (0) } # authorize = updated (0) Starting proxy to home server 10.0.0.1 port 1812 (0) server default { (0) } The above -X output is generally the same output as the working default sql configuration output. However, the default working sql configuration does not match the following -X output when I change the configs to use "sql foo {". I am not seeing correct behavior in the post-auth section of sites-enabled/default: [...] (0) Auth-Type = Accept, accepting the user (0) # Executing section post-auth from file /etc/freeradius/3.0/sites-enabled/default (0) post-auth { (0) if ("%{client:group}" == 'network-infrastructure') { (0) EXPAND %{client:group} (0) --> network-infrastructure (0) if ("%{client:group}" == 'network-infrastructure') -> TRUE (0) if ("%{client:group}" == 'network-infrastructure') { (0) if (foo:SQL-Group == 'network-admin') { (0) if (foo:SQL-Group == 'network-admin') -> FALSE (0) else { I am expecting the last FALSE to be TRUE, as in the -X output with the default sql configuration: (0) if (SQL-Group == 'network-admin') { (0) sql_groupcmp (0) EXPAND %{User-Name} (0) --> mzagrabe (0) SQL-User-Name set to 'mzagrabe' rlm_sql (sql): Reserved connection (5) rlm_sql (sql): Reserved connection (6) rlm_sql (sql): Released connection (6) (0) EXPAND SELECT GroupName FROM radius_user_group WHERE UserName='%{SQL-User-Name}' ORDER BY priority (0) --> SELECT GroupName FROM radius_user_group WHERE UserName='mzagrabe' ORDER BY priority (0) Executing select query: SELECT GroupName FROM radius_user_group WHERE UserName='mzagrabe' ORDER BY priority rlm_sql_postgresql: Status: PGRES_TUPLES_OK rlm_sql_postgresql: query affected rows = 2 , fields = 1 (0) sql_groupcmp finished: User is a member of group network-admin rlm_sql (sql): Released connection (5) (0) if (SQL-Group == 'network-admin') -> TRUE Here is what I have added to the top of my post-auth section: post-auth { if ("%{client:group}" == 'network-infrastructure') { if (foo:SQL-Group == 'network-admin') { update reply { APC-Service-Type = Admin # Set user type for Eaton UPS's Service-Type = Administrative-User } } else { reject } } I have also tried changing (in the same post-auth section) the -sql to -foo but it was not successful. Any hints or ideas about where to look next would be very appreciated. Thank you for your time! -m
On Jul 31, 2023, at 11:34 AM, Matt Zagrabelny via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
Running freeradius 3.0.21+dfsg-2.2+deb11u1.
Updated packages are available on http://packages.networkradius.com
The default FR configs (obviously!) work wonderfully. Thank you for making a robust system.
It's taken a while. :)
I am attempting to deviate from those defaults and am hitting a snag. I would like to use multiple databases (within the sql module), thus I am attempting to change:
sql { }
to
sql foo { }
That's OK, but there may be other references to the "sql" module in expansions, etc. Making the above change means that all of those references will stop working. You will know by testing.
After successfully adding "foo", I can then add "bar".
From running -X it appears that I am connecting to "foo" correctly: ... The above -X output is generally the same output as the working default sql configuration output.
That's good.
However, the default working sql configuration does not match the following -X output when I change the configs to use "sql foo {". I am not seeing correct behavior in the post-auth section of sites-enabled/default:
You changed what to what?
[...] (0) Auth-Type = Accept, accepting the user (0) # Executing section post-auth from file /etc/freeradius/3.0/sites-enabled/default (0) post-auth { (0) if ("%{client:group}" == 'network-infrastructure') { (0) EXPAND %{client:group} (0) --> network-infrastructure (0) if ("%{client:group}" == 'network-infrastructure') -> TRUE (0) if ("%{client:group}" == 'network-infrastructure') { (0) if (foo:SQL-Group == 'network-admin') {
What's that? You have "foo:SQL-Group"? Where did that come from? That will never work. You'll need to use "foo-SQL-Group" See mods-available/sql, which documents this. Alan DeKok.
On Mon, Jul 31, 2023 at 10:55 AM Alan DeKok <aland@deployingradius.com> wrote:
On Jul 31, 2023, at 11:34 AM, Matt Zagrabelny via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
However, the default working sql configuration does not match the following -X output when I change the configs to use "sql foo {". I am not seeing correct behavior in the post-auth section of sites-enabled/default:
You changed what to what?
Inserted "foo" into 'sql {' within mods-available/sql. Changed group attribute to: group_attribute = "${.:instance}-SQL-Group" and changed '-sql' to '-foo' in sites/available/default. And of course, what you already found below, added the 'foo:' <--- WRONG!, to the post-auth unlang.
[...] (0) Auth-Type = Accept, accepting the user (0) # Executing section post-auth from file /etc/freeradius/3.0/sites-enabled/default (0) post-auth { (0) if ("%{client:group}" == 'network-infrastructure') { (0) EXPAND %{client:group} (0) --> network-infrastructure (0) if ("%{client:group}" == 'network-infrastructure') -> TRUE (0) if ("%{client:group}" == 'network-infrastructure') { (0) if (foo:SQL-Group == 'network-admin') {
What's that? You have "foo:SQL-Group"? Where did that come from? That will never work.
Ha! Thanks for the quick eyes, Alan! Indeed, I copied over some other FR 3.x configs (which used multiple sql instances) to this new system and curiously transposed the hyphen to a colon.
You'll need to use "foo-SQL-Group" See mods-available/sql, which documents this.
Of course. The docs are great and I have benefited from your efforts. As always, a pleasure to reach out to FR-users. Thanks for the speedy assistance! -m
participants (2)
-
Alan DeKok -
Matt Zagrabelny