How to Install FreeRADIUS with PostgreSQL

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.

1. Install FreeRADIUS and the PostgreSQL plugin.

   CONSOLE

   Copy

   $ sudo apt-get install freeradius freeradius-postgresql

   Explain Code

   Powered by Vultr AgentBeta

2. Check the FreeRADIUS version.

   CONSOLE

   Copy

   $ freeradius -v

   Explain Code

   Powered by Vultr AgentBeta

   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.

1. Create a new freeradius_schema.sql schema file in a system-wide location such as /opt.

   CONSOLE

   Copy

   $ sudo touch /etc/freeradius_schema.sql

   Explain Code

   Powered by Vultr AgentBeta

2. Edit the file using a text editor such as nano.

   CONSOLE

   Copy

   $ sudo nano /etc/freeradius_schema.sql

   Explain Code

   Powered by Vultr AgentBeta

3. Add the following database schema to the file.

   SQL

   Copy

   /*
   *
   * 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
   );

   Explain Code

   Powered by Vultr AgentBeta

   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.

4. Access the PostgreSQL database server console as the postgres user.

   CONSOLE

   Copy

   $ sudo -u postgres psql

   Explain Code

   Powered by Vultr AgentBeta

5. Modify the postgres user password.

   SQL

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

6. Exit the PostgreSQL console.

   SQL

   Copy

   postgres=# \q

   Explain Code

   Powered by Vultr AgentBeta

7. Create a new freeradius database to use with FreeRADIUS.

   CONSOLE

   Copy

   $ sudo -u postgres createdb freeradius

   Explain Code

   Powered by Vultr AgentBeta

8. Run the following command to enable password authentication on the PostgreSQL database server. Replace 17 with the actual PostgreSQL version on your server.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

9. Import the freeradius_schema.sql file to the freeradius database.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   CREATE TABLE
   CREATE INDEX
   ...

10. Log in to the PostgreSQL database server as the postgres user and switch to the freeradius database.

    CONSOLE

    Copy

    $ sudo -u postgres psql -d freeradius

    Explain Code

    Powered by Vultr AgentBeta

11. Verify that all FreeRADIUS tables from your schema are available.

    SQL

    Copy

    freeradius=# \dt

    Explain Code

    Powered by Vultr AgentBeta

    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)

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

    SQL

    Copy

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

    Explain Code

    Powered by Vultr AgentBeta

13. Query the radcheck table to view the available records.

    SQL

    Copy

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

    Explain Code

    Powered by Vultr AgentBeta

    Output:

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

14. Create a new test server in the nas table.

    SQL

    Copy

    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');

    Explain Code

    Powered by Vultr AgentBeta

15. Query the nas table to verify the available servers.

    SQL

    Copy

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

    Explain Code

    Powered by Vultr AgentBeta

    Output:

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

16. Exit the PostgreSQL database console.

    SQL

    Copy

    freeradius=# \q

    Explain Code

    Powered by Vultr AgentBeta

Configure FreeRADIUS to Use PostgreSQL

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

1. Enable the FreeRADIUS SQL module by creating a symbolic link in the mods-enabled configurations directory.

   CONSOLE

   Copy

   $ sudo ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/

   Explain Code

   Powered by Vultr AgentBeta

2. Open the default FreeRADIUS configuration file.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

3. Find the following -sql directive and remove the - symbol to enable the SQL directive.

   INI

   Copy

   #  See "Authorization Queries" in mods-available/sql
   -sql

   Explain Code

   Powered by Vultr AgentBeta

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

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

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

   INI

   Copy

   #  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

   Explain Code

   Powered by Vultr AgentBeta

   Save and close the file.

6. Open the /mods-available/sql file.

   CONSOLE

   Copy

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

   Explain Code

   Powered by Vultr AgentBeta

7. Find the following dialect directive and change the value from SQLite to PostgreSQL.

   INI

   Copy

   dialect = "postgresql"

   Explain Code

   Powered by Vultr AgentBeta

8. Find the driver directive and change the value from rlm_sql_null to rlm_sql_${dialect} to integrate FreeRADIUS with PostgreSQL.

   INI

   Copy

   driver = "rlm_sql_${dialect}"

   Explain Code

   Powered by Vultr AgentBeta

9. Find the database connection section, uncomment all directives, and enter your PostgreSQL database information in the respective fields.

   INI

   Copy

   # Connection info:
   server = "localhost"
   port = 5432
   login = "postgres"
   password = "radpass"

   Explain Code

   Powered by Vultr AgentBeta

10. Find the radius_db directive and change the value from radius to the FreeRADIUS database you created earlier.

    INI

    Copy

    radius_db = "freeradius"

    Explain Code

    Powered by Vultr AgentBeta

11. Find and uncomment the read_clients directive to enable FreeRADIUS to read client details from the nas table.

    INI

    Copy

    read_clients = yes

    Explain Code

    Powered by Vultr AgentBeta

    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.

1. Stop the FreeRADIUS system service.

   CONSOLE

   Copy

   $ sudo service freeradius stop

   Explain Code

   Powered by Vultr AgentBeta

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

   CONSOLE

   Copy

   $ sudo /usr/sbin/freeradius -X &

   Explain Code

   Powered by Vultr AgentBeta

   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

     Copy

     $ sudo ps aux | grep radius

     Explain Code

     Powered by Vultr AgentBeta

     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

     Copy

     $ kill-9 9374

     Explain Code

     Powered by Vultr AgentBeta

   • Start the server again in debugging mode.

     CONSOLE

     Copy

     $ sudo /usr/sbin/freeradius -X

     Explain Code

     Powered by Vultr AgentBeta

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

   CONSOLE

   Copy

   $ radtest kiki 1234 localhost 0 testing123

   Explain Code

   Powered by Vultr AgentBeta

   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.