postgresql-prod/init/init.sql

SET ROLE radius;
CREATE TABLE radacct (
    radacctid BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    acctsessionid VARCHAR(64) NOT NULL DEFAULT '',
    acctuniqueid VARCHAR(32) NOT NULL DEFAULT '',
    username VARCHAR(64) NOT NULL DEFAULT '',
    groupname VARCHAR(64) NOT NULL DEFAULT '',
    realm VARCHAR(64) DEFAULT '',
    nasipaddress INET,
    nasportid VARCHAR(32),
    nasporttype VARCHAR(32),
    acctstarttime TIMESTAMP,
    acctupdatetime TIMESTAMP,
    acctstoptime TIMESTAMP,
    acctinterval INTEGER,
    acctsessiontime BIGINT,
    acctauthentic VARCHAR(32),
    connectinfo_start VARCHAR(50),
    connectinfo_stop VARCHAR(50),
    acctinputoctets BIGINT,
    acctoutputoctets BIGINT,
    calledstationid VARCHAR(50) NOT NULL DEFAULT '',
    callingstationid VARCHAR(50) NOT NULL DEFAULT '',
    acctterminatecause VARCHAR(32),
    servicetype VARCHAR(32),
    framedprotocol VARCHAR(32),
    framedipaddress INET,
    framedipv6address INET,
    framedipv6prefix CIDR,
    framedinterfaceid VARCHAR(44) NOT NULL DEFAULT '',
    delegatedipv6prefix CIDR,
    class VARCHAR(64),

    CONSTRAINT radacct_acctuniqueid_key UNIQUE (acctuniqueid)
);

CREATE INDEX idx_radacct_username ON radacct (username);
CREATE INDEX idx_radacct_framedipaddress ON radacct (framedipaddress);
CREATE INDEX idx_radacct_framedipv6address ON radacct (framedipv6address);
CREATE INDEX idx_radacct_acctsessionid ON radacct (acctsessionid);
CREATE INDEX idx_radacct_acctstarttime ON radacct (acctstarttime);
CREATE INDEX idx_radacct_acctstoptime ON radacct (acctstoptime);
CREATE INDEX idx_radacct_nasipaddress ON radacct (nasipaddress);

CREATE INDEX idx_radacct_bulk_close
ON radacct (acctstoptime, nasipaddress, acctstarttime);

CREATE TABLE radcheck (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(64) NOT NULL DEFAULT '',
    attribute VARCHAR(64) NOT NULL DEFAULT '',
    op CHAR(2) NOT NULL DEFAULT '==',
    value VARCHAR(253) NOT NULL DEFAULT ''
);

CREATE INDEX idx_radcheck_username
ON radcheck (username);


CREATE TABLE radgroupcheck (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    groupname VARCHAR(64) NOT NULL DEFAULT '',
    attribute VARCHAR(64) NOT NULL DEFAULT '',
    op CHAR(2) NOT NULL DEFAULT '==',
    value VARCHAR(253) NOT NULL DEFAULT ''
);

CREATE INDEX idx_radgroupcheck_groupname
ON radgroupcheck (groupname);


CREATE TABLE radgroupreply (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    groupname VARCHAR(64) NOT NULL DEFAULT '',
    attribute VARCHAR(64) NOT NULL DEFAULT '',
    op CHAR(2) NOT NULL DEFAULT '=',
    value VARCHAR(253) NOT NULL DEFAULT ''
);

CREATE INDEX idx_radgroupreply_groupname
ON radgroupreply (groupname);


CREATE TABLE radreply (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(64) NOT NULL DEFAULT '',
    attribute VARCHAR(64) NOT NULL DEFAULT '',
    op CHAR(2) NOT NULL DEFAULT '=',
    value VARCHAR(253) NOT NULL DEFAULT ''
);

CREATE INDEX idx_radreply_username
ON radreply (username);


