PostgreSQL Plugin¶

Overview¶

The PostgreSQL plugin provides full PostgreSQL database management and optionally installs pgAdmin 4 for a web-based GUI.

Plugin ID: postgresql

Enable the Plugin¶

POST /api/plugins/postgresql/enable

Service Control¶

GET /api/postgresql/status
POST /api/postgresql/start
POST /api/postgresql/stop
POST /api/postgresql/restart

Backups¶

GET /api/postgresql/backups
POST /api/postgresql/backup
POST /api/postgresql/restore
{ "name": "backup-2024-01-15.sql.gz" }
DELETE /api/postgresql/backups/:name

Global Users¶

GET /api/postgresql/users
POST /api/postgresql/users
{ "username": "app_user", "password": "secret" }
DELETE /api/postgresql/users/:username

Per-Website Databases¶

# List databases for a website
GET /api/webapps/:id/databases/pg

# List by username
GET /api/databases/pg?username=example

# Create
POST /api/databases/pg
{
  "username": "example",
  "db_name": "example_app"
}

# Delete
DELETE /api/databases/pg/:id

# Check name availability
GET /api/databases/pg/check-name?name=example_app

# Import
POST /api/databases/pg/:id/import
Content-Type: multipart/form-data

# Export
POST /api/databases/pg/:id/export
GET /api/databases/pg/:id/download

# Create DB user
POST /api/databases/pg/:id/users
{
  "username": "app_reader",
  "password": "secret",
  "privileges": "SELECT"
}

# Delete DB user
DELETE /api/databases/pg/:id/users/:username

# Reset password
POST /api/databases/pg/:id/users/:username/reset-password
{ "password": "new_secret" }

Unassociated Databases (Admin)¶

GET /api/databases/pg/admin/unassociated
POST /api/databases/pg/admin/associate
{
  "db_name": "orphan_db",
  "website_id": 42
}

pgAdmin 4¶

pgAdmin is installed via the panel and proxied through the panel's own HTTPS listener:

# Status
GET /api/postgresql/pgadmin

# Credentials
GET /api/postgresql/pgadmin/credentials

# Install
POST /api/postgresql/pgadmin/install

# Update
POST /api/postgresql/pgadmin/update

# Uninstall
POST /api/postgresql/pgadmin/uninstall

# Proxy (web UI - outside auth group)
ANY /api/postgresql/pgadmin/proxy/*path

pgAdmin Proxy Authentication

The pgAdmin proxy uses IP-bound session authentication instead of standard JWT, because pgAdmin's AJAX layer does not reliably forward cookies. Session tokens are bound to the connecting IP and expire after 4 hours.

Data Location¶

PostgreSQL data directory: /var/lib/postgresql/ (Ubuntu) or /var/lib/pgsql/ (RHEL)

Backups: configurable, default /etc/starkone/pg-backups/