DEV Community

Cover image for Installing FreeRADIUS with PostgreSQL
Vultr profile image Sanskriti Harmukh
Sanskriti Harmukh for Vultr

Posted on with Aashish Chaurasiya Originally published at docs.vultr.com

Installing FreeRADIUS with PostgreSQL

#postgres #linux #networking #security

FreeRADIUS is a popular open-source RADIUS (Remote Authentication Dial-In User Service) application for managing network access through user authentication, authorization, and accounting (AAA) services. It integrates with database engines such as PostgreSQL for centralized user management. This guide installs FreeRADIUS, configures it to use PostgreSQL as its backend database, loads the RADIUS schema, and authenticates a test user against the database to create a reliable RADIUS solution. By the end, you'll have FreeRADIUS running with a PostgreSQL-backed user store, verified with a live authentication test.

Prerequisites: an Ubuntu 24.04 server with a non-root sudo user, SSH access to the instance, and PostgreSQL already installed.

Install FreeRADIUS

FreeRADIUS is available in the default Ubuntu package repositories.

1. Install FreeRADIUS and the PostgreSQL plugin:

$ sudo apt-get install freeradius freeradius-postgresql
Enter fullscreen mode Exit fullscreen mode

2. Check the installed version:

$ freeradius -v
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Configure the PostgreSQL Database Schema for FreeRADIUS

FreeRADIUS requires a specific schema to work with a database server like PostgreSQL.

1. Create a new schema file:

$ sudo touch /etc/freeradius_schema.sql
Enter fullscreen mode Exit fullscreen mode

2. Edit the file:

$ sudo nano /etc/freeradius_schema.sql
Enter fullscreen mode Exit fullscreen mode

3. Add the following database schema to the file:

/*
*
* 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
);
Enter fullscreen mode Exit fullscreen mode

Save and close the file. This schema creates the following tables: radcheck (user-specific authentication attributes), radreply (reply attributes after successful authentication), radgroupcheck and radgroupreply (group-specific variants), radusergroup (maps users to groups), radacct (accounting records), radpostauth (authentication attempt logs), nas (Network Access Server records), and nasreload (dynamic NAS config reloads).

4. Access the PostgreSQL console as the postgres user:

$ sudo -u postgres psql
Enter fullscreen mode Exit fullscreen mode

5. Set a password for the postgres user:

postgres=#  ALTER USER postgres WITH ENCRYPTED PASSWORD 'radpass';
Enter fullscreen mode Exit fullscreen mode

6. Exit the PostgreSQL console:

postgres=# \q
Enter fullscreen mode Exit fullscreen mode

7. Create a new freeradius database:

$ sudo -u postgres createdb freeradius
Enter fullscreen mode Exit fullscreen mode

8. Enable password authentication on the database server (replace 17 with your actual PostgreSQL version):

$ sudo sed -i '/^local/s/peer/scram-sha-256/' /etc/postgresql/17/main/pg_hba.conf
Enter fullscreen mode Exit fullscreen mode

9. Import the schema file into the freeradius database:

$ sudo -u postgres psql -d freeradius -f /etc/freeradius_schema.sql
Enter fullscreen mode Exit fullscreen mode

Output:

CREATE TABLE
CREATE INDEX
...
Enter fullscreen mode Exit fullscreen mode

10. Log in to the freeradius database:

$ sudo -u postgres psql -d freeradius
Enter fullscreen mode Exit fullscreen mode

11. Verify all FreeRADIUS tables are available:

freeradius=# \dt
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

12. Create a sample user in the radcheck table (username kiki, password 1234):

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

13. Query the radcheck table to view the record:

freeradius=# SELECT * FROM radcheck WHERE UserName = 'kiki';
Enter fullscreen mode Exit fullscreen mode

Output:

id | username  | attribute           | op  | value
----+----------+--------------------+-----+------
 1 | kiki      | Cleartext-Password  | :=  | 1234
Enter fullscreen mode Exit fullscreen mode

14. Create a test server entry in the nas table:

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');
Enter fullscreen mode Exit fullscreen mode

15. Query the nas table to verify the entry:

freeradius=# SELECT * FROM nas WHERE nasname = '127.0.0.1';
Enter fullscreen mode Exit fullscreen mode

Output:

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

16. Exit the PostgreSQL console:

freeradius=# \q
Enter fullscreen mode Exit fullscreen mode

Configure FreeRADIUS to Use PostgreSQL

1. Enable the FreeRADIUS SQL module by symlinking it into mods-enabled:

$ sudo ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/
Enter fullscreen mode Exit fullscreen mode

2. Open the default site configuration file:

$ sudo nano /etc/freeradius/3.0/sites-available/default
Enter fullscreen mode Exit fullscreen mode

3. Find the -sql directive and remove the leading - to enable it:

#  See "Authorization Queries" in mods-available/sql
-sql
Enter fullscreen mode Exit fullscreen mode

4. Open the inner-tunnel site configuration file:

$ sudo nano /etc/freeradius/3.0/sites-available/inner-tunnel
Enter fullscreen mode Exit fullscreen mode

5. Find the following SQL directives and remove the leading - or # to enable each one:

#  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
Enter fullscreen mode Exit fullscreen mode

Save and close the file.

6. Open the sql module configuration file:

$ sudo nano /etc/freeradius/3.0/mods-available/sql
Enter fullscreen mode Exit fullscreen mode

7. Change dialect from SQLite to PostgreSQL:

dialect = "postgresql"
Enter fullscreen mode Exit fullscreen mode

8. Change driver from rlm_sql_null to rlm_sql_${dialect}:

driver = "rlm_sql_${dialect}"
Enter fullscreen mode Exit fullscreen mode

9. Uncomment the database connection section and enter your PostgreSQL connection details:

# Connection info:
server = "localhost"
port = 5432
login = "postgres"
password = "radpass"
Enter fullscreen mode Exit fullscreen mode

10. Change radius_db from radius to the database you created earlier:

radius_db = "freeradius"
Enter fullscreen mode Exit fullscreen mode

11. Uncomment read_clients so FreeRADIUS reads client details from the nas table:

read_clients = yes
Enter fullscreen mode Exit fullscreen mode

Save and close the file.

Test the FreeRADIUS Integration with PostgreSQL

Authenticate against the local server with the test user created earlier to confirm FreeRADIUS is reading correctly from the database.

1. Stop the FreeRADIUS system service:

$ sudo service freeradius stop
Enter fullscreen mode Exit fullscreen mode

2. Start FreeRADIUS in debugging mode as a background process:

$ sudo /usr/sbin/freeradius -X &
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

If you get an error like the following (address already in use):

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
Enter fullscreen mode Exit fullscreen mode

Find and stop the existing FreeRADIUS process, then start it again:

$ sudo ps aux | grep radius
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode 
$ kill-9 9374
Enter fullscreen mode Exit fullscreen mode 
$ sudo /usr/sbin/freeradius -X
Enter fullscreen mode Exit fullscreen mode

3. Authenticate as the test user with radtest:

$ radtest kiki 1234 localhost 0 testing123
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

The Received Access-Accept... response confirms you've successfully authenticated against the server.

Next Steps

  • Use the radgroupcheck and radgroupreply tables for group-based access control, such as restricting bandwidth or session limits per group
  • Add real Network Access Servers (NAS) to the nas table for production RADIUS clients
  • Review accounting data in the radacct table to track session usage
  • Consult the FreeRADIUS documentation for advanced configuration options

For the full guide with additional tips, visit the original article on Vultr Docs.

Top comments (0)