README.md

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