portal-auth-dashboard/AUTH_LXC_PORTAL_AUTH_DATABASE.md

Auth LXC – Shared Portal Auth Database

Purpose

The Auth LXC is a dedicated container that runs PostgreSQL and hosts the shared auth database (portal_auth). It allows multiple portal applications (e.g. FreePBX Portal, future billing or other portals) to use the same user accounts without duplicating users or maintaining separate credentials per portal.

• One set of users: Create an account once; it works for every portal that points to this DB.
• Central auth data: Users, sessions, auth logs, and API tokens live here.
• Portal-specific data stays elsewhere: Each portal keeps its own database (e.g. extensions, companies, assignments) on its own host or on this LXC; only auth is shared.

---

Host Details

Item	Value
Host / LXC	Auth (DB) LXC
IP address	10.110.60.210
SSH access	ssh root@10.110.60.210
OS	Debian GNU/Linux 13 (trixie)
PostgreSQL	17 (Debian package)
Listen	All interfaces, port 5432

---

What This LXC Hosts

Database: portal_auth

This is the only database used for shared authentication. It contains four tables:

Table	Purpose
users	User accounts (username, password hash, role, active flag)
sessions	Active login sessions (session_id, username, expires_at)
auth_logs	Auth events (login, logout, failed login, account_locked, deactivated)
api_tokens	API tokens for programmatic access (token hash, username, expiry, active)

No other application data (extensions, companies, trunks, activity logs, etc.) is stored here; that remains in each portal’s own database.

---

Database Structure (portal_auth)

1. users

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Auto-incrementing user ID
username	VARCHAR(255)	UNIQUE, NOT NULL	Login username
password_hash	TEXT	NOT NULL	Bcrypt or legacy salt:hash
role	VARCHAR(50)	NOT NULL, DEFAULT 'support'	admin or support
created_at	TIMESTAMP	NOT NULL	Account creation (UTC)
created_by	VARCHAR(255)	NULL	Creator username
is_active	BOOLEAN	DEFAULT TRUE	Whether the account can log in

2. sessions

Column	Type	Constraints	Description
session_id	VARCHAR(255)	PRIMARY KEY	Session identifier (cookie value)
username	VARCHAR(255)	NOT NULL, FK → users(username)	Owner of the session
created_at	TIMESTAMP	NOT NULL	Session start (UTC)
expires_at	TIMESTAMP	NOT NULL	Session end (UTC)

Sessions are validated on each request; expired rows can be purged by the app (e.g. on startup).

3. auth_logs

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Log entry ID
username	VARCHAR(255)	NOT NULL	User involved
event_type	VARCHAR(50)	NOT NULL	login, logout, login_failed, deactivated, account_locked
timestamp	TIMESTAMP	NOT NULL	Event time (UTC)
ip_address	VARCHAR(45)	NULL	Client IP (IPv4/IPv6)
user_agent	TEXT	NULL	Browser/client string
details	TEXT	NULL	Extra info

Indexes: idx_auth_logs_username, idx_auth_logs_timestamp, idx_auth_logs_event_type

4. api_tokens

Column	Type	Constraints	Description
id	SERIAL	PRIMARY KEY	Token ID
token_hash	VARCHAR(255)	UNIQUE, NOT NULL	SHA-256 hash of the token
token_prefix	VARCHAR(20)	NOT NULL	First 20 chars (for display only)
username	VARCHAR(255)	NOT NULL, FK → users(username)	Token owner
description	TEXT	NULL	Optional label
created_at	TIMESTAMP	NOT NULL	Creation time (UTC)
created_by	VARCHAR(255)	NULL	Creator username
last_used_at	TIMESTAMP	NULL	Last validation time
expires_at	TIMESTAMP	NULL	Optional expiry
is_active	BOOLEAN	DEFAULT TRUE	Whether the token is valid

Indexes: idx_api_tokens_token_hash, idx_api_tokens_username, idx_api_tokens_is_active

Relationships

users (username)
  ├── sessions.username → users(username)
  └── api_tokens.username → users(username) ON DELETE CASCADE

auth_logs references users only by username (no FK); it is an append-only log.

---

Access and Security

• DB user: portal_user (password set on the server; same as used in portal .env for DB_AUTH_PASSWORD).
• Allowed clients (pg_hba.conf):
  • 10.110.60.0/24 – portal LXCs on the same subnet
  • 192.168.200.0/24 – gateway/subnet used by the production portal host to reach this LXC
• Authentication: scram-sha-256.
• Timezone: Connections use UTC.

Adding another portal: ensure its host IP (or subnet) is allowed in /etc/postgresql/17/main/pg_hba.conf for database portal_auth and user portal_user, then reload PostgreSQL.

---

How Portals Use This LXC

Each portal (e.g. FreePBX Portal) is configured with two DB targets:

1. Auth DB (this LXC): DB_AUTH_HOST=10.110.60.210, DB_AUTH_NAME=portal_auth, etc.
   Used for: login, sessions, auth logs, user management, API tokens.

2. Portal DB (local or elsewhere): DB_HOST, DB_NAME=freepbx_portal, etc.
   Used for: extensions, companies, assignments, activity logs, and all other app data.

In the portal .env:

# Shared auth (this LXC)
DB_AUTH_HOST=10.110.60.210
DB_AUTH_PORT=5432
DB_AUTH_NAME=portal_auth
DB_AUTH_USER=portal_user
DB_AUTH_PASSWORD=<same password as on Auth LXC>

# Portal’s own data (e.g. same host or another DB server)
DB_HOST=localhost
DB_NAME=freepbx_portal
DB_USER=portal_user
DB_PASSWORD=<password>

If DB_AUTH_HOST is not set, the portal runs in single-DB mode and uses only its own database for everything (no use of this LXC).

---

Verification

• Connections from a portal: On the Auth LXC, as postgres:

  SELECT count(*), client_addr
  FROM pg_stat_activity
  WHERE datname = 'portal_auth'
  GROUP BY client_addr;
  

  You should see connections from the portal host(s).

• Recent logins: After logging in via a portal:

  SELECT username, event_type, timestamp, ip_address
  FROM auth_logs
  ORDER BY timestamp DESC
  LIMIT 10;
  

• Active sessions:

  SELECT session_id, username, created_at, expires_at
  FROM sessions;
  

---

Maintenance

• Backups: Backup the portal_auth database (e.g. pg_dump -U postgres -d portal_auth) regularly; it holds all shared identities and sessions.
• Log retention: The portal app can purge old auth_logs rows (e.g. via AUTH_LOG_RETENTION_DAYS); expired sessions are removed on app startup or by scheduled jobs.
• PostgreSQL: Managed with systemd (postgresql.service); config under /etc/postgresql/17/main/ (postgresql.conf, pg_hba.conf).

---

Summary

What	Where
LXC	10.110.60.210 (Auth / DB LXC)
Use	Shared user accounts and auth for multiple portals
Database	portal_auth
Tables	users, sessions, auth_logs, api_tokens
Portal config	Set DB_AUTH_HOST=10.110.60.210 (and related DB_AUTH_*) in each portal’s .env

For full schema details of auth and portal tables, see DATABASE_SCHEMA.md.