iPSK Manager Deployment

1. Executive Summary

Item Value

Purpose

Deploy iPSK Manager for IoT device self-service PSK authentication

Target Host

ipsk-mgr-01.inside.domusdigitalis.dev (10.50.1.30)

Hypervisor

kvm-01 (Supermicro A)

Base OS

Ubuntu 24.04 LTS (cloud image)

Integration

ISE ODBC → iPSK MySQL for PSK lookup

2. Architecture

iPSK Manager provides self-service PSK management for IoT devices:

Component Description

Apache

Web server for sponsor portal

PHP

Application runtime (8.x)

MariaDB

PSK database (ISE queries via ODBC)

ISE Integration

ODBC data source for iPSK authentication

2.1. HA Architecture (Future)

Component Value

Primary Server

ipsk-mgr-01.inside.domusdigitalis.dev (10.50.1.30)

Secondary Server

ipsk-mgr-02.inside.domusdigitalis.dev (10.50.1.31)

VIP (HAProxy)

ipsk-mgr.inside.domusdigitalis.dev (10.50.1.32)

Operating System

Ubuntu 24.04 LTS

Certificate Authority

HashiCorp Vault PKI (pki_int)

Network Security

Management VLAN (no 802.1X - infrastructure dependency)

2.2. Security Model

Why no ISE 802.1X for these servers?

iPSK Manager servers reside in the datacenter/management VLAN where:

  • Physical access is controlled

  • All devices are infrastructure components

  • ISE itself depends on these servers for iPSK authentication

Applying 802.1X to infrastructure that ISE depends on creates circular dependencies.

Security controls applied instead:

  • SSH key-based authentication only (no passwords)

  • Vault PKI for TLS certificates (auto-renewal)

  • Firewall rules limiting access to management IPs

  • Ansible-managed configuration (auditable, version-controlled)

3. Phase 0: Preparation (Workstation)

3.1. Download Ubuntu 24.04 Cloud Image

On your workstation (Arch Linux):

curl -LO https://cloud-images.ubuntu.com/noble/current/noble-server-cloudimg-amd64.img

3.2. Create Cloud-Init Configuration

cat > /tmp/ipsk-cloud-init.yml << 'EOF'
#cloud-config
hostname: ipsk-mgr-01
fqdn: ipsk-mgr-01.inside.domusdigitalis.dev
users:
  - name: evanusmodestus
    sudo: ALL=(ALL) NOPASSWD:ALL
    shell: /bin/bash
    plain_text_passwd: changeme123
    lock_passwd: false
    ssh_authorized_keys:
      - ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIIrgE9z8gkQVRVkkdbc1ejdth7vJkqpY35FrIUv8L6JB vault-signed
      - ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIL3vaIABqHOwy88p/5GcX3ZNU044GAz/3T5dH8GIU7DS evanusmodestus@d000
      - sk-ssh-ed25519@openssh.com AAAAGnNrLXNzaC1lZDI1NTE5QG9wZW5zc2guY29tAAAAIFHfsGSAFAkqwYj6EGS9sA2MROjs28zM6LJds3gagsCkAAAACHNzaDpkMDAw evanusmodestus@d000-yubikey
      - sk-ssh-ed25519@openssh.com AAAAGnNrLXNzaC1lZDI1NTE5QG9wZW5zc2guY29tAAAAIEBZ+kus4aTHzQt1zNnEnGxJs+Lf56vrCdcyvqLhpp9hAAAACHNzaDpkMDAw ssh:d000
      - sk-ssh-ed25519@openssh.com AAAAGnNrLXNzaC1lZDI1NTE5QG9wZW5zc2guY29tAAAAIG/EGu00HuV3jnisul7DUBuk9jLtrE3yR4BZCwGb2YpCAAAABHNzaDo= d000-nano
chpasswd:
  expire: false
package_update: true
packages:
  - apache2
  - php
  - php-mbstring
  - php-ldap
  - php-mysqli
  - php-curl
  - php-xml
  - mariadb-server
  - git
  - unzip
runcmd:
  - systemctl enable apache2 mariadb
  - a2enmod rewrite ssl
  - systemctl restart apache2
EOF

3.3. Create Cloud-Init ISO

cloud-localds /tmp/ipsk-cloud-init.iso /tmp/ipsk-cloud-init.yml

On Arch Linux, install cloud-utils first:

sudo pacman -S cloud-utils

3.4. Transfer Files to kvm-01

scp ~/Downloads/noble-server-cloudimg-amd64.img kvm-01:/tmp/
scp /tmp/ipsk-cloud-init.iso kvm-01:/tmp/

4. Phase 1: VM Creation (kvm-01)

SSH to kvm-01 and execute the following:

4.1. Prepare Disk Image

# Copy cloud image to VM storage
sudo cp /tmp/noble-server-cloudimg-amd64.img /mnt/onboard-ssd/libvirt/images/ipsk-mgr-01.qcow2
# Resize disk to 20GB
sudo qemu-img resize /mnt/onboard-ssd/libvirt/images/ipsk-mgr-01.qcow2 20G
# Copy cloud-init ISO
sudo mv /tmp/ipsk-cloud-init.iso /mnt/onboard-ssd/isos/

4.2. Create and Start VM

