Defining multiple post-auth INSERT queries for the same table

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Defining multiple post-auth INSERT queries for the same table

Stefan Paetow-3
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: [hidden email]
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.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

signature.asc (507 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Defining multiple post-auth INSERT queries for the same table

Alan DeKok-2
On May 16, 2017, at 12:04 PM, Stefan Paetow <[hidden email]> 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.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|

Re: Defining multiple post-auth INSERT queries for the same table

Stefan Paetow-3
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: [hidden email]
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.


>


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|

Re: Defining multiple post-auth INSERT queries for the same table

Alan DeKok-2
On May 16, 2017, at 5:48 PM, Stefan Paetow <[hidden email]> 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.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|

Re: Defining multiple post-auth INSERT queries for the same table

Stefan Paetow-3
>
>  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: [hidden email]
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.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

signature.asc (507 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Defining multiple post-auth INSERT queries for the same table

Stefan Paetow-3
In reply to this post by Alan DeKok-2
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: [hidden email]
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.




-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|

Re: Defining multiple post-auth INSERT queries for the same table

Phil Mayers
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)
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Reply | Threaded
Open this post in threaded view
|

Re: Defining multiple post-auth INSERT queries for the same table

Stefan Paetow-3
> 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: [hidden email]
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.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

signature.asc (507 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Defining multiple post-auth INSERT queries for the same table

Phil Mayers
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.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html