How to Install FreeRADIUS with PostgreSQL

Published on October 18, 2024•Updated on October 18, 2024

Introduction

FreeRADIUS is a popular open-source RADIUS (Remote Authentication Dial-In User Service) application for managing network access through user authentication, authorization, and accounting services (AAA). When you install FreeRADIUS, it integrates with database engines such as PostgreSQL for centralized user management.

This article explains how to install and configure FreeRadius with PostgreSQL to create a reliable RADIUS solution.

Prerequisites

Before you begin:

Deploy an Ubuntu 24.04 instance on Vultr and enable limited-user login.
Access the instance using SSH.
Install PostgreSQL on the instance.

Install FreeRADIUS

FreeRADIUS is available in the default package repositories on Ubuntu. Follow the steps below to install FreeRADIUS and the dependency plugin for PostgreSQL on your server.

Install FreeRADIUS and the PostgreSQL plugin.

                            console
                            
$ sudo apt-get install freeradius freeradius-postgresql

Check the FreeRADIUS version.

console

$ freeradius -v

Output:

radiusd: FreeRADIUS Version 3.2.3, for host x86_64-pc-linux-gnu, built on Mar 31 2024 at 05:22:45
FreeRADIUS Version 3.2.3
Copyright (C) 1999-2022 The FreeRADIUS server project and contributors
There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
PARTICULAR PURPOSE
You may redistribute copies of FreeRADIUS under the terms of the
GNU General Public License
For more information about these matters, see the file named COPYRIGHT

Configure PostgreSQL Database Schema for FreeRADIUS

FreeRADIUS requires a specific schema to work with database servers such as PostgreSQL. Follow the steps below to create a new schema file that contains the default tables and fields to use with PostgreSQL.

Create a new freeradius_schema.sql schema file in a system-wide location such as /opt.
console
```
$ sudo touch /etc/freeradius_schema.sql
```
Edit the file using a text editor such as nano.
console
```
$ sudo nano /etc/freeradius_schema.sql
```

Add the following database schema to the file.

                            sql
                            
                        
/*
*
* PostgreSQL schema for FreeRADIUS
*
*/

/*
* Table structure for table 'radacct'
*
*/
CREATE TABLE IF NOT EXISTS radacct (
       RadAcctId               bigserial PRIMARY KEY,
       AcctSessionId           text NOT NULL,
       AcctUniqueId            text NOT NULL UNIQUE,
       UserName                text,
       Realm                   text,
       NASIPAddress            inet NOT NULL,
       NASPortId               text,
       NASPortType             text,
       AcctStartTime           timestamp with time zone,
       AcctUpdateTime          timestamp with time zone,
       AcctStopTime            timestamp with time zone,
       AcctInterval            bigint,
       AcctSessionTime         bigint,
       AcctAuthentic           text,
       ConnectInfo_start       text,
       ConnectInfo_stop        text,
       AcctInputOctets         bigint,
       AcctOutputOctets        bigint,
       CalledStationId         text,
       CallingStationId        text,
       AcctTerminateCause      text,
       ServiceType             text,
       FramedProtocol          text,
       FramedIPAddress         inet,
       FramedIPv6Address       inet,
       FramedIPv6Prefix        inet,
       FramedInterfaceId       text,
       DelegatedIPv6Prefix     inet,
       Class                   text
);

-- For use by update-, stop- and simul_* queries
CREATE INDEX radacct_active_session_idx ON radacct (AcctUniqueId) WHERE AcctStopTime IS NULL;

-- For use by on-off-
CREATE INDEX radacct_bulk_close ON radacct (NASIPAddress, AcctStartTime) WHERE AcctStopTime IS NULL;

-- and for common statistic queries:
CREATE INDEX radacct_start_user_idx ON radacct (AcctStartTime, UserName);

-- and for Class
CREATE INDEX radacct_calss_idx ON radacct (Class);


