Spaces:

darkfire514
/

PostgreSQL_general

Running

App Files Files Community

PostgreSQL_general / README.md

darkfire514

Update README.md

dd72944 verified 6 days ago

preview code

Raw

History Blame Contribute Delete

12.9 kB

metadata

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

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:

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

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

2. Hugging Face Space setup

Create a new Hugging Face Space and choose:

SDK: Docker

The README front matter configures the Space:

---
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:

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:

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:

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

The bucket stores:

/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:

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

The homepage should show a login page first.

Useful routes:

/                  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:

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

7. Login-first dashboard workflow

Open:

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

You should first see the login page.

Admin user: admin
Password: your POSTGRES_PASSWORD

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

After login, the dashboard contains:

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

Recommended first-time usage:

Open the Space homepage.
Login with admin and POSTGRES_PASSWORD.
Open API Keys.
Enter a key name, such as rss_project.
Select scopes: read, write, or both.
Click Generate Key.
Copy the generated API Key and generated API links.
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:

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:

pgk_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

9. Admin authentication

Admin endpoints use PostgreSQL admin credentials.

Headers:

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

By default:

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:

Homepage → Login → API Keys → Generate Key

After generating a key, the page shows:

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:

POST /admin/api-keys

Example:

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:

{
  "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:

Homepage → Login → API Keys → Refresh

curl method:

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:

Homepage → Login → API Keys → Revoke

curl method:

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:

One-Click Backup

has actual backend functionality. It calls:

POST /admin/backups/run

The endpoint runs pg_dump and saves files to:

/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:

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

Authorization: Bearer your_API_KEY

Example:

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

URL style, convenient for simple tools

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

Example:

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:

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

Read-write key:

{
  "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:

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:

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

19. RSS article example

Create or update an RSS article:

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:

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:

POSTGRES_USER=admin
POSTGRES_PASSWORD=your_strong_password
POSTGRES_DB=appdb

Do not give the admin account to external projects.

Recommended model:

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

Example 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:

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:

/data/backups

Files:

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

Default interval:

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:

One-Click Backup

22. Local test

Build:

docker build -t postgresql-general-hf .

Run:

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

Open:

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:

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