PostgreSQL Database Setup¶

Section: Database Configuration | Article 10
Audience: IT Administrators, Database Administrators
Last Updated: 2026-04-07

Overview¶

This article walks you through creating a PostgreSQL database and service accounts for RP-PAM. PostgreSQL is fully supported as an alternative to MSSQL and is the recommended choice for Linux-based and containerised deployments.

Complete this guide before running the Setup Wizard.

Prerequisites¶

Requirement	Details
PostgreSQL Version	PostgreSQL 14 or later
Access	A PostgreSQL superuser account (typically `postgres`) or a role with `CREATEDB` and `CREATEROLE` privileges
Network	The RP-PAM server must be able to reach PostgreSQL on port 5432 (or your custom port)
Tools	`psql` command-line client or a GUI tool such as pgAdmin

Database Sizing¶

Deployment Size	Users	Initial Size	Growth Estimate (1 Year)
Small	Up to 50	500 MB	2 - 5 GB
Medium	51 - 500	2 GB	10 - 25 GB
Large	501 - 5,000	5 GB	25 - 100 GB
Enterprise	5,000+	10 GB	100+ GB

Note: PostgreSQL manages storage dynamically. Unlike MSSQL, you do not need to pre-allocate data and log file sizes. However, ensure your disk has sufficient free space for the growth estimate above.

Step 1 — Create Service Accounts (Roles)¶

RP-PAM uses a three-role service account model. Create these roles before creating the database.

Using psql¶

Connect as the postgres superuser:

Bash:

sudo -u postgres psql

Or connect remotely:

psql -h your-pg-server.example.com -U postgres

Then execute:

-- Create roles with login and password
CREATE ROLE rppam_read  WITH LOGIN PASSWORD 'ReadP@ssw0rd!2026';
CREATE ROLE rppam_write WITH LOGIN PASSWORD 'WriteP@ssw0rd!2026';
CREATE ROLE rppam_audit WITH LOGIN PASSWORD 'AuditP@ssw0rd!2026';

PowerShell (using psql):

$env:PGPASSWORD = "your-postgres-password"
& psql -h your-pg-server.example.com -U postgres -c @"
CREATE ROLE rppam_read  WITH LOGIN PASSWORD 'ReadP@ssw0rd!2026';
CREATE ROLE rppam_write WITH LOGIN PASSWORD 'WriteP@ssw0rd!2026';
CREATE ROLE rppam_audit WITH LOGIN PASSWORD 'AuditP@ssw0rd!2026';
"@

Important: Replace the example passwords with strong, unique passwords. Store them securely.

Step 2 — Create the Database¶

Using psql¶

-- Create the database owned by the write role
CREATE DATABASE rppam
    OWNER = rppam_write
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

Bash (one-liner):

sudo -u postgres createdb -O rppam_write -E UTF8 --locale=en_US.UTF-8 -T template0 rppam

PowerShell:

$env:PGPASSWORD = "your-postgres-password"
& psql -h your-pg-server.example.com -U postgres -c "CREATE DATABASE rppam OWNER = rppam_write ENCODING = 'UTF8' TEMPLATE = template0;"

Step 3 — Configure Permissions¶

Connect to the rppam database and set up permissions:

Bash:

psql -h your-pg-server.example.com -U postgres -d rppam

Then execute:

-- Grant connect to all three roles
GRANT CONNECT ON DATABASE rppam TO rppam_read;
GRANT CONNECT ON DATABASE rppam TO rppam_write;
GRANT CONNECT ON DATABASE rppam TO rppam_audit;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO rppam_read;
GRANT USAGE ON SCHEMA public TO rppam_write;
GRANT USAGE ON SCHEMA public TO rppam_audit;

-- Write role: full access (owner already has this, but be explicit)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO rppam_write;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO rppam_write;

-- Read role: read-only
GRANT SELECT ON ALL TABLES IN SCHEMA public TO rppam_read;

-- Set default privileges so future tables get the same grants
ALTER DEFAULT PRIVILEGES FOR ROLE rppam_write IN SCHEMA public
    GRANT SELECT ON TABLES TO rppam_read;

ALTER DEFAULT PRIVILEGES FOR ROLE rppam_write IN SCHEMA public
    GRANT ALL ON TABLES TO rppam_write;

ALTER DEFAULT PRIVILEGES FOR ROLE rppam_write IN SCHEMA public
    GRANT ALL ON SEQUENCES TO rppam_write;

-- Audit role: insert-only on audit tables (run AFTER migrations)
-- GRANT INSERT ON audit_events TO rppam_audit;
-- GRANT INSERT ON audit_sessions TO rppam_audit;

Note: The audit role grants are commented out because the audit tables do not exist until after the setup wizard runs migrations. See the note in Step 5 below.

Step 4 — Verify Connectivity from the RP-PAM Server¶

Bash:

# Test TCP connectivity
nc -zv your-pg-server.example.com 5432

# Test authentication
psql -h your-pg-server.example.com -U rppam_write -d rppam -c "SELECT 1 AS connection_test;"

PowerShell:

# Test TCP connectivity
Test-NetConnection -ComputerName "your-pg-server.example.com" -Port 5432