/*
* Table structure for table 'radcheck'
*/
CREATE TABLE IF NOT EXISTS radcheck (
       id                      serial PRIMARY KEY,
       UserName                text NOT NULL DEFAULT '',
       Attribute               text NOT NULL DEFAULT '',
       op                      VARCHAR(2) NOT NULL DEFAULT '==',
       Value                   text NOT NULL DEFAULT ''
);

create index radcheck_UserName on radcheck (UserName,Attribute);

/*
* Table structure for table 'radgroupcheck'
*/
CREATE TABLE IF NOT EXISTS radgroupcheck (
       id                      serial PRIMARY KEY,
       GroupName               text NOT NULL DEFAULT '',
       Attribute               text NOT NULL DEFAULT '',
       op                      VARCHAR(2) NOT NULL DEFAULT '==',
       Value                   text NOT NULL DEFAULT ''
);

create index radgroupcheck_GroupName on radgroupcheck (GroupName,Attribute);

/*
* Table structure for table 'radgroupreply'
*/
CREATE TABLE IF NOT EXISTS radgroupreply (
       id                      serial PRIMARY KEY,
       GroupName               text NOT NULL DEFAULT '',
       Attribute               text NOT NULL DEFAULT '',
       op                      VARCHAR(2) NOT NULL DEFAULT '=',
       Value                   text NOT NULL DEFAULT ''
);

create index radgroupreply_GroupName on radgroupreply (GroupName,Attribute);

/*
* Table structure for table 'radreply'
*/
CREATE TABLE IF NOT EXISTS radreply (
       id                      serial PRIMARY KEY,
       UserName                text NOT NULL DEFAULT '',
       Attribute               text NOT NULL DEFAULT '',
       op                      VARCHAR(2) NOT NULL DEFAULT '=',
       Value                   text NOT NULL DEFAULT ''
);

create index radreply_UserName on radreply (UserName,Attribute);

/*
* Table structure for table 'radusergroup'
*/
CREATE TABLE IF NOT EXISTS radusergroup (
       id                      serial PRIMARY KEY,
       UserName                text NOT NULL DEFAULT '',
       GroupName               text NOT NULL DEFAULT '',
       priority                integer NOT NULL DEFAULT 0
);

create index radusergroup_UserName on radusergroup (UserName);

--
-- Table structure for table 'radpostauth'
--

CREATE TABLE IF NOT EXISTS radpostauth (
       id                      bigserial PRIMARY KEY,
       username                text NOT NULL,
       pass                    text,
       reply                   text,
       CalledStationId         text,
       CallingStationId        text,
       authdate                timestamp with time zone NOT NULL default now(),
       Class                   text
);

CREATE INDEX radpostauth_username_idx ON radpostauth (username);
CREATE INDEX radpostauth_class_idx ON radpostauth (Class);

/*
* Table structure for table 'nas'
*/
CREATE TABLE IF NOT EXISTS nas (
       id                      serial PRIMARY KEY,
       nasname                 text NOT NULL,
       shortname               text NOT NULL,
       type                    text NOT NULL DEFAULT 'other',
       ports                   integer,
       secret                  text NOT NULL,
       server                  text,
       community               text,
       description             text
);

create index nas_nasname on nas (nasname);

/*
* Table structure for table 'nasreload'
*/
CREATE TABLE IF NOT EXISTS nasreload (
       NASIPAddress		inet PRIMARY KEY,
       ReloadTime		timestamp with time zone NOT NULL
);

Save and close the file.

The above schema creates the following database tables for FreeRADIUS:

radcheck: Stores user-specific authentication attributes, such as usernames and passwords.
radreply: Stores reply attributes after a successful authentication, such as session limits and IP assignments.
radgroupcheck: Stores group-specific authentication attributes.
radgroupreply: Stores group-specific reply attributes after a successful authentication.
radusergroup: Associates users with groups. For example, maps user A to the VIP Wi-Fi group.
radacct: Stores accounting records on how users interact with a network. For example, the session start time, data usage, stop-time, and other usage metrics.
radpostauth: Logs successful and failed authentication attempts including the username, timestamp, and authentication response.
nas: Stores information about the Network Access Servers (NAS) that send the authentication, authorization, and accounting requests to the FreeRADIUS server.
nasreload: Enables dynamic reloading of NAS configurations to the FreeRADIUS memory.