Use br-mgmt bridge for MGMT VLAN connectivity. The libvirt hook sets PVID 100 for VMs in the PVID100_VMS list.

 
sudo virt-install \
  --name ipsk-mgr-01 \
  --memory 4096 \
  --vcpus 2 \
  --import \
  --disk path=/mnt/onboard-ssd/libvirt/images/ipsk-mgr-01.qcow2,format=qcow2 \
  --disk path=/mnt/onboard-ssd/isos/ipsk-cloud-init.iso,device=cdrom \
  --network bridge=br-mgmt,model=virtio \
  --os-variant ubuntu24.04 \
  --graphics vnc \
  --noautoconsole

4.3. Verify Libvirt Hook Configuration

Ensure ipsk-mgr-01 is in the PVID100_VMS list:

sudo grep "ipsk-mgr" /etc/libvirt/hooks/qemu

If missing, add it:

sudo vi /etc/libvirt/hooks/qemu
# Add ipsk-mgr-01 to PVID100_VMS list

4.4. Verify VM Started

sudo virsh list --all | grep ipsk
Expected output
 -     ipsk-mgr-01   running

4.5. Verify VLAN Assignment

sudo virsh domiflist ipsk-mgr-01
Expected: bridge should be br-mgmt
Interface   Type     Source    Model    MAC
-----------------------------------------------------------
vnet17      bridge   br-mgmt   virtio   52:54:00:xx:xx:xx
bridge vlan show dev $(sudo virsh domiflist ipsk-mgr-01 | awk '/vnet/{print $1}')
Expected: PVID 100
port              vlan-id
vnet17            100 PVID Egress Untagged

4.6. Troubleshooting: No Network Connectivity

If VM cannot reach gateway (10.50.1.1):

Check libvirt hook logs:

sudo journalctl -t "libvirt-hook[ipsk-mgr-01]" --since "10 minutes ago"

Common issues:

Symptom Cause Fix

"No vnets found on br-mgmt"

VM using wrong bridge (virbr0)

Change bridge to br-mgmt in VM XML

PVID shows 1 instead of 100

VM name not in PVID100_VMS list

Add to /etc/libvirt/hooks/qemu

Hook not running

Hook not executable

sudo chmod 755 /etc/libvirt/hooks/qemu

Fix wrong bridge:

sudo virsh destroy ipsk-mgr-01
sudo sed -i "s/bridge='virbr0'/bridge='br-mgmt'/" /etc/libvirt/qemu/ipsk-mgr-01.xml
sudo virsh define /etc/libvirt/qemu/ipsk-mgr-01.xml
sudo virsh start ipsk-mgr-01

Manually set VLAN (temporary fix):

VNET=$(sudo virsh domiflist ipsk-mgr-01 | awk '/vnet/{print $1}')
sudo bridge vlan add vid 100 dev $VNET pvid untagged
sudo bridge vlan del vid 1 dev $VNET

4.7. Connect via VNC (if SSH not yet available)

From workstation:

# Create SSH tunnel (VNC display number from virsh vncdisplay ipsk-mgr-01)
ssh -L 5905:localhost:5902 kvm-01
# In another terminal
vncviewer localhost:5905

On Arch Linux, install tigervnc:

sudo pacman -S tigervnc

5. Phase 2: Initial Configuration

5.1. Configure Static IP

Ubuntu 24.04 cloud images use netplan (not NetworkManager). The interface name is typically enp1s0.

5.1.1. Verify Interface Name

ip link show | awk '/^[0-9]+:/{print $2}' | grep -v lo
Expected output
enp1s0:

5.1.2. Check Current Configuration

cat /etc/netplan/50-cloud-init.yaml

5.1.3. Create Static IP Configuration

sudo tee /etc/netplan/99-static.yaml << 'EOF'
network:
  version: 2
  ethernets:
    enp1s0:
      dhcp4: false
      dhcp6: false
      addresses:
        - 10.50.1.30/24
      routes:
        - to: default
          via: 10.50.1.1
      nameservers:
        addresses:
          - 10.50.1.1
          - 10.50.1.90
        search:
          - inside.domusdigitalis.dev
EOF

5.1.4. Secure File Permissions

Netplan requires config files to be readable only by root:

sudo chmod 600 /etc/netplan/99-static.yaml

5.1.5. Apply Configuration

This will disconnect your SSH session. Reconnect using the static IP (10.50.1.30).

 
sudo netplan apply

5.1.6. Reconnect via Static IP

From workstation:

ssh evanusmodestus@10.50.1.30

5.2. Change Default Password

passwd

5.3. Verify Network

# Check IP
ip -4 addr show enp1s0 | awk '/inet/{print $2}'
# Test gateway
ping -c3 10.50.1.1
# Test DNS
dig ise-01.inside.domusdigitalis.dev +short

5.4. Add DNS Records

Add A records to BIND (bind-01):

ssh bind-01 "sudo nsupdate -l << 'EOF'
zone inside.domusdigitalis.dev
update add ipsk-mgr-01.inside.domusdigitalis.dev 86400 A 10.50.1.30
update add ipsk-mgr-02.inside.domusdigitalis.dev 86400 A 10.50.1.31
update add ipsk-mgr.inside.domusdigitalis.dev 86400 A 10.50.1.32
send
EOF"

Verify:

dig +short ipsk-mgr-01.inside.domusdigitalis.dev

5.5. Verify SSH Access

From workstation:

ssh evanusmodestus@10.50.1.30

6. Phase 3: iPSK Manager Application Installation

6.1. Verify Prerequisites

