Apppublic

darkfire514/PostgreSQL_general

sourceHugging Faceupdated 2mo agoView on Hugging Face

0likes

App README

PostgreSQL General HF

A lightweight PostgreSQL + visual admin dashboard + API service for Hugging Face Docker Spaces.

This project is designed for a practical no-command workflow:

text

Open Space homepage
  → Login with admin / POSTGRES_PASSWORD
  → Generate API Key visually
  → Copy ready-to-use API links
  → Use API links from external projects
  → Trigger one-click backup when needed

It includes:

—PostgreSQL 17
—FastAPI backend
—Login-first visual dashboard at /
—Internal API Key generation and management
—Multiple API Keys
—API Key hash storage, not plaintext storage
—read / write scopes
—One-click backup button
—Automatic PostgreSQL backup to /data/backups
—File index APIs
—RSS article demo APIs
—FastAPI docs at /docs

1. Files

text

README.md
Dockerfile
requirements.txt
api.py
start.sh
.gitignore

2. Hugging Face Space setup

Create a new Hugging Face Space and choose:

text

SDK: Docker

The README front matter configures the Space:

yaml

---
title: PostgreSQL General HF
emoji: 🐘
colorFrom: blue
colorTo: indigo
sdk: docker
app_port: 7860
pinned: false
---

3. Required Secret

In Hugging Face Space settings, add this Secret:

env

POSTGRES_PASSWORD=replace_with_a_strong_password

Do not commit the real password to GitHub.

4. Optional Variables

You can keep the defaults or configure these variables in Space settings:

env

POSTGRES_USER=admin
POSTGRES_DB=appdb
PGDATA=/home/user/pgdata
DATA_DIR=/data
BACKUP_DIR=/data/backups
USER_FILE_DIR=/data/files
EXPORT_DIR=/data/exports
GENERATED_DIR=/data/generated
BACKUP_INTERVAL_SECONDS=3600

API_TOKEN is not required. API Keys are generated inside the dashboard and stored in PostgreSQL as SHA-256 hashes.

5. Storage bucket mount

Recommended Hugging Face Storage Bucket configuration:

text

Mount path: /data
Access mode: Read & Write
Bucket visibility: Private

The bucket stores:

text

/data/backups      PostgreSQL SQL backups
/data/files        user-uploaded files
/data/exports      exported CSV / JSON / SQL files
/data/generated    generated images, videos, or other AI outputs

Do not set PGDATA=/data/postgres. PostgreSQL runtime data should stay on the Space local disk to avoid filesystem permission and locking issues.

6. Main URLs

After deployment, open the Space homepage:

text

https://your-space-name.hf.space/

The homepage should show a login page first.

Useful routes:

text

/                  Login page + web dashboard
/docs              FastAPI interactive docs
/api/health        Public health check
/admin/api-keys    API Key management endpoint
/admin/backups/run One-click backup endpoint
/api/db-health     PostgreSQL connection check
/api/files         File index API
/api/rss/articles  RSS article demo API

Example:

text

https://darkfire514-postgresql-general.hf.space/
https://darkfire514-postgresql-general.hf.space/docs

7. Login-first dashboard workflow

Open:

text

https://your-space-name.hf.space/

You should first see the login page.

text

Admin user: admin
Password: your POSTGRES_PASSWORD

If you changed POSTGRES_USER, use that value instead of admin.

After login, the dashboard contains:

text

Dashboard
API Keys
API Reference
Settings
FastAPI Docs
Logout
One-Click Backup

Recommended first-time usage:

1.Open the Space homepage.
2.Login with admin and POSTGRES_PASSWORD.
3.Open API Keys.
4.Enter a key name, such as rss_project.
5.Select scopes: read, write, or both.
6.Click Generate Key.
7.Copy the generated API Key and generated API links.
8.Save the API Key immediately because the full key is shown only once.

The admin password is stored only in the current browser's localStorage for convenience. It is not written to PostgreSQL by the dashboard.

8. API Key mechanism

API Keys are generated inside the app.

Security behavior:

text

