On 8 Jul 2014, at 14:57, Axel Luttgens <axel.luttgens@skynet.be> wrote:
Hello,
I'm testing FR with the slite backend, with the configs provided by:
raddb/mods-config/sql/main/sqlite/schema.sql raddb/mods-config/sql/main/sqlite/queries.conf
And I have some questions. So, if I may ask...
1. Almost all tables have an id column, conveniently named "id" and defined as:
id int(11) PRIMARY KEY
Is there any reason for not having defined those columns as
id INTEGER PRIMARY KEY AUTOINCREMENT instead?
Nope.
2. Incidentally, table "radusergroup" doesn't have such a column. An omission? Or could it prove problematic to define such a column?
It should probably have one as well.
3. The post-auth query is defined as:
query = "\ INSERT INTO ${..postauth_table} \ (username, pass, reply, authdate) \ VALUES ( \ '%{SQL-User-Name}', \ '%{%{User-Password}:-%{Chap-Password}}', \ '%{reply:Packet-Type}', \ %{%{integer:Event-Timestamp}:-date('now')})"
and the log shows it expands as:
INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'bob', 'hello', 'Access-Accept', )
As a result, no row is inserted into table "radpostauth", since its column "authdate" is defined to be NOT NULL.
Could you provide the debug output please.
How is the expression %{%{integer:Event-Timestamp}:-date('now')} supposed to be interpreted? More specifically, why the enclosing %{...}?
It's interpreted by the xlat parser. As integer:Event-Timestamp expands to a zero length string, data('now') should be inserted into the query string instead... I guess that's a bug.
4. Replacing above expression by '%S' works beautifully. I mean, the expansion for the query yields a timestamp such as "'2014-07-08 18:09:54'", and a row is now happily inserted into table "radpostauth". :-) What is the rationale for having devised a query based on Event-Timestamp at first hand?
It's adjusted for delay, whereas %S isn't. If you're using the detail/reader writer combo using %S will produce invalid results. If you're doing accounting Acct-Delay-Time will be taken into account when producing the value for Event-Timestamp also.
5. BTW, file http://networkradius.com/doc/FreeRADIUS-Implementation-Ch16.pdf describes %S as returning a timestamp with format "YYYY-mmm-ddd HH:MM:SS". Wouldn't something like "YYYY-MM-DD HH:MM:SS" be more accurate?
Probably.
6. I've left the default setting
radius_db = "radius"
in raddb/mods-available/sql, even if my db file is named "radiusdb". Everything seems to be working fine. What is the role of variable "radius_db" in an sqlite context?
None. The database file is set with sql { sqlite { filename = "/path/to/file" } }
7. Going back to question 3, this raises the usual question about sqlite in such cases. Would there be a way to get an explicit error message written to syslog?
Use linelog to log Module-Failure-Message attribute values. -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS Development Team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2