Confirm cloud-init installed required packages:

dpkg -l | awk '/apache2|php|mariadb-server|git/{print $2, $3}'
Expected output (versions may vary)
apache2 2.4.58-1ubuntu8.x
git 1:2.43.0-1ubuntu7.x
mariadb-server 1:10.11.x
php8.3 8.3.x
php8.3-curl 8.3.x
php8.3-ldap 8.3.x
php8.3-mbstring 8.3.x
php8.3-mysqli 8.3.x
php8.3-xml 8.3.x
# Verify services running
systemctl is-active apache2 mariadb

6.2. Clone iPSK Manager Repository

cd /var/www
sudo git clone https://github.com/CiscoDevNet/iPSK-Manager.git ipsk
sudo chown -R www-data:www-data /var/www/ipsk

6.3. Configure MariaDB

sudo mysql_secure_installation

Answer prompts:

  • Switch to unix_socket authentication: Y (more secure, uses OS auth)

  • Change the root password: n (unix_socket handles auth via sudo mysql)

  • Remove anonymous users: Y

  • Disallow root login remotely: Y

  • Remove test database: Y

  • Reload privilege tables: Y

6.4. Create Database

First, generate password for iPSK database user (on workstation):

gopass generate v3/domains/d000/services/ipsk-mgr/mariadb-ipsk 32

Then create database (on ipsk-mgr-01):

sudo mysql << 'EOF'
CREATE DATABASE ipsk;
CREATE USER 'ipsk'@'localhost' IDENTIFIED BY 'PASTE_PASSWORD_HERE';
GRANT ALL PRIVILEGES ON ipsk.* TO 'ipsk'@'localhost';
FLUSH PRIVILEGES;
EOF

With unix_socket auth, root connects via sudo mysql without a password. The ipsk application user still needs a password for database connections.

6.5. Configure Apache Virtual Host (HTTP - Optional)

Skip this section if using HTTPS (recommended). Proceed directly to Phase 4 and 5 for TLS setup.

 
sudo tee /etc/apache2/sites-available/ipsk.conf << 'EOF'
<VirtualHost *:80>
    ServerName ipsk-mgr-01.inside.domusdigitalis.dev
    DocumentRoot /var/www/ipsk/adminportal

    <Directory /var/www/ipsk/adminportal>
        AllowOverride All
        Require all granted
    </Directory>

    ErrorLog ${APACHE_LOG_DIR}/ipsk-error.log
    CustomLog ${APACHE_LOG_DIR}/ipsk-access.log combined
</VirtualHost>
EOF
sudo a2ensite ipsk
sudo a2dissite 000-default
sudo systemctl reload apache2

6.6. Run iPSK Setup Wizard

Complete Phase 4 and Phase 5 first to enable HTTPS before running the setup wizard.

Open browser: ipsk-mgr-01.inside.domusdigitalis.dev/supportfiles/setup.php

Follow the web-based setup wizard to:

  1. Configure database connection (use ipsk user password from gopass)

  2. Create admin account

  3. Configure LDAP/AD integration (optional)

  4. Set up ISE ODBC connection

7. Phase 4: Vault PKI TLS Certificate

7.1. Issue Certificate from Vault

From workstation:

dsource d000 dev/vault
vault write pki_int/issue/domus-server \
  common_name="ipsk-mgr-01.inside.domusdigitalis.dev" \
  ip_sans="10.50.1.30" \
  ttl="8760h" \
  -format=json > /dev/shm/ipsk-mgr-01-cert.json

7.2. Extract Certificate Files

jq -r '.data.certificate' /dev/shm/ipsk-mgr-01-cert.json > /dev/shm/ipsk-mgr-01.crt
jq -r '.data.private_key' /dev/shm/ipsk-mgr-01-cert.json > /dev/shm/ipsk-mgr-01.key
jq -r '.data.ca_chain[]' /dev/shm/ipsk-mgr-01-cert.json > /dev/shm/ca-chain.pem

7.3. Deploy Certificate to iPSK

scp /dev/shm/ipsk-mgr-01.crt /dev/shm/ipsk-mgr-01.key /dev/shm/ca-chain.pem 10.50.1.30:/tmp/
ssh 10.50.1.30 << 'EOF'
sudo mv /tmp/ipsk-mgr-01.crt /etc/ssl/certs/
sudo mv /tmp/ipsk-mgr-01.key /etc/ssl/private/
sudo mv /tmp/ca-chain.pem /etc/ssl/certs/
sudo chmod 600 /etc/ssl/private/ipsk-mgr-01.key
EOF

8. Phase 5: Apache SSL Configuration

8.1. Enable SSL Module

sudo a2enmod ssl

8.2. Create SSL Virtual Host

DocumentRoot must be /var/www/ipsk/adminportal - the installer and index.php are in the adminportal/ subdirectory, not the repo root.

 
sudo tee /etc/apache2/sites-available/ipsk-ssl.conf << 'EOF'
<VirtualHost *:443>
    ServerName ipsk-mgr-01.inside.domusdigitalis.dev
    DocumentRoot /var/www/ipsk/adminportal

    SSLEngine on
    SSLCertificateFile /etc/ssl/certs/ipsk-mgr-01.crt
    SSLCertificateKeyFile /etc/ssl/private/ipsk-mgr-01.key
    SSLCertificateChainFile /etc/ssl/certs/ca-chain.pem

    SSLProtocol all -SSLv2 -SSLv3 -TLSv1 -TLSv1.1
    SSLCipherSuite ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256

    <Directory /var/www/ipsk/adminportal>
        AllowOverride All
        Require all granted
    </Directory>

    ErrorLog ${APACHE_LOG_DIR}/ipsk-ssl-error.log
    CustomLog ${APACHE_LOG_DIR}/ipsk-ssl-access.log combined
