Php-mysql support + Radius database

fredfred5

Hello,

Following on from this topic: http://forum.pfsense.org/index.php/topic,39339.0.html

I have taken the advice and have managed to set up a freeRADIUS server on an Ubuntu machine, which pfsense now authenticates against.

The freeRADIUS uses a mySQL database, into which I need to add new users. I wrote a PHP script (which runs from pfsense) to test connectivity and to write basic information to the database. However when I run the script I get this error message:

Fatal error: Call to undefined function mysqli_connect() in /var/db/cpelements/captiveportal-reg.php on line 6

This is the script

DEFINE('DB_USER', 'root');
DEFINE('DB_PASSWORD', 'password');
DEFINE('DB_HOST', '192.168.1.253');
DEFINE('DB_NAME', 'radius');
	$link = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

/* check connection */
if (!$link) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

printf("Host information: %s\n", mysqli_get_host_info($link));

/* close connection */
mysqli_close($link);

?>

For reference the versions we are using are PHP 5.2.17 on the pfsense machine and MySQL Server 5.1.5 -1ubuntu1 on the Ubuntu machine.

I looked at this topic: http://forum.pfsense.org/index.php/topic,42674.0.html

and tried to enable the mySQL extension in the /etc/rc.php_ini_setup file by following the instructions but I still get the same error.

Then I looked at this topic: http://forum.pfsense.org/index.php/topic,41825.msg217034.html#msg217034

It seems MySQL support is not enabled by default and I need to
@jimp:

compile php52-mysql, which can be done using a pfSense builder setup (check the doc wiki), and then copy the files over to the firewall.

I've checked the document wiki and can find no information on this at all. Can anyone help me with this or point me in the right direction?

Any help is greatly appreciated!

Thanks in advance.

marcelloc

try this:

http://forum.pfsense.org/index.php/topic,42031.msg217061.html#msg217061

fredfred5

Thanks. I did that on the shell, I assume that was where I was supposed to do it.

However, it still doesn't work. I'm now using an even simpler script:


mysql_connect("RADIUS","root","password") or die(mysql_error());
echo "Connected to MySQL
";
mysql_select_db("radius") or die(mysql_error());
echo "Connected to database";

and I get the error:

Warning: mysql_connect(); Unknown MySQL server host 'Radius" (2) in /var/db/cpelements/captiveportal-connecTtodb.php on line 2 Unknown MySQL server host 'RADIUS' (2)

Whereas if I change the hostname "RADIUS" to an IP address I get:

Warning: mysql_connect(); Lost connection to MySQL server at 'reading initial communication packet', system error: 61 in /var/db/cpelements/captiveportal-connecTtodb.php on line 2 Lost connection to MySQL server at 'reading initial communication packet', system error: 61

Also I have tried putting localhost there and I get:

Warning: mysql_connect(); Can't connect to local MySQL server through socket /tmp/mysql.sock

I've also opened the 3306 port on the machine hosting the database, thinking this might be what is stopping it.

Is there a way to verify that MySQL is working properly on the firewall?
Am I missing something with the script?
Do I need to change something on the DB host machine?

marcelloc

The erros says that mysql could not resolve RADIUS ip. try to configure a full fqdn hostname.

wallabybob

Does the SQL server need to be configured to allow access from the firewall?

fredfred5

@wallabybob:

Does the SQL server need to be configured to allow access from the firewall?

That was it. I needed to type this command when logged in to the MySQL server:


GRANT ALL ON radius.* TO pfsense@'pfsenseIPADDRESS' IDENTIFIED BY "PASSWORD";

Thanks for the help!

Also the test connection script need to be:

mysql_connect("192.168.1.100","pfsense","PASSWORD") or die(mysql_error());
echo "Connected to MySQL
";
mysql_select_db("radius") or die(mysql_error());
echo "Connected to database";

It couldn't find the hostname but works with the IP address instead.