[Help] Captive Portal authenticate user from external database MySQL

[overkillcf]

Hi all.
Recent, I have setup a pfsense machine. After, i enable Captive Portal service and use local user in pfsense machine.
Now, i want store user list in another machine (Windows XP run MySQL) to easy manage and backup user. Pfsense machine will connect to Windows XP machine to authenticate user.
But i don't know to config that. Please help me to config as above. Thank verymuch!
Sorry i'm bad English. :D

pfsense machine:
WAN: 192.168.10.1
LAN: 10.0.0.1

windows xp machine:
LAN: 10.0.0.2

[communig8]

I realise this will be an over simplification but….
"Just" install a freeRADIUS server and MySQL and then point the Captive Portal config at that RADIUS server.

[overkillcf]

@communig8:

I realise this will be an over simplification but….
"Just" install a freeRADIUS server and MySQL and then point the Captive Portal config at that RADIUS server.

You can help me about steps to install them. Please!

[communig8]

Your best source of information is - http://freeradius.org/

[overkillcf]

@communig8:

Your best source of information is - http://freeradius.org/

but i don't know to config them, please help me!

[communig8]

The freeRADIUS site is really the best source of documentation.

[unixaccent]

hi communig8!

I am now following your advise to overkillcf. I am now reading the documentation in freeradius.org. My simple question is, I am interested of setting up this in a hotel, would it be possible to provide a balance inquiry page where the user/client will type his username and password and he will be able to see his sessions, time consumed and time remaining in his account? thank you in advance.

[communig8]

Hi.
Yes but you will have a bit of work to do.
freeRADIUS can be setup to use MySQL as the backend datastore but does not really have much in the way of querying and showing this data.
The way I do this is to use a Joomla! web site with Fabrik installed. This allows me to easily construct web pages that show accounting data from
the freeRADIUS MySQL tables.

[unixaccent]

Thank you for the quick reply communig8!

I know a little about joomla like how it works as a cms. I hope I am not asking too much of your experience but how did you do it? Did you install first joomla and then from joomla, you added the database structure for the radius?

[communig8]

Install Joomla!
Add the Fabrik component (from www.fabrikar.com) and the Farbrik Plug-in (allows data to be inserted in articles).
In Fabrik create a database connection to the database holding the freeRADIUS data.
In Fabrik create tables (called lists in Fabrik) for the freeRADIUS tables you want to use.
Fabrik then creates a representation of all the columns.
Create additional views for querying the data as required.
You can then create menu items that goto pages that Fabrik will generate to allow you to work with the data.
You can also insert data in Articles using the Fabrik Plug-in.

[unixaccent]

Good day communig8!

Do you mean that you never used custom php scripts in your setup? I need an insight on how to put a balance inquiry page in joomla website using fabrik module. Is it possible to show all the sessions(browsing history), time remaining, and other AAA stuffs without diving into php custom scripts?

Thanks.

[communig8]

It will all depend how complicated your requirements are.
In general you can either create views on the MySQL tables and use the view in Fabrik
or you can use the filtering/joining facilities in Fabrik to get your data.
In the implementations I have worked on, I have only had to use any scripts to help
provision new RADIUS accounts but even they are driven by Fabrik.

[unixaccent]

When you said "I have only had to use any scripts to help provision new RADIUS accounts but even they are driven by Fabrik", do you mean that you created your own script? thanks!

[communig8]

Yes

[unixaccent]

Copy that sir.. Now that you have shared you ideas, I might consider learning PHP language and create my own queries carried by the php scripts for AAA display of information especially the balance inquiry that I require. There are lots of database (mysql) structure around this forum that I've been seeing but would you care to share or point to us the database structure you used for your setup that was tested and running?

[communig8]

You need to be looking at the freeRADIUS documentation not pfSense documentation for the MySQL tables used by freeRADIUS.