Access the PostgreSQL database server console as the postgres user.
console
```
$ sudo -u postgres psql
```

Modify the postgres user password.

                            sql
                            
postgres=#  ALTER USER postgres WITH ENCRYPTED PASSWORD 'radpass';

Exit the PostgreSQL console.
sql
```
postgres=# \q
```
Create a new freeradius database to use with FreeRADIUS.
console
```
$ sudo -u postgres createdb freeradius
```
Run the following command to enable password authentication on the PostgreSQL database server. Replace 17 with the actual PostgreSQL version on your server.
console
```
$ sudo sed -i '/^local/s/peer/scram-sha-256/' /etc/postgresql/17/main/pg_hba.conf
```

Import the freeradius_schema.sql file to the freeradius database.

                            console
                            
$ sudo -u postgres psql -d freeradius -f /etc/freeradius_schema.sql

Output:

CREATE TABLE
CREATE INDEX
...

Log in to the PostgreSQL database server as the postgres user and switch to the freeradius database.
console
```
$ sudo -u postgres psql -d freeradius
```

Verify that all FreeRADIUS tables from your schema are available.

                            sql
                            
freeradius=# \dt

Output:

             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | nas           | table | postgres
 public | nasreload     | table | postgres
 public | radacct       | table | postgres
 public | radcheck      | table | postgres
 public | radgroupcheck | table | postgres
 public | radgroupreply | table | postgres
 public | radpostauth   | table | postgres
 public | radreply      | table | postgres
 public | radusergroup  | table | postgres
(9 rows)

Create a new sample user in the radcheck table. For example, kiki and set the user's password to 1234.

                            sql
                            
                        
freeradius=# INSERT INTO radcheck (UserName, Attribute, op, Value)
VALUES ('kiki', 'Cleartext-Password', ':=', '1234');

Query the radcheck table to view the available records.

                            sql
                            
freeradius=# SELECT * FROM radcheck WHERE UserName = 'kiki';

Output:

id | username  | attribute           | op  | value
----+----------+--------------------+-----+------
 1 | kiki      | Cleartext-Password  | :=  | 1234

Create a new test server in the nas table.

                            sql
                            
                        
freeradius=# INSERT INTO nas (nasname, shortname, type, ports, secret, community, description)
VALUES ('127.0.0.1', 'localhost', 'other', 0, 'testing123', NULL, 'Local NAS for testing');

Query the nas table to verify the available servers.

                            sql
                            
freeradius=# SELECT * FROM nas WHERE nasname = '127.0.0.1';

Output:

 id | nasname    | shortname | type  | ports | secret     | community | description
----+------------+-----------+-------+-------+------------+-----------+-----------------------
  1 | 127.0.0.1  | localhost | other | 0     | testing123 |           | Local NAS for testing

Exit the PostgreSQL database console.
sql
```
freeradius=# \q
```

Configure FreeRADIUS to Use PostgreSQL

Follow the steps below to configure FreeRADIUS to work with the PostgreSQL database server.

Enable the FreeRADIUS SQL module by creating a symbolic link in the mods-enabled configurations directory.
console
```
$ sudo ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/
```

Open the default FreeRADIUS configuration file.

                            console
                            
$ sudo nano /etc/freeradius/3.0/sites-available/default

Find the following -sql directive and remove the - symbol to enable the SQL directive.
ini
```
#  See "Authorization Queries" in mods-available/sql
-sql
```

Open the /sites-available/inner-tunnel file.

                            console
                            
$ sudo nano /etc/freeradius/3.0/sites-available/inner-tunnel

Find the following SQL directives and remove the - or # symbols to enable each directive.

                            ini
                            
                        
#  See "Simultaneous Use Checking Queries" in `mods-config/sql/main/$driver/queries.>
#       sql

#  See "Authentication Logging Queries" in `mods-config/sql/main/$driver/queries.con>
-sql

Save and close the file.

Open the /mods-available/sql file.

                            console
                            