1. The full API Key is shown only once.
2. PostgreSQL stores only the SHA-256 hash.
3. Existing keys show only their prefix.
4. Keys can be revoked from the dashboard.
5. Keys support read/write scopes.

Key format:

text

pgk_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

9. Admin authentication

Admin endpoints use PostgreSQL admin credentials.

Headers:

http

X-Admin-User: admin
X-Admin-Password: your_POSTGRES_PASSWORD

By default:

text

X-Admin-User = POSTGRES_USER = admin
X-Admin-Password = POSTGRES_PASSWORD

The dashboard sends these headers automatically after login.

10. Generate API Key by dashboard

Recommended method:

text

Homepage → Login → API Keys → Generate Key

After generating a key, the page shows:

text

1. Full API Key
2. /api/db-health link
3. /api/files link
4. /api/rss/articles link
5. Header-style curl example

11. Generate API Key by curl

Endpoint:

text

POST /admin/api-keys

Example:

bash

curl -X POST "https://your-space-name.hf.space/admin/api-keys" \
  -H "X-Admin-User: admin" \
  -H "X-Admin-Password: your_POSTGRES_PASSWORD" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "rss_project",
    "scopes": ["read", "write"]
  }'

Response example:

json

{
  "id": 1,
  "name": "rss_project",
  "api_key": "pgk_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx",
  "key_prefix": "pgk_xxxxxxxx",
  "scopes": ["read", "write"],
  "created_at": "2026-05-30T00:00:00",
  "warning": "This API key is shown only once. Save it now."
}

12. List API Keys

Dashboard method:

text

Homepage → Login → API Keys → Refresh

curl method:

bash

curl "https://your-space-name.hf.space/admin/api-keys" \
  -H "X-Admin-User: admin" \
  -H "X-Admin-Password: your_POSTGRES_PASSWORD"

Only the key prefix is returned, not the full API Key.

13. Revoke API Key

Dashboard method:

text

Homepage → Login → API Keys → Revoke

curl method:

bash

curl -X POST "https://your-space-name.hf.space/admin/api-keys/revoke" \
  -H "X-Admin-User: admin" \
  -H "X-Admin-Password: your_POSTGRES_PASSWORD" \
  -H "Content-Type: application/json" \
  -d '{
    "id": 1
  }'

14. One-Click Backup

The dashboard button:

text

One-Click Backup

has actual backend functionality. It calls:

text

POST /admin/backups/run

The endpoint runs pg_dump and saves files to:

text

/data/backups/backup_appdb_YYYYMMDD_HHMMSS.sql
/data/backups/latest.sql

The button requires a successful admin login because it uses the admin headers stored in the browser.

curl method:

bash

curl -X POST "https://your-space-name.hf.space/admin/backups/run" \
  -H "X-Admin-User: admin" \
  -H "X-Admin-Password: your_POSTGRES_PASSWORD"

15. API authentication styles

The API supports two styles.

Header style, recommended

http

Authorization: Bearer your_API_KEY

Example:

bash

curl "https://your-space-name.hf.space/api/db-health" \
  -H "Authorization: Bearer pgk_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"

URL style, convenient for simple tools

text

https://your-space-name.hf.space/api/db-health?api_key=your_API_KEY

Example:

text

https://your-space-name.hf.space/api/files?api_key=pgk_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

The dashboard automatically generates URL-style links for easy copying.

16. API Key scopes

Supported scopes:

Scope	Purpose
`read`	Read APIs
`write`	Write APIs

Examples:

Read-only key:

json

{
  "name": "readonly_dashboard",
  "scopes": ["read"]
}

Read-write key:

json

{
  "name": "rss_project",
  "scopes": ["read", "write"]
}

17. Available API endpoints

Public

Method	Path	Description
GET	`/`	Login page + web dashboard
GET	`/api/health`	Public health check
GET	`/docs`	FastAPI docs

Admin

Method	Path	Description
POST	`/admin/api-keys`	Generate API Key
GET	`/admin/api-keys`	List API Keys
POST	`/admin/api-keys/revoke`	Revoke API Key
POST	`/admin/backups/run`	One-click backup