</VirtualHost>
EOF
sudo a2ensite ipsk-ssl
sudo systemctl reload apache2

8.3. Verify HTTPS

curl -Ik https://localhost 2>&1 | head -5
Expected: HTTP/1.1 200 OK
HTTP/1.1 200 OK
Date: ...

9. Phase 5.5: iPSK Manager Setup Wizard

9.1. Create Temporary Install User

The web-based installer cannot use MariaDB root with unix_socket authentication (no password for network auth). Create a temporary install user with full privileges.

 
sudo mysql << 'EOF'
CREATE USER 'install'@'localhost' IDENTIFIED BY 'TempInstall123!';
GRANT ALL PRIVILEGES ON *.* TO 'install'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EOF

9.2. Run Setup Wizard

Open browser: ipsk-mgr-01.inside.domusdigitalis.dev/supportfiles/setup.php

9.2.1. PHP Validation

The installer checks PHP extensions. If any show "NOT Installed":

# Missing mysqli
sudo apt install php8.3-mysql

# Missing mbstring
sudo apt install php8.3-mbstring

# Restart Apache after installing extensions
sudo systemctl reload apache2

9.2.2. Database Setup

Enter credentials:

Field Value

Database Server

localhost

Database Name

ipsk

Database Username

install

Database Password

TempInstall123!

Let the installer create the database fresh. Do NOT pre-create the ipsk database or you’ll get "Database Already Exists" error.

9.2.3. Installation Failure: Database Already Exists

If you already created the database manually, drop it first:

sudo mysql << 'EOF'
DROP DATABASE IF EXISTS ipsk;
DROP USER IF EXISTS 'ipsk'@'localhost';
FLUSH PRIVILEGES;
EOF

Then retry the setup wizard.

9.2.4. Installation Failure: Cannot Create config.php

sudo chown -R www-data:www-data /var/www/ipsk
sudo chmod -R 755 /var/www/ipsk

9.3. Post-Installation: Database User for Application

After successful installation, the installer creates config.php at:

/var/www/ipsk/supportfiles/include/config.php

Create the permanent ipsk database user (if installer didn’t):

IPSK_PASS=$(gopass show v3/domains/d000/services/ipsk-mgr/mariadb-ipsk)

sudo mysql << EOF
CREATE USER IF NOT EXISTS 'ipsk'@'localhost' IDENTIFIED BY '$IPSK_PASS';
GRANT ALL PRIVILEGES ON ipsk.* TO 'ipsk'@'localhost';
FLUSH PRIVILEGES;
EOF

Update config.php with the permanent password:

sudo sed -i "s/TempInstall123!/$IPSK_PASS/" /var/www/ipsk/supportfiles/include/config.php

9.4. Remove Temporary Install User

Security: Remove the install user after setup completes.

 
sudo mysql << 'EOF'
DROP USER 'install'@'localhost';
FLUSH PRIVILEGES;
EOF

9.5. Database Schema Migration (v6 → v7)

After first login, iPSK Manager may show "Database Schema Update Required".

9.5.1. SUPER Privilege Error

If migration fails with:

Access denied; you need (at least one of) the SUPER, SET USER privilege(s)

Grant SUPER temporarily:

sudo mysql << 'EOF'
GRANT SUPER ON *.* TO 'ipsk'@'localhost';
FLUSH PRIVILEGES;
EOF

Enter ipsk credentials in the migration form and run migration.

After migration succeeds, revoke SUPER (least privilege):

sudo mysql << 'EOF'
REVOKE SUPER ON *.* FROM 'ipsk'@'localhost';
FLUSH PRIVILEGES;
EOF

9.6. Login Credentials

After installation, the installer creates a DONOTDELETE-iPSKMANAGER file with credentials.

sudo cat /var/www/ipsk/supportfiles/DONOTDELETE-iPSKMANAGER

The admin username is Administrator (not what’s shown in the file for Apache auth).

10. Phase 5.6: MariaDB TLS Configuration

ISE ODBC connections should use TLS to encrypt credentials and data in transit.

10.1. Check Current TLS Status

sudo mysql -e "SHOW VARIABLES LIKE '%ssl%';"

If have_ssl shows DISABLED, configure TLS below.

10.2. Issue MariaDB Certificate from Vault

On workstation:

vault write pki_int/issue/domus-server \
  common_name="ipsk-mgr-01.inside.domusdigitalis.dev" \
  ip_sans="10.50.1.30" \
  ttl="8760h" \
  -format=json > /dev/shm/mariadb-cert.json
jq -r '.data.certificate' /dev/shm/mariadb-cert.json > /dev/shm/mariadb.crt
jq -r '.data.private_key' /dev/shm/mariadb-cert.json > /dev/shm/mariadb.key
jq -r '.data.ca_chain[]' /dev/shm/mariadb-cert.json > /dev/shm/mariadb-ca.pem
scp /dev/shm/mariadb.crt /dev/shm/mariadb.key /dev/shm/mariadb-ca.pem 10.50.1.30:/tmp/