# Test authentication
$env:PGPASSWORD = "WriteP@ssw0rd!2026"
& psql -h your-pg-server.example.com -U rppam_write -d rppam -c "SELECT 1 AS connection_test;"

Expected output:

 connection_test
-----------------
               1
(1 row)

Step 5 — Run Database Migrations¶

Migrations are applied automatically during the Setup Wizard (Step 5). You do not need to run them manually for a fresh installation.

After the setup wizard completes and creates the audit tables, come back and grant the audit role permissions:

Bash:

psql -h your-pg-server.example.com -U postgres -d rppam -c "
GRANT INSERT ON audit_events TO rppam_audit;
GRANT INSERT ON audit_sessions TO rppam_audit;
GRANT SELECT ON audit_events TO rppam_read;
GRANT SELECT ON audit_sessions TO rppam_read;
"

PowerShell:

$env:PGPASSWORD = "your-postgres-password"
& psql -h your-pg-server.example.com -U postgres -d rppam -c @"
GRANT INSERT ON audit_events TO rppam_audit;
GRANT INSERT ON audit_sessions TO rppam_audit;
GRANT SELECT ON audit_events TO rppam_read;
GRANT SELECT ON audit_sessions TO rppam_read;
"@

Manual Migration (Upgrades Only)¶

Windows (PowerShell):

& "C:\Program Files\Ravenphyre\RP-PAM\rppam.exe" migrate --apply

Linux (Bash):

sudo /opt/rppam/rppam migrate --apply

Docker:

docker exec -it rppam /opt/rppam/rppam migrate --apply

pgvector for AI Features¶

If your license includes AI features (Enterprise or MSP tier), RP-PAM uses vector embeddings for its RAG (Retrieval-Augmented Generation) knowledge base. PostgreSQL requires the pgvector extension to store and query these embeddings.

Install pgvector¶

Ubuntu/Debian:

sudo apt install -y postgresql-14-pgvector
# Replace "14" with your PostgreSQL major version

RHEL/CentOS:

sudo dnf install -y pgvector_14

Docker (official PostgreSQL image with pgvector):

Use the pgvector/pgvector image instead of the standard postgres image:

docker pull pgvector/pgvector:pg16

Enable the Extension¶

Connect as the postgres superuser and enable the extension in the rppam database:

\c rppam
CREATE EXTENSION IF NOT EXISTS vector;

Bash (one-liner):

psql -h your-pg-server.example.com -U postgres -d rppam -c "CREATE EXTENSION IF NOT EXISTS vector;"

Note: If you do not plan to use AI features, you can skip pgvector. RP-PAM will function normally without it; only the AI assistant and RAG features will be unavailable.

Configuring pg_hba.conf¶

Ensure the RP-PAM server can authenticate. Add the following line to pg_hba.conf (adjust the IP address to match your RP-PAM server):

# TYPE  DATABASE  USER         ADDRESS              METHOD
host    rppam     rppam_read   10.0.1.0/24          scram-sha-256
host    rppam     rppam_write  10.0.1.0/24          scram-sha-256
host    rppam     rppam_audit  10.0.1.0/24          scram-sha-256

After editing, reload the configuration:

sudo systemctl reload postgresql

Troubleshooting¶

Symptom	Cause	Resolution
Connection refused on port 5432	PostgreSQL not listening on network interface	Set `listen_addresses = '*'` in `postgresql.conf` and restart
"password authentication failed"	Wrong password or `pg_hba.conf` not updated	Verify password; add the RP-PAM server IP to `pg_hba.conf`
"database does not exist"	Database not created or wrong name	Run `\l` in psql to list databases
"permission denied for table"	Missing grants	Re-run the GRANT statements from Step 3
"extension vector is not available"	pgvector not installed	Install the pgvector package for your PostgreSQL version
Migration fails	Write role lacks DDL permissions	Verify `rppam_write` is the database owner

Next Steps¶

Setup Wizard — Run the setup wizard with your PostgreSQL connection details
Connection String Encryption — Encrypt database credentials in the configuration file
Backup and Restore — Configure PostgreSQL backups

Setting	Recommended Value	Why
`shared_buffers`	25% of server RAM	Primary memory cache for PostgreSQL
`effective_cache_size`	50-75% of server RAM	Helps the query planner estimate available cache
`work_mem`	64 MB	Memory for sort and hash operations
`maintenance_work_mem`	256 MB	Memory for maintenance tasks (VACUUM, CREATE INDEX)
`max_connections`	100 (small) / 300 (large)	Maximum concurrent connections
`ssl`	`on`	Encrypts data in transit
`wal_level`	`replica`	Enables point-in-time recovery
`log_statement`	`ddl`	Logs schema changes for audit purposes

PostgreSQL Database Setup¶

Overview¶

Prerequisites¶

Database Sizing¶

Step 1 — Create Service Accounts (Roles)¶

Using psql¶

Step 2 — Create the Database¶

Using psql¶

Step 3 — Configure Permissions¶

Step 4 — Verify Connectivity from the RP-PAM Server¶

Step 5 — Run Database Migrations¶

Manual Migration (Upgrades Only)¶

pgvector for AI Features¶

Install pgvector¶

Enable the Extension¶

Recommended PostgreSQL Settings¶

Configuring pg_hba.conf¶

Troubleshooting¶

Next Steps¶