CREATE TABLE radusergroup (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(64) NOT NULL DEFAULT '',
    groupname VARCHAR(64) NOT NULL DEFAULT '',
    priority INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_radusergroup_username
ON radusergroup (username);

CREATE TABLE radpostauth (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(64) NOT NULL DEFAULT '',
    reply VARCHAR(32) NOT NULL DEFAULT '',
    authdate TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reason VARCHAR(255),
    class VARCHAR(64) NOT NULL DEFAULT ''
);


CREATE TABLE nas (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nasname VARCHAR(128) NOT NULL,
    shortname VARCHAR(32),
    type VARCHAR(30) DEFAULT 'other',
    ports INTEGER,
    secret VARCHAR(60) NOT NULL DEFAULT 'secret',
    server VARCHAR(64),
    community VARCHAR(50),
    description VARCHAR(200) DEFAULT 'RADIUS Client'
);

CREATE INDEX idx_nas_nasname ON nas (nasname);


CREATE TABLE nasreload (
    nasipaddress INET PRIMARY KEY,
    reloadtime TIMESTAMP NOT NULL
);


CREATE TABLE radippool (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pool_name VARCHAR(128) NOT NULL,
    framedipaddress INET NOT NULL,
    nasipaddress INET NOT NULL,
    calledstationid VARCHAR(30) NOT NULL DEFAULT '',
    callingstationid VARCHAR(30) NOT NULL DEFAULT '',
    expiry_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    username VARCHAR(128) NOT NULL DEFAULT '',
    pool_key VARCHAR(64) NOT NULL DEFAULT '',
    tenant_id VARCHAR(64) NOT NULL DEFAULT '',

    CONSTRAINT radippool_framedipaddress_unique
        UNIQUE (framedipaddress)
);

CREATE INDEX idx_radippool_pool_lookup
ON radippool (pool_name, expiry_time);

CREATE INDEX idx_radippool_node_lookup
ON radippool (nasipaddress, pool_key, framedipaddress);

CREATE TABLE node_assignments (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    username VARCHAR(128) NOT NULL UNIQUE,
    tenant_id VARCHAR(64) NOT NULL,
    node_ip INET NOT NULL,
    vpn_ip INET,
    vpn_subnet CIDR NOT NULL,
    client_type VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT node_assignments_client_type_check
        CHECK (client_type IN ('device', 'engineer')),

    CONSTRAINT unique_ip_per_node
        UNIQUE (node_ip, vpn_ip)
);

CREATE INDEX idx_node_assignments_node
ON node_assignments (node_ip);

CREATE INDEX idx_node_assignments_tenant
ON node_assignments (tenant_id);

CREATE INDEX idx_node_assignments_username_node
ON node_assignments (username, node_ip);


CREATE TABLE tenant_subnets (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id VARCHAR(64) NOT NULL UNIQUE,
    subnet CIDR NOT NULL,
    node_ip INET NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT unique_subnet UNIQUE (subnet)
);

CREATE INDEX idx_tenant_subnets_node
ON tenant_subnets (node_ip);


CREATE TABLE node_subnets (
    id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    node_ip INET NOT NULL,
    subnet CIDR NOT NULL,
    is_allocated BOOLEAN DEFAULT FALSE,
    tenant_id VARCHAR(64),

    CONSTRAINT unique_subnet_node_subnets UNIQUE (subnet),

    CONSTRAINT chk_allocated CHECK (
        (is_allocated = TRUE AND tenant_id IS NOT NULL)
        OR
        (is_allocated = FALSE AND tenant_id IS NULL)
    )
);

CREATE INDEX idx_node_subnets_node
ON node_subnets (node_ip);

CREATE VIEW v_active_sessions AS
SELECT
    ra.username,
    ra.nasipaddress AS connected_to_node,
    na.node_ip AS assigned_node,

    CASE
        WHEN ra.nasipaddress = na.node_ip THEN 'OK'
        ELSE 'WRONG NODE'
    END AS node_status,

    ra.framedipaddress AS vpn_ip,
    ra.acctstarttime AS connected_since,
    na.tenant_id

FROM radacct ra
LEFT JOIN node_assignments na
    ON na.username = ra.username

WHERE ra.acctstoptime IS NULL;

-- Permissions for the radius application user
GRANT USAGE ON SCHEMA public TO radius;

GRANT ALL PRIVILEGES
ON ALL TABLES IN SCHEMA public
TO radius;

GRANT ALL PRIVILEGES
ON ALL SEQUENCES IN SCHEMA public
TO radius;

RESET ROLE;