10.3. Deploy Certificates

On ipsk-mgr-01:

sudo mkdir -p /etc/mysql/ssl
sudo mv /tmp/mariadb.crt /tmp/mariadb.key /tmp/mariadb-ca.pem /etc/mysql/ssl/
sudo chown mysql:mysql /etc/mysql/ssl/*
sudo chmod 600 /etc/mysql/ssl/mariadb.key
sudo chmod 644 /etc/mysql/ssl/mariadb.crt /etc/mysql/ssl/mariadb-ca.pem

10.4. Configure MariaDB TLS

sudo tee /etc/mysql/mariadb.conf.d/99-ssl.cnf << 'EOF'
[mysqld]
ssl_ca = /etc/mysql/ssl/mariadb-ca.pem
ssl_cert = /etc/mysql/ssl/mariadb.crt
ssl_key = /etc/mysql/ssl/mariadb.key
require_secure_transport = OFF
EOF

require_secure_transport = OFF allows both TLS and non-TLS connections. Set to ON after verifying ISE ODBC works with TLS.

10.5. Enable Remote Connections

By default, MariaDB only listens on localhost. Enable network access for ISE:

sudo tee /etc/mysql/mariadb.conf.d/98-network.cnf << 'EOF'
[mysqld]
bind-address = 0.0.0.0
EOF
sudo systemctl restart mariadb

10.6. Verify Network Listening

ss -tlnp | grep 3306
Expected output (0.0.0.0, not 127.0.0.1)
LISTEN 0      80           0.0.0.0:3306      0.0.0.0:*

10.7. Verify TLS Enabled

sudo mysql -e "SHOW VARIABLES LIKE 'have_ssl';"
Expected output
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+

10.8. Test TLS Connection Locally

mysql -u ipsk -p --ssl -e "SHOW STATUS LIKE 'Ssl_cipher';"

Should show a cipher like TLS_AES_256_GCM_SHA384.

10.9. Create ISE Read-Only User with SSL Requirement

READONLY_PASS=$(gopass generate v3/domains/d000/services/ipsk-mgr/mariadb-readonly 32)

sudo mysql << EOF
DROP USER IF EXISTS 'ipsk_readonly'@'%';
CREATE USER 'ipsk_readonly'@'%' IDENTIFIED BY '$READONLY_PASS' REQUIRE SSL;
GRANT SELECT ON ipsk.* TO 'ipsk_readonly'@'%';
FLUSH PRIVILEGES;
EOF

REQUIRE SSL forces ISE to use TLS for this user. Connection fails without TLS.

11. Phase 6: ISE ODBC Configuration

11.1. CA Certificate (Already in ISE)

ISE already has DOMUS-ISSUING-CA and DOMUS-ROOT-CA trusted from EAP-TLS setup. The MariaDB certificate is issued from the same Vault PKI - no new import required.

Verify in ISE: Administration → System → Certificates → Trusted Certificates → Look for DOMUS-ISSUING-CA

11.2. Create ODBC Identity Source in ISE

ISE Admin → Administration → External Identity Sources → ODBC → Add

Setting Value

Name

iPSK-Manager-ODBC

ODBC Driver

MySQL ODBC 8.0 Unicode Driver

Hostname (Primary)

10.50.1.30

Hostname (Failover)

10.50.1.31

Database

ipsk

Username

ipsk-ise

Password

gopass show v3/domains/d000/services/ipsk-mgr/mariadb-ise

Port

3306

Enable SSL

Yes ✓

SSL CA Certificate

(Select imported DOMUS CA cert)

11.3. Quick Reference (Copy-Paste)

Database:  ipsk
Username:  ipsk-ise
Host:      10.50.1.30
Port:      3306
SSL:       Enabled

The ipsk-ise user was created automatically during iPSK Manager setup. ISE must use TLS or connection will fail.

11.4. Test Connection

Click Test Connection in ISE ODBC configuration. Should show success.

11.5. Configure Stored Procedures

iPSK Manager uses 5 stored procedures for ISE ODBC authentication. Configure these in the ISE ODBC Stored Procedures tab.

11.5.1. Required Stored Procedures

Procedure Purpose Required

iPSK_AttributeFetch

Fetch endpoint attributes (fullName, vlan, dacl, subscriberName)

Yes

iPSK_AuthMACPlain

Plain text password authentication

Yes

iPSK_FetchGroups

Fetch endpoint groups

Yes

iPSK_FetchPasswordForMAC

Get PSK for specific MAC address

Yes

iPSK_MACLookup

Check if MAC exists in database

Yes

11.5.2. ISE Stored Procedure Configuration

In ISE ODBC configuration → Stored Procedures tab:

ISE Field Stored Procedure Name

Fetch Attributes

iPSK_AttributeFetch

Plain Text Password Authentication

iPSK_AuthMACPlain

Fetch Groups

iPSK_FetchGroups

Fetch Plain Text Password

iPSK_FetchPasswordForMAC

Check Username or Machine Exists

iPSK_MACLookup

11.5.3. Verify Stored Procedures Exist

ssh ipsk-mgr-01 "sudo mysql ipsk -e \"SHOW PROCEDURE STATUS WHERE Db='ipsk';\" | awk -F'\t' 'NR>1{print \$2}' | sort"

Expected output (8 procedures):

iPSK_AttributeFetch
iPSK_AuthMACPlain
iPSK_AuthMACPlainNonExpired
iPSK_FetchGroups
iPSK_FetchPasswordForMAC
iPSK_FetchPasswordForMACNonExpired
iPSK_MACLookup
iPSK_MACLookupNonExpired

11.5.4. Troubleshooting: Missing iPSK_AttributeFetch

The v7 migration for iPSK_AttributeFetch may fail silently due to unresolved placeholder variables ({DB_NAME}, {ISE_DB_USERNAME}). If Fetch attributes test fails in ISE, apply the migration manually.

Root Cause: The migration file /var/www/ipsk/supportfiles/db/migrations/v7__attribute_fetch_subscriber_name.sql contains placeholders that weren’t replaced during install.

Fix: Apply the migration with correct substitutions:

ssh ipsk-mgr-01 "cat /var/www/ipsk/supportfiles/db/migrations/v7__attribute_fetch_subscriber_name.sql | sed 's/{{DB_NAME}}/ipsk/g; s/{{ISE_DB_USERNAME}}/ipsk-ise/g' | sudo mysql"

Verify the procedure exists:

ssh ipsk-mgr-01 "sudo mysql ipsk -e \"CALL iPSK_AttributeFetch('*', @result); SELECT @result;\""

Expected output:

fullName  emailAddress  createdBy  description  expirationDate  accountExpired  pskValue  pskValuePlain  vlan   dacl   subscriberName
Empty     Empty         Empty      Empty        0               False           EMPTY     EMPTY          Empty  Empty  subscriber:username=Empty
@result
0

After applying, retest the ISE ODBC connection - Fetch attributes should now pass.

12. Phase 7: Vault Agent for Certificate Renewal (Optional)

Only configure Vault Agent if you need automatic certificate renewal. Manual renewal from workstation is simpler for single-server deployments.

12.1. Install Vault Agent

curl -fsSL https://apt.releases.hashicorp.com/gpg | sudo gpg --dearmor -o /usr/share/keyrings/hashicorp-archive-keyring.gpg
echo "deb [signed-by=/usr/share/keyrings/hashicorp-archive-keyring.gpg] https://apt.releases.hashicorp.com $(lsb_release -cs) main" | sudo tee /etc/apt/sources.list.d/hashicorp.list
sudo apt update && sudo apt install -y vault

12.2. Configure Vault Agent

sudo mkdir -p /etc/vault.d/templates

Create /etc/vault.d/agent.hcl:

sudo tee /etc/vault.d/agent.hcl << 'EOF'
pid_file = "/var/run/vault-agent.pid"

vault {
  address = "https://vault-01.inside.domusdigitalis.dev:8200"
  tls_skip_verify = false
}

auto_auth {
  method "approle" {
    config = {
      role_id_file_path = "/etc/vault.d/role-id"
      secret_id_file_path = "/etc/vault.d/secret-id"
    }
  }

  sink "file" {
    config = {
      path = "/var/run/vault-token"
    }
  }
}

template {
  source = "/etc/vault.d/templates/cert.tpl"
  destination = "/etc/ssl/certs/ipsk-mgr-01.crt"
  command = "systemctl reload apache2"
}

template {
  source = "/etc/vault.d/templates/key.tpl"
  destination = "/etc/ssl/private/ipsk-mgr-01.key"
  perms = "0600"
  command = "systemctl reload apache2"
}
EOF

12.3. Create Certificate Template

sudo tee /etc/vault.d/templates/cert.tpl << 'EOF'
{{ with secret "pki_int/issue/domus-server" "common_name=ipsk-mgr-01.inside.domusdigitalis.dev" "ip_sans=10.50.1.30" "ttl=720h" }}
{{ .Data.certificate }}
{{ end }}
EOF

12.4. Create Key Template

sudo tee /etc/vault.d/templates/key.tpl << 'EOF'
{{ with secret "pki_int/issue/domus-server" "common_name=ipsk-mgr-01.inside.domusdigitalis.dev" "ip_sans=10.50.1.30" "ttl=720h" }}
{{ .Data.private_key }}
{{ end }}
EOF

12.5. Enable Vault Agent Service

sudo systemctl enable --now vault-agent

13. Phase 8: ISE Policy Configuration for iPSK

With ODBC connection established, configure ISE authorization rules to use iPSK attributes for wireless authentication.

13.1. Architecture Overview

Component Purpose

Policy Set

Domus_MAB (existing - handles MAB authentications)

Identity Source

iPSK-Manager-ODBC (configured in Phase 6)

Authorization Rule

Matches PSK-enabled endpoints, assigns VLAN/PSK

Authorization Profile

Defines VLAN, dACL, PSK delivery via RADIUS attributes

13.2. ISE API Environment Setup

Load credentials (on workstation):

dsource d000 dev/ise

Verify environment variables set:

echo "ISE: $ISE_PAN_FQDN | User: $ISE_API_USER"
Expected output
ISE: ise-02.inside.domusdigitalis.dev | User: admin

13.3. List Existing Policy Sets

curl -sk -u "$ISE_API_USER:$ISE_API_PASS" \
  "https://$ISE_PAN_FQDN/api/v1/policy/network-access/policy-set" \
  -H "Accept: application/json" | jq '.response[] | {id, name, state}'

Find the MAB policy set ID (typically Domus_MAB):

POLICY_NAME="Domus_MAB"
POLICY_ID=$(curl -sk -u "$ISE_API_USER:$ISE_API_PASS" \
  "https://$ISE_PAN_FQDN/api/v1/policy/network-access/policy-set" \
  -H "Accept: application/json" | jq -r --arg name "$POLICY_NAME" '.response[] | select(.name==$name) | .id')
echo "Policy ID: $POLICY_ID"

13.4. Create iPSK Authorization Profile

The authorization profile defines what happens when a PSK device authenticates successfully.

Via ISE GUI:

  1. Policy → Policy Elements → Results → Authorization → Authorization Profiles → Add

Field Value

Name

iPSK-IoT-Profile

Description

iPSK-authenticated IoT devices - VLAN 40

Access Type

ACCESS_ACCEPT

VLAN

IOT_VLAN (or VLAN ID 40)

Downloadable ACL

(optional - select IoT dACL if exists)

  1. Click Submit

Via API:

curl -sk -u "$ISE_API_USER:$ISE_API_PASS" \
  "https://$ISE_PAN_FQDN/ers/config/authorizationprofile" \
  -H "Accept: application/json" \
  -H "Content-Type: application/json" \
  -X POST \
  -d '{
    "AuthorizationProfile": {
      "name": "iPSK-IoT-Profile",
      "description": "iPSK-authenticated IoT devices - VLAN 40",
      "accessType": "ACCESS_ACCEPT",
      "vlan": {
        "nameID": "IOT_VLAN",
        "tagID": 1
      }
    }
  }'

13.5. Create iPSK Authorization Rule

Add rule to Domus_MAB policy set that matches endpoints authenticated via iPSK ODBC.

Via ISE GUI:

  1. Policy → Policy Sets → Domus_MAB → Authorization Policy → Add Rule Above

Field Value

Rule Name

iPSK_Wireless_IoT

Conditions

IdentityGroup:Name EQUALS Endpoint Identity Groups:iPSK_Devices AND Wireless_MAB (if available)

Profiles

iPSK-IoT-Profile

Security Group

(optional)

The condition should match endpoints that were successfully authenticated via the iPSK ODBC identity source. ISE automatically uses the ODBC source when the endpoint MAC is found in the iPSK database.

13.6. Configure Identity Source Sequence

Ensure iPSK-Manager-ODBC is included in the identity source sequence used by the MAB policy.

  1. Administration → Identity Management → Identity Source Sequences → Add/Edit

Field Value

Name

MAB_Identity_Sequence

Identity Sources

  1. iPSK-Manager-ODBC

  2. Internal Endpoints

  1. Assign this sequence to the Domus_MAB policy set authentication rule.

13.7. Test iPSK Authentication

13.7.1. Add Test Device to iPSK Manager

  1. Login to iPSK Manager: ipsk-mgr-01.inside.domusdigitalis.dev

  2. Endpoints → Add Endpoint

  3. Enter MAC address of test device

  4. Assign to endpoint group (e.g., IoT_Devices)

  5. Generate or assign PSK

13.7.2. Connect Test Device

On the test device, connect to the iPSK-enabled SSID using the assigned PSK.

13.7.3. Verify in ISE

netapi ise mnt sessions | awk '/iPSK/{print}'

Or check via DataConnect:

netapi ise dc query "
  SELECT
    TO_CHAR(acs_timestamp, 'YYYY-MM-DD HH24:MI:SS') as time,
    calling_station_id as mac,
    selected_azn_profiles as profile,
    passed as status
  FROM mnt.radius_auth_48_live
  WHERE selected_azn_profiles LIKE '%iPSK%'
  ORDER BY acs_timestamp DESC
  FETCH FIRST 5 ROWS ONLY"

13.8. Verify RADIUS Attributes Returned

Check that iPSK attributes are returned by the ODBC stored procedures:

ssh ipsk-mgr-01 "sudo mysql ipsk -e \"CALL iPSK_FetchPasswordForMAC('AA:BB:CC:DD:EE:FF');\""

Replace AA:BB:CC:DD:EE:FF with your test device MAC.

13.9. Troubleshooting iPSK Policy

13.9.1. Authentication Fails - Check ODBC

# Verify stored procedure returns data
ssh ipsk-mgr-01 "sudo mysql ipsk -e \"CALL iPSK_MACLookup('AA:BB:CC:DD:EE:FF');\""

13.9.2. Wrong VLAN Assigned

Check authorization profile is correctly configured and rule rank is appropriate (higher rank = evaluated first).

curl -sk -u "$ISE_API_USER:$ISE_API_PASS" \
  "https://$ISE_PAN_FQDN/api/v1/policy/network-access/policy-set/$POLICY_ID/authorization" \
  -H "Accept: application/json" | jq '.response[] | {rank: .rule.rank, name: .rule.name, profile: .profile}'

13.9.3. ISE Live Logs

Check Operations → RADIUS → Live Logs for detailed authentication flow:

  1. Look for iPSK-Manager-ODBC in Identity Store column

  2. Check Failure Reason if authentication fails

  3. Verify Authorization Profile matches expected

14. Validation Checklist

Check Command Expected

VM running

sudo virsh list --all | grep ipsk

running

DNS resolution

dig ipsk-mgr-01.inside.domusdigitalis.dev +short

10.50.1.30

SSH access

ssh 10.50.1.30 hostname

ipsk-mgr-01

HTTP accessible

curl -sI 10.50.1.30 | head -1

HTTP/1.1 200 OK

HTTPS accessible (if TLS configured)

curl -sk 10.50.1.30 | head -1

200 OK

Apache running

systemctl status apache2

active (running)

MariaDB running

systemctl status mariadb

active (running)

TLS cert valid (if configured)

openssl x509 -in /etc/ssl/certs/ipsk-mgr-01.crt -noout -dates

Not expired

ISE ODBC test

ISE Admin → External Identity Sources → Test Connection

Success

15. HA Configuration (Future - Phase 8)

HA with ipsk-mgr-02 requires:

  1. Deploy second VM on kvm-02 using same process

  2. Configure MariaDB replication between nodes

  3. Deploy HAProxy or keepalived for VIP failover

  4. Update ISE ODBC with primary/failover hostnames

See iPSK Manager HA Architecture runbook (planned - not yet created).

15.1. MariaDB Replication Setup (Preview)

On ipsk-mgr-01 (primary), edit /etc/mysql/mariadb.conf.d/50-server.cnf:

[mysqld]
server-id = 1
log_bin = mysql-bin
binlog_format = ROW
bind-address = 10.50.1.30

On ipsk-mgr-02 (replica):

[mysqld]
server-id = 2
log_bin = mysql-bin
binlog_format = ROW
read_only = ON
bind-address = 10.50.1.31

15.2. Failover Procedure

Promote ipsk-mgr-02 to primary:

STOP REPLICA;
SET GLOBAL read_only = OFF;

Update ISE ODBC:

  1. ISE Admin → Administration → External Identity Sources → ODBC

  2. Edit iPSK-Manager-ODBC

  3. Swap Primary and Failover hostnames

  4. Test Connection

15.3. Verify Authentication After Failover

netapi ise mnt sessions | awk '/iPSK/{print}'

16. Troubleshooting

16.1. 404 on /supportfiles/setup.php

Symptom: Browser shows "Not Found" when accessing setup wizard.

Root Cause: DocumentRoot points to /var/www/ipsk but the index.php and setup.php are in /var/www/ipsk/adminportal/.

Diagnosis - Check the README:

grep -n -i "install\|setup\|wizard\|database\|config" /var/www/ipsk/README.md

Find actual application files:

find /var/www/ipsk -name "*.php" -type f | head -10
ls /var/www/ipsk/adminportal/*.php

Fix with sed:

sudo sed -i 's|DocumentRoot /var/www/ipsk|DocumentRoot /var/www/ipsk/adminportal|g' /etc/apache2/sites-available/ipsk-ssl.conf
sudo sed -i 's|<Directory /var/www/ipsk>|<Directory /var/www/ipsk/adminportal>|g' /etc/apache2/sites-available/ipsk-ssl.conf
sudo systemctl reload apache2

16.2. mysqli Extension Not Installed

Symptom: PHP Validation shows "PHP Extension 'mysqli' is NOT Installed"

Root Cause: Ubuntu 24.04 requires explicit php8.3-mysql package (not installed by php-mysqli).

Fix:

sudo apt install php8.3-mysql
sudo systemctl reload apache2

16.3. Unix Socket Authentication vs Web Installer

Problem: MariaDB root with unix_socket can’t be used by PHP web apps.

Understanding:

Auth Method How It Works Use Case

unix_socket

OS user → MariaDB user mapping via socket

CLI access: sudo mysql (no password)

Password

Traditional username/password

Network/web app connections

Solution: Create temporary install user with password authentication for web installer.

16.4. Apache SSL Certificate Errors

Check certificate validity:

openssl x509 -in /etc/ssl/certs/ipsk-mgr-01.crt -noout -dates -subject

Verify CA chain:

openssl verify -CAfile /etc/ssl/certs/ca-chain.pem /etc/ssl/certs/ipsk-mgr-01.crt

Check Apache config syntax:

sudo apache2ctl configtest

16.5. Useful sed Patterns

Pattern Purpose

sed -i 's|old|new|g' file

Substitute with pipe delimiter (avoids escaping /)

sed -i "s/pattern/$VARIABLE/" file

Substitute with shell variable expansion

sed -n 'Np' file

Print line N only

sed -i '/pattern/d' file

Delete lines matching pattern

16.6. Debug Checklist

# 1. Apache running?
systemctl status apache2

# 2. Apache config valid?
sudo apache2ctl configtest

# 3. DocumentRoot correct?
grep DocumentRoot /etc/apache2/sites-enabled/*.conf

# 4. Files exist where expected?
ls -la /var/www/ipsk/adminportal/supportfiles/setup.php

# 5. Permissions correct?
ls -la /var/www/ipsk/supportfiles/include/

# 6. Apache error log
sudo tail -50 /var/log/apache2/ipsk-ssl-error.log

# 7. PHP modules loaded?
php -m | grep -E "mysqli|mbstring|curl|ldap"

# 8. MariaDB accessible?
sudo mysql -e "SHOW DATABASES;"

17. Lessons Learned

Lesson Detail

Check README first

The actual project structure is documented. Don’t assume DocumentRoot = repo root.

unix_socket auth

Great for CLI security, but web apps need password auth. Plan for both.

Let installer create DB

Pre-creating database causes "Already Exists" error. Let the wizard handle it.

sed is your friend

In-place edits with sed -i faster than vi for single changes.

SUPER privilege

Schema migrations often need elevated privileges for triggers/procedures.

Temporary users

Create high-privilege install user, remove after setup. Least privilege.

18. References