$ sudo nano /etc/freeradius/3.0/mods-available/sql

Find the following dialect directive and change the value from SQLite to PostgreSQL.
ini
```
dialect = "postgresql"
```
Find the driver directive and change the value from rlm_sql_null to rlm_sql_${dialect} to integrate FreeRADIUS with PostgreSQL.
ini
```
driver = "rlm_sql_${dialect}"
```
Find the database connection section, uncomment all directives, and enter your PostgreSQL database information in the respective fields.
ini
```
# Connection info:
server = "localhost"
port = 5432
login = "postgres"
password = "radpass"
```
Find the radius_db directive and change the value from radius to the FreeRADIUS database you created earlier.
ini
```
radius_db = "freeradius"
```
Find and uncomment the read_clients directive to enable FreeRADIUS to read client details from the nas table.
ini
```
read_clients = yes
```
Save and close the file.

Test the FreeRADIUS Integration with PostgreSQL

Follow the steps below to test the FreeRADIUS integration with PostgreSQL. You will authenticate with your local server using the test user you created earlier in the radcheck table to verify that FreeRADIUS successfully reads data from the database.

Stop the FreeRADIUS system service.
console
```
$ sudo service freeradius stop
```

Start FreeRADIUS in debugging mode as a background process to view detailed process information.

                            console
                            
$ sudo /usr/sbin/freeradius -X &

Output:

Listening on auth address 127.0.0.1 port 18120 bound to server inner-tunnel
Listening on auth address * port 1812 bound to server default
Listening on acct address * port 1813 bound to server default
Listening on auth address :: port 1812 bound to server default
Listening on acct address :: port 1813 bound to server default
Listening on proxy address * port 49424
Listening on proxy address :: port 46803
Ready to process requests

If you receive the following error when starting the server:

Failed binding to auth address 127.0.0.1 port 18120 bound to server inner-tunnel: Address already in 
use /etc/freeradius/sites-enabled/inner-tunnel[33]: Error binding to port for 127.0.0.1 port 18120

Run the following command to get the FreeRADIUS process ID.

                            console
                            
$ sudo ps aux | grep radius

Output:

freerad     9374  0.0  3.0  98528 30080 ?        Ssl  06:43   0:00 /usr/sbin/freeradius -f
root       23698  0.0  0.2   7076  2048 pts/0    S+   16:33   0:00 grep --color=auto radius

Stop the freerand process by specifying the ID, such as 9374 based on the above output.
console
```
$ kill-9 9374
```
Start the server again in debugging mode.
console
```
$ sudo /usr/sbin/freeradius -X
```

Run the radtest utility and try authenticating to the FreeRADIUS server as kiki and specify 1234 as the password.

                            console
                            
$ radtest kiki 1234 localhost 0 testing123

Output:

Sent Access-Request Id 36 from 0.0.0.0:a668 to 127.0.0.1:1812 length 74
 User-Name = "kiki"
 User-Password = "1234"
 NAS-IP-Address = 127.0.1.1
 NAS-Port = 0
 Message-Authenticator = 0x00
 Cleartext-Password = "1234"
Received Access-Accept Id 36 from 127.0.0.1:714 to 127.0.0.1:42600 length 20

The Received Access-Accept... response in the above output shows that you have successfully authenticated to the server.

Conclusion

You have installed and configured FreeRADIUS with PostgreSQL to enable real-time authentication on a server. You can use FreeRADIUS to perform user authentication and authorization tasks. In addition, you can also use group-based access control by utilizing the RadGroupCheck and RadGroupReply tables in PostgreSQL to store attribute checks for specific user groups. After you install FreeRADIUS, you will have more configuration options; please visit the FreeRADIUS documentation.

How to Install FreeRADIUS with PostgreSQL

Introduction

Prerequisites

Install FreeRADIUS

Configure PostgreSQL Database Schema for FreeRADIUS

Configure FreeRADIUS to Use PostgreSQL

Test the FreeRADIUS Integration with PostgreSQL

Conclusion

Products

Features

Marketplace

Resources

Company