PostgreSQL¶

Server-level PostgreSQL management when the plugin is enabled.

All endpoints below are mounted under /api. Authentication: Authorization: Bearer <token> unless noted as public.

Endpoint count: 16

Endpoints¶

Method	Path	Handler	Guards
`POST`	`/api/postgresql/backup`	`postgreSQLBackup`	RequirePermission(db, "administrative_operations")
`GET`	`/api/postgresql/backups`	`postgreSQLListBackups`	RequirePermission(db, "administrative_operations")
`DELETE`	`/api/postgresql/backups/:name`	`postgreSQLDeleteBackup`	RequirePermission(db, "administrative_operations")
`GET`	`/api/postgresql/pgadmin`	`getPgAdminStatus`	RequirePermission(db, "administrative_operations")
`GET`	`/api/postgresql/pgadmin/credentials`	`getPgAdminCredentials`	RequirePermission(db, "administrative_operations")
`POST`	`/api/postgresql/pgadmin/install`	`installPgAdmin`	RequirePermission(db, "administrative_operations")
`POST`	`/api/postgresql/pgadmin/uninstall`	`uninstallPgAdmin`	RequirePermission(db, "administrative_operations")
`POST`	`/api/postgresql/pgadmin/update`	`updatePgAdmin`	RequirePermission(db, "administrative_operations")
`POST`	`/api/postgresql/restart`	`postgreSQLRestart`	RequirePermission(db, "administrative_operations")
`POST`	`/api/postgresql/restore`	`postgreSQLRestore`	RequirePermission(db, "administrative_operations")
`POST`	`/api/postgresql/start`	`postgreSQLStart`	RequirePermission(db, "administrative_operations")
`GET`	`/api/postgresql/status`	`getPostgreSQLStatus`	RequirePermission(db, "administrative_operations")
`POST`	`/api/postgresql/stop`	`postgreSQLStop`	RequirePermission(db, "administrative_operations")
`GET`	`/api/postgresql/users`	`postgreSQLListUsers`	RequirePermission(db, "administrative_operations")
`POST`	`/api/postgresql/users`	`postgreSQLCreateUser`	RequirePermission(db, "administrative_operations")
`DELETE`	`/api/postgresql/users/:username`	`postgreSQLDeleteUser`	RequirePermission(db, "administrative_operations")

Source¶

Handlers for this section live in api/routes/handlers_*.go in the StarkOne source. Each handler binds its request body with gin.ShouldBindJSON(&req) and responds with the standard envelope:

{ "data": { ... } }

On error:

{ "error": { "code": "string", "message": "string" } }

Reference¶

`POST /api/postgresql/backup`¶

Creates a full SQL dump of all PostgreSQL roles and databases via pg_dumpall and writes it to /var/backups/postgresql.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true, "backup": "pg-backup-2026-06-17-153000.sql" }

Errors

Status	Code	Meaning
`500`	`backup_error`	`pg_dumpall` failed; message includes the command error and output.

Side effects

Creates the directory /var/backups/postgresql (mode 0755) if missing and chowns it to postgres:postgres.
Runs sudo -u postgres pg_dumpall -f <path>, writing a timestamped file pg-backup-YYYY-MM-DD-HHMMSS.sql.

Example

curl -X POST https://panel.example.com:2087/api/postgresql/backup \
  -H "Authorization: Bearer $TOKEN"

`GET /api/postgresql/backups`¶

Lists the most recent PostgreSQL backup files (up to 10) found in /var/backups/postgresql.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

Returns the envelope. Only files whose name begins with pg-backup- are included, sorted newest-first by name, capped at 10. If the backup directory cannot be read, an empty list is returned with 200.

{
  "data": {
    "backups": [
      { "name": "pg-backup-2026-06-17-153000.sql", "date": "2026-06-17 15:30", "size": "2.4 MB" }
    ]
  }
}

Example

curl https://panel.example.com:2087/api/postgresql/backups \
  -H "Authorization: Bearer $TOKEN"

`DELETE /api/postgresql/backups/:name`¶

Deletes a single backup file by name from /var/backups/postgresql.

Guards: RequirePermission(db, "administrative_operations")

Path parameters