API Key protected

Method	Path	Scope	Description
GET	`/api/db-health`	`read`	Check PostgreSQL connection
GET	`/api/db`	`read`	Single URL API gateway status
GET	`/api/files`	`read`	List file index records
POST	`/api/files`	`write`	Create file index record
GET	`/api/rss/articles`	`read`	List RSS article records
POST	`/api/rss/articles`	`write`	Create or update RSS article

18. File index example

Create a file index record:

bash

curl -X POST "https://your-space-name.hf.space/api/files" \
  -H "Authorization: Bearer your_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "user_id": "user_001",
    "filename": "image_001.png",
    "file_path": "/data/files/image_001.png",
    "file_size": 245891,
    "mime_type": "image/png"
  }'

List file index records:

text

https://your-space-name.hf.space/api/files?api_key=your_API_KEY

19. RSS article example

Create or update an RSS article:

bash

curl -X POST "https://your-space-name.hf.space/api/rss/articles" \
  -H "Authorization: Bearer your_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{
    "source": "example-rss",
    "title": "Example Article",
    "url": "https://example.com/article-1",
    "summary": "This is an example article.",
    "published_at": "2026-05-30T00:00:00"
  }'

List articles:

text

https://your-space-name.hf.space/api/rss/articles?api_key=your_API_KEY

20. PostgreSQL user account model

This project does not provide public PostgreSQL user registration.

PostgreSQL users are database roles. They must be created by the administrator through SQL or a management tool.

The default admin user is controlled by:

env

POSTGRES_USER=admin
POSTGRES_PASSWORD=your_strong_password
POSTGRES_DB=appdb

Do not give the admin account to external projects.

Recommended model:

text

One project = one database
One project = one database user
One project = one password

Example SQL:

sql

CREATE DATABASE rss_project;
CREATE USER rss_user WITH PASSWORD 'replace_with_real_strong_password';
GRANT ALL PRIVILEGES ON DATABASE rss_project TO rss_user;

Then connect to rss_project and grant schema privileges:

sql

GRANT ALL ON SCHEMA public TO rss_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO rss_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO rss_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON FUNCTIONS TO rss_user;

21. Automatic backup and restore

The startup script creates SQL backups using pg_dump.

Default backup directory:

text

/data/backups

Files:

text

/data/backups/latest.sql
/data/backups/backup_appdb_YYYYMMDD_HHMMSS.sql

Default interval:

text

BACKUP_INTERVAL_SECONDS=3600

If /home/user/pgdata is missing and /data/backups/latest.sql exists, the startup script attempts automatic restore.

You can also use the dashboard button:

text

One-Click Backup

22. Local test

Build:

bash

docker build -t postgresql-general-hf .

Run:

bash

docker run --rm -it \
  -p 7860:7860 \
  -p 5432:5432 \
  -e POSTGRES_PASSWORD=your_strong_password \
  -v $(pwd)/data:/data \
  postgresql-general-hf

Open:

text

http://localhost:7860/
http://localhost:7860/docs

23. Security notes

—Keep the Space private if possible.
—If the Space is public, use a strong POSTGRES_PASSWORD.
—The dashboard stores admin credentials only in the browser's localStorage after login.
—Full API Keys are shown only once.
—The database stores only API Key hashes.
—Do not expose PostgreSQL port 5432 publicly.
—Use API endpoints instead of giving external users direct database access.
—Put another layer such as Cloudflare Access in front of the Space for stronger protection.
—Back up important data outside the Space as well, for example to R2, S3, or another object storage service.

24. Notes about Adminer

This version is dashboard-first and API-first.

The root path / is the login page and visual dashboard.

Adminer dependencies are installed in the image, but Adminer is not exposed by default because Hugging Face Spaces usually expose one main app port.

If you want both Adminer and API under the same port later, add a reverse proxy such as Caddy or Nginx and route:

text

/          -> Login + Dashboard
/api       -> FastAPI
/docs      -> FastAPI Docs
/adminer   -> Adminer