# Installing FreeRADIUS with PostgreSQL

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, following network authentication deployment practices documented in [**Vultr Docs**](https://docs.vultr.com/how-to-install-freeradius-with-postgresql).

> **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:**

```console
sudo apt-get install freeradius freeradius-postgresql
```

**2\. Check the installed version:**

```console
freeradius -v
```

Output:

```plaintext
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 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:**

```console
sudo touch /etc/freeradius_schema.sql
```

**2\. Edit the file:**

```console
sudo nano /etc/freeradius_schema.sql
```

**3\. 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. 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:**

```console
sudo -u postgres psql
```

**5\. Set a password for the** `postgres` **user:**

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

**6\. Exit the PostgreSQL console:**

```sql
postgres=# \q
```

**7\. Create a new** `freeradius` **database:**

```console
sudo -u postgres createdb freeradius
```

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

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

**9\. Import the schema file into the** `freeradius` **database:**

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

Output:

```plaintext
CREATE TABLE
CREATE INDEX
...
```

**10\. Log in to the** `freeradius` **database:**

```console
sudo -u postgres psql -d freeradius
```

**11\. Verify all FreeRADIUS tables are available:**

```sql
freeradius=# \dt
```

Output:

```plaintext
             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 sample user in the** `radcheck` **table** (username `kiki`, password `1234`):

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

**13\. Query the** `radcheck` **table to view the record:**

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

Output:

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

**14\. Create a test server entry 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');
```

**15\. Query the** `nas` **table to verify the entry:**

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

Output:

```plaintext
 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 console:**

```sql
freeradius=# \q
```

## Configure FreeRADIUS to Use PostgreSQL

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

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

**2\. Open the default site configuration file:**

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

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

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

**4\. Open the** `inner-tunnel` **site configuration file:**

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

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

```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.

**6\. Open the** `sql` **module configuration file:**

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

**7\. Change** `dialect` **from** `SQLite` **to** `PostgreSQL`**:**

```ini
dialect = "postgresql"
```

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

```ini
driver = "rlm_sql_${dialect}"
```

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

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

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

```ini
radius_db = "freeradius"
```

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

```ini
read_clients = yes
```

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:**

```console
sudo service freeradius stop
```

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

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

Output:

```plaintext
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 get an error like the following (address already in use):

```plaintext
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
```

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

```console
sudo ps aux | grep radius
```

Output:

```plaintext
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
```

```console
kill-9 9374
```

```console
sudo /usr/sbin/freeradius -X
```

**3\. Authenticate as the test user with** `radtest`**:**

```console
radtest kiki 1234 localhost 0 testing123
```

Output:

```plaintext
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 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](https://www.networkradius.com/doc/current/) for advanced configuration options
    

For the full guide with additional tips, visit the original article on [**Vultr Docs**](https://docs.vultr.com/how-to-install-freeradius-with-postgresql).