README.md

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