--- 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: ```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. Login values: ```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 ```