Troubleshooting PostgreSQL Integration

[Phil Mayers]

On 08/26/2013 12:10 AM, mdecheser at comcast.net wrote:
> Dear Users --
>
> This is my first posting to the FreeRADIUS users list, so please be patient :)

You're already doing pretty well - you actually posted a full debug, 
which hardly anyone does first time!


Ok, so for the SQL case the server startup shows:

> 	authorize_check_query = ""
> 	authorize_group_check_query = ""
> 	authorize_group_reply_query = ""
> 	

...which then causes this:

> rlm_sql (sql): Reserving sql socket id: 4
> [sql] 	expand:  ->
> [sql] Error generating query; rejecting user
> rlm_sql (sql): Released sql socket id: 4
> ++[sql] returns fail

i.e. because "authorize_check_query" is blank, the SQL module fails.

>
> I can see from the debug output that it's complaining about the user being invalid.

Sort of; that's the server core talking, based in the SQL module 
returning. The actual error is a few lines higher up (see above).


> authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \
>    FROM ${authcheck_table} \
>    WHERE Username = '%{SQL-User-Name}' \
>    ORDER BY id"
>
> [info on case & ordering of fields]

This isn't actually significant in SQL; it's case-insensitive to 
table/column names unless you double-quote them, and ordering is as-per 
the query, not table def. But it's not the cause of your issue.

> What else is left to provide as far as details?

Nothing, you've been very comprehensive. The actual problem seems to be 
that sql/postgres/dialup.conf isn't being read. The 2.x series 
"sql.conf" basically looks like this:

sql {
   database = "blah"
   driver = "rlm_sql_${blah}
   ...
   $INCLUDE sql/${database}/dialup.conf
}

Check that this INCLUDE hasn't vanished or gotten lost; if you look 
right at the top of the server startup, it reads all the config, 
including all "includ"ing lines, and you can see it loads sql.conf, then 
policy.conf - there's no message saying it's "including dialup.conf".

So, in short, the SQL queries aren't being read from your config file - 
check the "raddb/sql.conf" file, and compare it to the original, or the 
one for reference here:

https://github.com/FreeRADIUS/freeradius-server/blob/release_2_1_12/raddb/sql.conf

Finally, and not related to your issue - consider upgrading to 2.2.0 as 
2.1.12 has security issues and bugs which are fixed.