Pangolin Database Commands 📊

29/01/2025 networking 3 mins read

Table Of Contents

Single-line SQLite commands that you can directly use in your terminal. Each command is designed for a specific administrative task:
Install sqlite3 to excuate commands
Back up your SQLite database. This is an important practice for data safety. The command will create a backup file with the current date and time in its name.
For User Management:

Single-line SQLite commands that you can directly use in your terminal. Each command is designed for a specific administrative task: #

Install sqlite3 to excuate commands

apt install sqlite3

Back up your SQLite database. This is an important practice for data safety. The command will create a backup file with the current date and time in its name.

Here’s the command:

sqlite3 /path/to/db.sqlite ".backup '/path/to/backups/db_$(date +%Y%m%d_%H%M%S).sqlite'"

If you want to also compress the backup to save space, you can extend the command:

sqlite3 /path/to/db.sqlite ".backup '/path/to/backups/db_$(date +%Y%m%d_%H%M%S).sqlite'" && gzip "/path/to/backups/db_$(date +%Y%m%d_%H%M%S).sqlite"

This will create a compressed .gz file of your backup.

For User Management:

#### List all users and their roles
sqlite3 /path/to/db.sqlite "SELECT u.email, o.name as organization, r.name as role FROM user u LEFT JOIN userOrgs uo ON u.id = uo.userId LEFT JOIN orgs o ON uo.orgId = o.orgId LEFT JOIN roles r ON uo.roleId = r.roleId;"


#### Enable two-factor auth for a user
sqlite3 /path/to/db.sqlite "UPDATE user SET twoFactorEnabled = 1 WHERE email = '[email protected]';"

For Site Management:

#### View all sites and their status
sqlite3 /path/to/db.sqlite "SELECT name, online, subnet FROM sites;"

#### Check site bandwidth usage
sqlite3 /path/to/db.sqlite "SELECT name, bytesIn/1024/1024 as MB_in, bytesOut/1024/1024 as MB_out FROM sites;"

For Resource Management:

#### List all resources and their domains
sqlite3 /path/to/db.sqlite "SELECT name, fullDomain, ssl FROM resources;"

#### Check resource access settings
sqlite3 /path/to/db.sqlite "SELECT name, blockAccess, sso, emailWhitelistEnabled FROM resources;"

For Role and Permission Management:

#### List all roles and their actions
sqlite3 /path/to/db.sqlite "SELECT r.name, a.actionId FROM roles r JOIN roleActions ra ON r.roleId = ra.roleId JOIN actions a ON ra.actionId = a.actionId;"

#### Show all admin users
sqlite3 /path/to/db.sqlite "SELECT u.email FROM user u JOIN userOrgs uo ON u.id = uo.userId WHERE uo.isOwner = 1;"

For Security Auditing:

#### List users with 2FA status
sqlite3 /path/to/db.sqlite "SELECT email, twoFactorEnabled, emailVerified FROM user;"

#### Show active sessions
sqlite3 /path/to/db.sqlite "SELECT s.id, u.email, s.expiresAt FROM session s JOIN user u ON s.userId = u.id;"

For Organization Management:

#### List all organizations and their domains
sqlite3 /path/to/db.sqlite "SELECT orgId, name, domain FROM orgs;"

#### Show organization members
sqlite3 /path/to/db.sqlite "SELECT o.name, u.email FROM orgs o JOIN userOrgs uo ON o.orgId = uo.orgId JOIN user u ON uo.userId = u.id;"

Each of these commands can be directly copied and pasted into your terminal. Remember to:

Replace /path/to/db.sqlite with your actual database path
Replace placeholder values like ‘[email protected]’ with actual values
Add quotes around values when dealing with strings