Spaces:

darkfire514
/

PostgreSQL_general

Running

App Files Files Community

PostgreSQL_general / README.md

darkfire514

Update README.md

42cfec1 verified 28 days ago

preview code

Raw

History Blame Contribute Delete

9.91 kB

metadata

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.

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

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

This README contains the required Space metadata:

---
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 application and stored in PostgreSQL as 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 local disk to avoid filesystem permission and locking issues.

6. API URLs

After deployment, use:

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:

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

7. Public health check

No API key is required:

curl "https://your-space-name.hf.space/api/health"

8. Admin authentication

Management endpoints use the 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

9. Generate an API Key

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."
}

The full API key is shown only once. Save it immediately. The database stores only the SHA-256 hash.

10. List API Keys

Endpoint:

GET /admin/api-keys

Example:

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:

POST /admin/api-keys/revoke

Example:

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:

Authorization: Bearer your_API_KEY

Example:

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:

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

Read-write key:

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

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

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

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:

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:

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:

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;

18. 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.

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

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

/api      -> FastAPI
/docs     -> FastAPI
/adminer  -> Adminer