Defining multiple post-auth INSERT queries for the same table
So, here is the scenario: We've been asked to provide CUI-style backing for the Moonshot-*-TargetedId functionality in FreeRADIUS. Since this originates from us, I thought it might be more useful to simply use *one* column for the pre-requisite GSS-Acceptor-* and the Trust-Router-COI attributes (i.e. mix and mingle). But now, for the life of me, I can't remember for the life of me how to define multiple queries in one sql instance. Do I simply name them (i.e. name_of_query = 'query here...') and then just invoke them in their appropriate section? Or, since many here use databases, would you prefer using a table that includes a column for the appropriate attribute (even if it's not used for the retrieval)? That seems like an awful waste of space and data to me... :-/ Thoughts (on the approach) and the aide memoire are very much appreciated. :-) With Regards Stefan Paetow Moonshot Industry & Research Liaison Coordinator t: +44 (0)1235 822 125 gpg: 0x3FCE5142 xmpp: stefanp@jabber.dev.ja.net skype: stefan.paetow.janet jisc.ac.uk Jisc is a registered charity (number 1149740) and a company limited by guarantee which is registered in England under Company No. 5747339, VAT No. GB 197 0632 86. Jisc’s registered office is: One Castlepark, Tower Hill, Bristol, BS2 0JA. T 0203 697 5800.
On May 16, 2017, at 12:04 PM, Stefan Paetow <Stefan.Paetow@jisc.ac.uk> wrote:
We've been asked to provide CUI-style backing for the Moonshot-*-TargetedId functionality in FreeRADIUS. Since this originates from us, I thought it might be more useful to simply use *one* column for the pre-requisite GSS-Acceptor-* and the Trust-Router-COI attributes (i.e. mix and mingle). But now, for the life of me, I can't remember for the life of me how to define multiple queries in one sql instance. Do I simply name them (i.e. name_of_query = 'query here...') and then just invoke them in their appropriate section?
It depends on what you want to do. What's the schema and use-case? The current code does one query for authentication, and multiple possible insert / updates for accounting.
Or, since many here use databases, would you prefer using a table that includes a column for the appropriate attribute (even if it's not used for the retrieval)? That seems like an awful waste of space and data to me... :-/
I'm not sure what these things are used for, so I don't know. Alan DeKok.
Ok,
instance. Do I simply name them (i.e. name_of_query = 'query here...') and then just invoke them in their appropriate section?
It depends on what you want to do. What's the schema and use-case?
Schema is something like this: [gss_acceptor] [namespace] [username] [targeted_id] The *-TargetedId entries depend on 3 attributes: GSS-Acceptor-Host-Name (gives us Moonshot-Host-TargetedId), GSS-Acceptor-Realm-Name (gives us Moonshot-Realm-TargetedId) and Trust-Router-COI (gives us Moonshot-TR-COI-TargetedId). Given the policy unlang to generate a targeted Id is exactly the same for all three versions, except for the namespace and the GSS attribute, either I define custom attributes in the UKERNA dictionary that I set the gas_acceptor, namespace and targeted_id to for each generation, or I change the schema to include all three like this: [gss_acceptor_host_name] [gss_acceptor_realm_name] [trust_router_coi] [namespace] [username] [host_targeted_id] [realm_targeted_id] [tr_coi_targeted_id] This just seems a waste of space. What do you think is better? The first option? It seems more concise. With Regards Stefan Paetow Moonshot Industry & Research Liaison Coordinator t: +44 (0)1235 822 125 gpg: 0x3FCE5142 xmpp: stefanp@jabber.dev.ja.net skype: stefan.paetow.janet jisc.ac.uk Jisc is a registered charity (number 1149740) and a company limited by guarantee which is registered in England under Company No. 5747339, VAT No. GB 197 0632 86. Jisc¹s registered office is: One Castlepark, Tower Hill, Bristol, BS2 0JA. T 0203 697 5800.
On May 16, 2017, at 5:48 PM, Stefan Paetow <Stefan.Paetow@JISC.AC.UK> wrote:
Ok,
instance. Do I simply name them (i.e. name_of_query = 'query here...') and then just invoke them in their appropriate section?
It depends on what you want to do. What's the schema and use-case?
Schema is something like this:
[gss_acceptor] [namespace] [username] [targeted_id]
The *-TargetedId entries depend on 3 attributes: GSS-Acceptor-Host-Name (gives us Moonshot-Host-TargetedId), GSS-Acceptor-Realm-Name (gives us Moonshot-Realm-TargetedId) and Trust-Router-COI (gives us Moonshot-TR-COI-TargetedId).
Then you're probably better off just writing a custom SQL inner in the post-auth stage...
Given the policy unlang to generate a targeted Id is exactly the same for all three versions, except for the namespace and the GSS attribute, either I define custom attributes in the UKERNA dictionary that I set the gas_acceptor, namespace and targeted_id to for each generation, or I change the schema to include all three like this:
[gss_acceptor_host_name] [gss_acceptor_realm_name] [trust_router_coi] [namespace] [username] [host_targeted_id] [realm_targeted_id] [tr_coi_targeted_id]
This just seems a waste of space.
What do you think is better? The first option? It seems more concise.
I agree. Alan DeKok.
Then you're probably better off just writing a custom SQL inner in the post-auth stage...
What do you think is better? The first option? It seems more concise.
I agree.
Alrighty! *goes off to hammer something nice and elegant out* I hope I can shift this into 3.0.14 as soon as possible! ;-) Stefan Paetow Moonshot Industry & Research Liaison Coordinator t: +44 (0)1235 822 125 gpg: 0x3FCE5142 xmpp: stefanp@jabber.dev.ja.net skype: stefan.paetow.janet jisc.ac.uk Jisc is a registered charity (number 1149740) and a company limited by guarantee which is registered in England under Company No. 5747339, VAT No. GB 197 0632 86. Jisc’s registered office is: One Castlepark, Tower Hill, Bristol, BS2 0JA. T 0203 697 5800.
Hi Alan et al,
[gss_acceptor] [namespace] [username] [targeted_id]
The *-TargetedId entries depend on 3 attributes: GSS-Acceptor-Host-Name (gives us Moonshot-Host-TargetedId), GSS-Acceptor-Realm-Name (gives us Moonshot-Realm-TargetedId) and Trust-Router-COI (gives us Moonshot-TR-COI-TargetedId).
Then you're probably better off just writing a custom SQL inner in the post-auth stage...
Following on from this... How do I define multiple post-auth queries (to insert stuff into the database) in a sql module? Looks like I can only have one per instance (like the cui module), or I have to define a type? But if I define a type, how do I invoke it? In my queries.conf I have something like this (granted, this is sort-of guess work since there's nothing described in the Wiki how a queries.conf should look like other than looking at the other examples): post-auth { query1 { query = "\ INSERT INTO `${...table_name}` \ (column1, column2, column3, column4) VALUES \ ("%{Attribute1}", "%{Attribute2}", "%{Attribute3}", "%{Attribute4}")" } query2 { query = "\ INSERT INTO `${...table_name}` \ (column1, column2, column3, column4) VALUES \ ("%{Attribute5}", "%{Attribute6}", "%{Attribute7}", "%{Attribute8}")" } } The queries.conf is pulled in with a standard $INCLUDE as per the CUI module, and it loads, but when I try to invoke any of these queries, I get a nice red message when loading in debug mode: /etc/raddb/policy.d/moonshot-targeted-ids[70]: Failed to find "moonshot_tid_sql.post-auth.query1" as a module or policy. /etc/raddb/policy.d/moonshot-targeted-ids[70]: Please verify that the configuration exists in /etc/raddb/mods-enabled/moonshot_tid_sql.post-auth.query1. /etc/raddb/policy.d/moonshot-targeted-ids[70]: Failed to parse "moonshot_tid_sql.post-auth.query1" entry. /etc/raddb/policy.d/moonshot-targeted-ids[68]: Failed to parse "else" subsection. /etc/raddb/policy.d/moonshot-targeted-ids[55]: Failed to parse "if" subsection. /etc/raddb/policy.d/moonshot-targeted-ids[54]: Failed to parse "if" subsection. /etc/raddb/policy.d/moonshot-targeted-ids[42]: Failed to parse "if" subsection. Changing it to moonshot_tid_sql.query1 doesn't make a difference either. Of course I could invoke an INSERT INTO with a sqlxlat, but... That's messy, isn't it? I am missing *something*, I just don't quite get what. Stefan Paetow Moonshot Industry & Research Liaison Coordinator t: +44 (0)1235 822 125 gpg: 0x3FCE5142 xmpp: stefanp@jabber.dev.ja.net skype: stefan.paetow.janet jisc.ac.uk Jisc is a registered charity (number 1149740) and a company limited by guarantee which is registered in England under Company No. 5747339, VAT No. GB 197 0632 86. Jisc¹s registered office is: One Castlepark, Tower Hill, Bristol, BS2 0JA. T 0203 697 5800.
On 18/05/17 01:00, Stefan Paetow wrote:
Following on from this... How do I define multiple post-auth queries (to
AFAICT you can't do this. The code doesn't seem to support it, unless I'm mistaken - the postauth handler uses the acct_redundant() function in rlm_sql.c but that function seems to return if a query succeeded and touched >0 rows, unless RLM_SQL_ALT_QUERY (constraint violation) is returned. It seems to be mainly designed for primary/fallback or failover? FWIW we use SQL triggers for stuff like this, but I'm moving away from using the SQL stuff entirely for acct/postauth - we've bumped up against the limits of unlang and compiled-in module logic (and I'm abandoning C as a lost cause ;o)
AFAICT you can't do this. The code doesn't seem to support it, unless I'm mistaken - the postauth handler uses the acct_redundant() function in rlm_sql.c but that function seems to return if a query succeeded and touched >0 rows, unless RLM_SQL_ALT_QUERY (constraint violation) is returned.
Thanks for the clarification, Phil. :-)
FWIW we use SQL triggers for stuff like this, but I'm moving away from using the SQL stuff entirely for acct/postauth - we've bumped up against the limits of unlang and compiled-in module logic (and I'm abandoning C as a lost cause ;o)
Interesting. This thing is just a small... how do I put it... 'backup' for those who don't want to rely on automatic generation. This is just version 0.1. ;-) Stefan Paetow Moonshot Industry & Research Liaison Coordinator t: +44 (0)1235 822 125 gpg: 0x3FCE5142 xmpp: stefanp@jabber.dev.ja.net skype: stefan.paetow.janet jisc.ac.uk Jisc is a registered charity (number 1149740) and a company limited by guarantee which is registered in England under Company No. 5747339, VAT No. GB 197 0632 86. Jisc’s registered office is: One Castlepark, Tower Hill, Bristol, BS2 0JA. T 0203 697 5800.
On 18/05/17 13:19, Stefan Paetow wrote:
AFAICT you can't do this. The code doesn't seem to support it, unless I'm mistaken - the postauth handler uses the acct_redundant() function in rlm_sql.c but that function seems to return if a query succeeded and touched >0 rows, unless RLM_SQL_ALT_QUERY (constraint violation) is returned.
Thanks for the clarification, Phil. :-)
Well, it's a sort of educated guess/assumption.
participants (3)
-
Alan DeKok -
Phil Mayers -
Stefan Paetow