Name	Type	Description
`name`	string	Backup file name. Rejected if it contains `/`, `\`, or `..`.

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true }

Errors

Status	Code	Meaning
`400`	`bad_request`	Name contains a path separator or `..`.
`404`	`not_found`	Backup file does not exist.
`500`	`delete_error`	Removing the file failed.

Side effects

Removes the file at /var/backups/postgresql/<name> via os.RemoveAll.

Example

curl -X DELETE https://panel.example.com:2087/api/postgresql/backups/pg-backup-2026-06-17-153000.sql \
  -H "Authorization: Bearer $TOKEN"

`GET /api/postgresql/pgadmin`¶

Reports whether pgAdmin 4 is installed and running, its version, and the panel-relative URL to reach it through the panel's reverse proxy.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

Returns the envelope. installed is true when the venv pip exists; version comes from pip show pgadmin4; running reflects systemctl is-active pgadmin4; url is built from the request scheme/host plus the proxy prefix.

{
  "data": {
    "installed": true,
    "running": true,
    "version": "8.5",
    "url": "https://panel.example.com:2087/api/postgresql/pgadmin/proxy/"
  }
}

Example

curl https://panel.example.com:2087/api/postgresql/pgadmin \
  -H "Authorization: Bearer $TOKEN"

`GET /api/postgresql/pgadmin/credentials`¶

Returns the stored pgAdmin login email and password read from /etc/starkone/.pgadmin.conf.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

Returns the envelope wrapping the persisted credentials struct.

{ "data": { "email": "[email protected]", "password": "..." } }

Errors

Status	Code	Meaning
`404`	`not_installed`	Credentials file not found (pgAdmin not installed).
`500`	`corrupt_config`	Credentials file could not be parsed as JSON.

Example

curl https://panel.example.com:2087/api/postgresql/pgadmin/credentials \
  -H "Authorization: Bearer $TOKEN"

`POST /api/postgresql/pgadmin/install`¶

Installs pgAdmin 4 into a Python venv at /opt/pgadmin4/venv, generates credentials, writes its config and a systemd unit, and starts the service.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true, "message": "pgAdmin installed", "email": "[email protected]" }

Errors

Status	Code	Meaning
`500`	`prereq_error`	Installing OS prerequisites failed.
`500`	`mkdir_error`	Creating install/data/log/config directories failed.
`500`	`venv_error`	Creating the Python venv failed.
`500`	`pip_upgrade_error`	Upgrading pip/wheel/setuptools failed.
`500`	`pip_install_error`	`pip install pgadmin4` failed.
`500`	`creds_error`	Loading or generating credentials failed.
`500`	`config_error`	Writing the pgAdmin config failed.
`500`	`setup_error`	Running pgAdmin's setup (user creation) failed.
`500`	`systemd_error`	Writing the systemd unit failed.
`500`	`service_start_error`	`systemctl enable --now pgadmin4` failed.

Side effects

Installs OS prerequisites.
Creates /opt/pgadmin4, /var/lib/pgadmin, /var/log/pgadmin, /etc/starkone.
Builds a venv and pip installs pgadmin4.
Writes credentials, config, and /etc/starkone/pgadmin-servers.json.
Writes /etc/systemd/system/pgadmin4.service, runs systemctl daemon-reload, then systemctl enable --now pgadmin4.

Example

curl -X POST https://panel.example.com:2087/api/postgresql/pgadmin/install \
  -H "Authorization: Bearer $TOKEN"

`POST /api/postgresql/pgadmin/uninstall`¶

Stops and removes the pgAdmin service and deletes all pgAdmin install, data, log, and config artifacts (including leftover nginx/apache configs from older versions).

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true, "message": "pgAdmin uninstalled" }

Side effects

systemctl disable --now pgadmin4 and removes /etc/systemd/system/pgadmin4.service, then systemctl daemon-reload.
Removes stale web-server fragments: /etc/nginx/conf.d/00-pgadmin.conf, /etc/starkone/.pgadmin_alias, /etc/apache2/conf-available/pgadmin.conf (with a2disconf pgadmin), /etc/httpd/conf.d/pgadmin.conf, then systemctl reload nginx.
Deletes /opt/pgadmin4, /var/lib/pgadmin, /var/log/pgadmin, the credentials file, and the servers JSON.

Example

curl -X POST https://panel.example.com:2087/api/postgresql/pgadmin/uninstall \
  -H "Authorization: Bearer $TOKEN"

`POST /api/postgresql/pgadmin/update`¶

Upgrades the installed pgAdmin 4 package via pip and restarts the service, reporting the old and new versions.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true, "old_version": "8.4", "new_version": "8.5" }

Errors

Status	Code	Meaning
`400`	`not_installed`	The venv `pip` does not exist (pgAdmin not installed).
`500`	`pip_update_error`	`pip install --upgrade pgadmin4` failed.

Side effects

Runs pip install --upgrade pgadmin4 in the venv.
Runs systemctl restart pgadmin4.

Example

curl -X POST https://panel.example.com:2087/api/postgresql/pgadmin/update \
  -H "Authorization: Bearer $TOKEN"

`POST /api/postgresql/restart`¶

Restarts the PostgreSQL system service.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true, "message": "PostgreSQL restarted" }

Errors

Status	Code	Meaning
`500`	`restart_error`	`systemctl restart` of the PostgreSQL service failed.

Side effects

Runs systemctl restart <pg-service> (service name resolved at runtime).

Example

curl -X POST https://panel.example.com:2087/api/postgresql/restart \
  -H "Authorization: Bearer $TOKEN"

`POST /api/postgresql/restore`¶

Restores a previously created backup by feeding its SQL dump into psql, recreating roles and databases.

Guards: RequirePermission(db, "administrative_operations")

Request body

{ "backup": "string (required)" }

Field	Type	Required	Description
`backup`	string	yes	Backup file name in `/var/backups/postgresql`. Rejected if it contains `/`, `\`, or `..`.

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true, "message": "Restore completed" }

Errors

Status	Code	Meaning
`400`	`bad_request`	Body failed to bind, or the backup name contains a path separator or `..`.
`404`	`not_found`	The named backup file does not exist.
`500`	`restore_error`	`psql -f <path>` failed; message includes the error and output.

Side effects

Runs sudo -u postgres psql -f <backupPath> postgres, which recreates roles and databases from the dump.

Example

curl -X POST https://panel.example.com:2087/api/postgresql/restore \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"backup": "pg-backup-2026-06-17-153000.sql"}'

`POST /api/postgresql/start`¶

Starts the PostgreSQL system service.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true, "message": "PostgreSQL started" }

Errors

Status	Code	Meaning
`500`	`start_error`	`systemctl start` of the PostgreSQL service failed.

Side effects

Runs systemctl start <pg-service>.

Example

curl -X POST https://panel.example.com:2087/api/postgresql/start \
  -H "Authorization: Bearer $TOKEN"

`GET /api/postgresql/status`¶

Returns PostgreSQL runtime status: running state, version, uptime, connection count, memory usage, and total data size.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

Returns the envelope. version comes from psql --version (available even when stopped). When running, connections, uptime, and data_size are queried live; memory_mb is read from systemctl show ... MemoryCurrent. If the SQL data-size query fails, data_size falls back to du -sh of the data directory.

{
  "data": {
    "running": true,
    "version": "15.4",
    "uptime": "12h 5m",
    "connections": 7,
    "memory_mb": 128,
    "data_size": "2.4 GB"
  }
}

Example

curl https://panel.example.com:2087/api/postgresql/status \
  -H "Authorization: Bearer $TOKEN"

`POST /api/postgresql/stop`¶

Stops the PostgreSQL system service.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true, "message": "PostgreSQL stopped" }

Errors

Status	Code	Meaning
`500`	`stop_error`	`systemctl stop` of the PostgreSQL service failed.

Side effects

Runs systemctl stop <pg-service>.

Example

curl -X POST https://panel.example.com:2087/api/postgresql/stop \
  -H "Authorization: Bearer $TOKEN"

`GET /api/postgresql/users`¶

Lists PostgreSQL roles (excluding internal pg_* roles) with their role attributes.

Guards: RequirePermission(db, "administrative_operations")

Response - 200 OK

Returns the envelope. Each entry's roles is built from the role flags (SUPERUSER, CREATEDB, CREATEROLE, LOGIN). The db field is always an empty string. If the query fails, an empty list is returned with 200.

{
  "data": {
    "users": [
      { "user": "user1", "db": "", "roles": ["LOGIN", "CREATEDB"] }
    ]
  }
}

Example

curl https://panel.example.com:2087/api/postgresql/users \
  -H "Authorization: Bearer $TOKEN"

`POST /api/postgresql/users`¶

Creates a PostgreSQL role with the requested privilege level and, optionally, creates a database owned by it or grants it privileges on an existing database.

Guards: RequirePermission(db, "administrative_operations")

Request body

{
  "username": "string (required)",
  "password": "string (required)",
  "database": "string (optional)",
  "role": "string (optional)"
}

Field	Type	Required	Description
`username`	string	yes	Role name. Must be letters, digits, or underscore, max 63 chars.
`password`	string	yes	Role login password.
`database`	string	no	If set, a database to create (owned by the user) or grant privileges on. Must pass the same name validation.
`role`	string	no	Privilege level. One of `superuser`, `createdb`, `readonly`, `login`, `readwrite`. Defaults to `login`.

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true }

Errors

Status	Code	Meaning
`400`	`bad_request`	Body failed to bind, or the username/database failed name validation.
`500`	`create_error`	Creating the role failed, or the user was created but the database create/grant failed.

Side effects

Runs CREATE ROLE <username> WITH <attrs> PASSWORD <pw>. Role attributes depend on role: superuser -> LOGIN SUPERUSER CREATEDB CREATEROLE; createdb -> LOGIN CREATEDB; others -> LOGIN.
If database is set and does not exist: CREATE DATABASE <db> OWNER <username>.
If database exists: grants CONNECT (when role is readonly) or ALL PRIVILEGES to the user.

Example

curl -X POST https://panel.example.com:2087/api/postgresql/users \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"username": "user1", "password": "s3cret", "database": "appdb", "role": "createdb"}'

`DELETE /api/postgresql/users/:username`¶

Drops a PostgreSQL role after reassigning and dropping the objects it owns.

Guards: RequirePermission(db, "administrative_operations")

Path parameters

Name	Type	Description
`username`	string	Role name to drop. Must be letters, digits, or underscore, max 63 chars.

Response - 200 OK

This handler returns a flat (non-enveloped) body:

{ "success": true }

Errors

Status	Code	Meaning
`400`	`bad_request`	Username failed name validation.
`500`	`delete_error`	The `DROP ROLE` statement failed.

Side effects

Runs REASSIGN OWNED BY <username> TO postgres, then DROP OWNED BY <username>, then DROP ROLE IF EXISTS <username>.

Example

curl -X DELETE https://panel.example.com:2087/api/postgresql/users/user1 \
  -H "Authorization: Bearer $TOKEN"

PostgreSQL¶

Endpoints¶

Source¶

Reference¶

POST /api/postgresql/backup¶

GET /api/postgresql/backups¶

DELETE /api/postgresql/backups/:name¶

GET /api/postgresql/pgadmin¶

GET /api/postgresql/pgadmin/credentials¶

POST /api/postgresql/pgadmin/install¶

POST /api/postgresql/pgadmin/uninstall¶

POST /api/postgresql/pgadmin/update¶

POST /api/postgresql/restart¶

POST /api/postgresql/restore¶

POST /api/postgresql/start¶

GET /api/postgresql/status¶

POST /api/postgresql/stop¶

GET /api/postgresql/users¶

POST /api/postgresql/users¶

DELETE /api/postgresql/users/:username¶

`POST /api/postgresql/backup`¶

`GET /api/postgresql/backups`¶

`DELETE /api/postgresql/backups/:name`¶

`GET /api/postgresql/pgadmin`¶

`GET /api/postgresql/pgadmin/credentials`¶

`POST /api/postgresql/pgadmin/install`¶

`POST /api/postgresql/pgadmin/uninstall`¶

`POST /api/postgresql/pgadmin/update`¶

`POST /api/postgresql/restart`¶

`POST /api/postgresql/restore`¶

`POST /api/postgresql/start`¶

`GET /api/postgresql/status`¶

`POST /api/postgresql/stop`¶

`GET /api/postgresql/users`¶

`POST /api/postgresql/users`¶

`DELETE /api/postgresql/users/:username`¶