--- title: PostgreSQL General HF emoji: 🐘 colorFrom: blue colorTo: indigo sdk: docker app_port: 7860 pinned: false --- # PostgreSQL General HF This repository deploys a lightweight PostgreSQL service on a Hugging Face Docker Space. It includes: - PostgreSQL 17 - FastAPI API service - Internal API Key generation and management - Automatic PostgreSQL backup to mounted storage - File index and RSS article demo APIs - Optional Adminer dependency installed for future/manual use The current public entrypoint is FastAPI on port `7860`. ```text Hugging Face Space ├── PostgreSQL runtime database files: /home/user/pgdata ├── Mounted bucket: /data │ ├── backups │ ├── files │ ├── exports │ └── generated └── FastAPI: 0.0.0.0:7860 ``` > Important: PostgreSQL runtime data is intentionally stored on the Space local disk, not directly on `/data`. The mounted bucket is used for backups and business files. --- ## 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 ``` This README contains the required Space metadata: ```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 application and stored in PostgreSQL as 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 local disk to avoid filesystem permission and locking issues. --- ## 6. API URLs After deployment, use: ```text https://your-space-name.hf.space https://your-space-name.hf.space/docs https://your-space-name.hf.space/api/health ``` For your current Space, the base URL is likely: ```text https://darkfire514-postgresql-general.hf.space ``` --- ## 7. Public health check No API key is required: ```bash curl "https://your-space-name.hf.space/api/health" ``` --- ## 8. Admin authentication Management endpoints use the 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 ``` --- ## 9. Generate an API Key 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." } ``` The full API key is shown only once. Save it immediately. The database stores only the SHA-256 hash. --- ## 10. List API Keys Endpoint: ```text GET /admin/api-keys ``` Example: ```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. --- ## 11. Revoke an API Key Endpoint: ```text POST /admin/api-keys/revoke ``` Example: ```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 }' ``` --- ## 12. Use an API Key All normal API calls use this header: ```http Authorization: Bearer your_API_KEY ``` Example: ```bash curl "https://your-space-name.hf.space/api/db-health" \ -H "Authorization: Bearer pgk_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" ``` --- ## 13. 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"] } ``` --- ## 14. Available API endpoints ### Public | Method | Path | Description | |---|---|---| | GET | `/` | Service info | | GET | `/api/health` | Public health check | ### 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 | ### API Key protected | Method | Path | Scope | Description | |---|---|---|---| | GET | `/api/db-health` | `read` | Check PostgreSQL connection | | 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 | Interactive API docs: ```text https://your-space-name.hf.space/docs ``` --- ## 15. 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: ```bash curl "https://your-space-name.hf.space/api/files" \ -H "Authorization: Bearer your_API_KEY" ``` --- ## 16. 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: ```bash curl "https://your-space-name.hf.space/api/rss/articles" \ -H "Authorization: Bearer your_API_KEY" ``` --- ## 17. PostgreSQL user account model This project does not provide public 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; ``` --- ## 18. 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. --- ## 19. 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/docs ``` --- ## 20. Security notes - Keep the Space private if possible. - If the Space is public, use a strong `POSTGRES_PASSWORD`. - 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. --- ## 21. Notes about Adminer Adminer dependencies are installed in the image, but the current deployment exposes FastAPI on port `7860`. That means: ```text /docs FastAPI documentation /api/... API service /admin/... API key management endpoints ``` Adminer is not exposed by default in this API-first version. If you want both Adminer and API under the same port later, add a reverse proxy such as Caddy or Nginx and route: ```text /api -> FastAPI /docs -> FastAPI /adminer -> Adminer ```