Benefits and Use Cases for BlueGreenCity-KI
This document provides an overview of PostgREST, its benefits, and potential integration with the BlueGreenCity-KI system.
PostgREST is a standalone web server that turns your PostgreSQL database directly into a RESTful API. It provides:
Example Query:
GET /trees?district=eq.Mitte&vitality=lt.4&select=gisid,pflanzjahr,vitalitaet
PostgREST can significantly enhance BlueGreenCity-KI’s data access and sharing capabilities:
Current Situation: Data is accessed primarily through Python scripts or QGIS connections.
With PostgREST:
Example Endpoints:
GET /trees.inventory_current?select=gisid,art,vitalitaet,geom&limit=100
GET /analysis.tree_risk_scores?overall_drought_risk=gte.0.7
Create dashboards and web applications that display:
Benefit: Data can be visualized in tools like Grafana, Power BI, or custom web dashboards without additional backend development.
Enable field workers to:
Benefit: Direct database updates with proper authentication and validation via PostgreSQL row-level security.
Share tree inventory and risk data with:
Benefit: Standardized, documented API with controlled access via JWT tokens.
Enable event-driven integrations:
┌─────────────────────────────────────────────────────┐
│ BlueGreenCity-KI Python Pipeline │
│ │
│ ┌─────────────────────────────────────────────┐ │
│ │ Risk Assessment Pipeline │ │
│ │ - Load data from PostGIS │ │
│ │ - Execute criteria modules │ │
│ │ - Write results to PostGIS │ │
│ └─────────────────────────────────────────────┘ │
│ ↕ │
│ ┌─────────────────────────────────────────────┐ │
│ │ GeoAlchemy2 / psycopg2 │ │
│ └─────────────────────────────────────────────┘ │
└────────────────────────┬────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────┐
│ PostGIS Database │
│ - trees.inventory_current │
│ - context.osm_highways │
│ - analysis.tree_risk_scores │
└─────────────────────────────────────────────────────┘
│
▼
Direct SQL / QGIS
┌─────────────────────────────────────────────────────┐
│ BlueGreenCity-KI Python Pipeline │
│ (unchanged – continues to use GeoAlchemy2) │
└────────────────────────┬────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────┐
│ PostGIS Database │
│ - trees.inventory_current │
│ - context.osm_highways │
│ - analysis.tree_risk_scores │
│ - api (schema for PostgREST views/functions) │
└───────────┬─────────────────────────┬───────────────┘
│ │
▼ ▼
┌───────────────────────┐ ┌─────────────────────────┐
│ PostgREST │ │ Direct SQL / QGIS │
│ REST API Server │ │ (unchanged) │
│ Port: 3000 │ │ │
└───────────┬───────────┘ └─────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────┐
│ External Clients │
│ - Web Dashboards │
│ - Mobile Apps │
│ - Third-Party Systems │
│ - City Departments │
└─────────────────────────────────────────────────────┘
Key Point: PostgREST is additive – it does not replace existing data access methods but provides an additional interface for external consumers.
PostgREST works seamlessly with PostGIS for geospatial data:
Return geometry as GeoJSON directly from PostgREST:
-- Create a view that returns GeoJSON
CREATE OR REPLACE VIEW api.trees_geojson AS
SELECT
gisid,
art AS species,
vitalitaet AS vitality,
ST_AsGeoJSON(geom)::json AS geometry
FROM trees.inventory_current;
API Response:
{
"gisid": 12345,
"species": "Tilia cordata",
"vitality": 2,
"geometry": {
"type": "Point",
"coordinates": [9.732, 52.374]
}
}
Create functions for common spatial operations:
-- Trees within a bounding box
CREATE OR REPLACE FUNCTION api.trees_in_bbox(
min_x double precision,
min_y double precision,
max_x double precision,
max_y double precision
)
RETURNS TABLE (
gisid integer,
species text,
vitality integer,
geometry json
) AS $$
SELECT
t.gisid,
t.art AS species,
t.vitalitaet AS vitality,
ST_AsGeoJSON(t.geom)::json AS geometry
FROM trees.inventory_current t
WHERE ST_Within(
t.geom,
ST_MakeEnvelope(min_x, min_y, max_x, max_y, 25832)
);
$$ LANGUAGE SQL STABLE;
API Call:
POST /rpc/trees_in_bbox
{"min_x": 549000, "min_y": 5804000, "max_x": 550000, "max_y": 5805000}
For map applications, return complete GeoJSON FeatureCollections:
CREATE OR REPLACE FUNCTION api.trees_feature_collection()
RETURNS json AS $$
SELECT json_build_object(
'type', 'FeatureCollection',
'features', COALESCE(json_agg(
json_build_object(
'type', 'Feature',
'id', gisid,
'geometry', ST_AsGeoJSON(geom)::json,
'properties', json_build_object(
'species', art,
'vitality', vitalitaet,
'planting_year', pflanzjahr
)
)
), '[]'::json)
)
FROM trees.inventory_current;
$$ LANGUAGE SQL STABLE;
PostgREST leverages PostgreSQL’s built-in security features:
-- Create roles for different access levels
CREATE ROLE api_anon NOLOGIN; -- Anonymous users
CREATE ROLE api_user NOLOGIN; -- Authenticated users
CREATE ROLE api_admin NOLOGIN; -- Administrators
-- Grant read access to anonymous users
GRANT USAGE ON SCHEMA api TO api_anon;
GRANT SELECT ON api.trees_public TO api_anon;
-- Grant write access to authenticated users
GRANT USAGE ON SCHEMA api TO api_user;
GRANT SELECT, INSERT, UPDATE ON api.tree_observations TO api_user;
-- Grant full access to administrators
GRANT USAGE ON SCHEMA trees, context, analysis TO api_admin;
GRANT ALL ON ALL TABLES IN SCHEMA trees TO api_admin;
-- Enable RLS on observations table
ALTER TABLE api.tree_observations ENABLE ROW LEVEL SECURITY;
-- Users can only see their own observations
CREATE POLICY user_observations ON api.tree_observations
FOR ALL
USING (user_id = current_setting('request.jwt.claim.sub')::int);
PostgREST supports JWT (JSON Web Token) authentication:
Configuration:
jwt-secret = "your-256-bit-secret-key"
db-anon-role = "api_anon"
In a closed university network environment, exposing PostgREST to the Internet requires careful consideration of security while maintaining ease of use. This section outlines approaches that don’t rely on VPN solutions like Tailscale.
Deploy a reverse proxy (Nginx, Caddy, or Traefik) in the university DMZ to handle external traffic:
┌──────────────────────────────────────────────────────────────────┐
│ Internet │
└────────────────────────────┬─────────────────────────────────────┘
│ HTTPS (443)
▼
┌──────────────────────────────────────────────────────────────────┐
│ University Firewall / DMZ │
│ ┌────────────────────────────────────────────────────────────┐ │
│ │ Reverse Proxy (Nginx/Caddy/Traefik) │ │
│ │ - TLS termination (Let's Encrypt) │ │
│ │ - Rate limiting │ │
│ │ - Request filtering │ │
│ │ - Access logging │ │
│ └────────────────────────────┬───────────────────────────────┘ │
└───────────────────────────────┼──────────────────────────────────┘
│ HTTP (3000) - internal only
▼
┌──────────────────────────────────────────────────────────────────┐
│ Internal University Network │
│ ┌────────────────────────────────────────────────────────────┐ │
│ │ PostgREST Server (<internal-api-hostname>:3000) │ │
│ └────────────────────────────┬───────────────────────────────┘ │
│ │ │
│ ┌────────────────────────────▼───────────────────────────────┐ │
│ │ PostGIS Database (localhost:5432) │ │
│ └────────────────────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────────────────────┘
Nginx Configuration Example:
server {
listen 443 ssl http2;
server_name <api-hostname>;
# TLS Configuration
ssl_certificate /etc/letsencrypt/live/<api-hostname>/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/live/<api-hostname>/privkey.pem;
ssl_protocols TLSv1.2 TLSv1.3;
ssl_ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256;
# Security Headers
add_header X-Frame-Options "SAMEORIGIN" always;
add_header X-Content-Type-Options "nosniff" always;
add_header X-XSS-Protection "1; mode=block" always;
add_header Strict-Transport-Security "max-age=31536000; includeSubDomains" always;
# Rate Limiting
# Note: The limit_req_zone directive must be defined in the http block (typically /etc/nginx/nginx.conf)
# Add this line to the http block: limit_req_zone $binary_remote_addr zone=api:10m rate=10r/s;
limit_req zone=api burst=20 nodelay;
# Proxy to PostgREST
location / {
proxy_pass http://postgrest-internal:3000;
proxy_set_header Host $host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
# CORS for web applications
add_header Access-Control-Allow-Origin "https://<dashboard-hostname>";
add_header Access-Control-Allow-Methods "GET, POST, OPTIONS";
add_header Access-Control-Allow-Headers "Authorization, Content-Type";
}
}
Caddy provides automatic HTTPS with minimal configuration:
<api-hostname> {
reverse_proxy postgrest-internal:3000
# Rate limiting
rate_limit {
zone api {
key {remote_host}
events 10
window 1s
}
}
# Security headers
header {
X-Frame-Options "SAMEORIGIN"
X-Content-Type-Options "nosniff"
Strict-Transport-Security "max-age=31536000; includeSubDomains"
}
}
JWT Token Authentication (Primary):
# PostgREST configuration
jwt-secret = "your-secure-256-bit-secret"
jwt-aud = "bgcki-api"
db-anon-role = "api_anon"
API Key Authentication (Simple Alternative):
For simpler use cases, implement API key validation via a PostgreSQL function:
-- Create API keys table
CREATE TABLE api.api_keys (
id SERIAL PRIMARY KEY,
key_hash TEXT NOT NULL UNIQUE,
description TEXT,
created_at TIMESTAMP DEFAULT NOW(),
expires_at TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE
);
-- Enable pgcrypto extension for secure hashing
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- Function to generate and store a new API key (returns the plain key once)
CREATE OR REPLACE FUNCTION api.create_api_key(description TEXT, expires_in_days INTEGER DEFAULT NULL)
RETURNS TEXT AS $$
DECLARE
plain_key TEXT;
hashed_key TEXT;
BEGIN
-- Generate a random API key (24 bytes = 32 base64 characters)
plain_key := encode(gen_random_bytes(24), 'base64');
-- Hash with bcrypt for secure storage
hashed_key := crypt(plain_key, gen_salt('bf'));
INSERT INTO api.api_keys (key_hash, description, expires_at)
VALUES (
hashed_key,
description,
CASE WHEN expires_in_days IS NOT NULL
THEN NOW() + (expires_in_days || ' days')::INTERVAL
ELSE NULL END
);
-- Return plain key (only shown once!)
RETURN plain_key;
END;
$$ LANGUAGE plpgsql;
-- Validation function
CREATE OR REPLACE FUNCTION api.validate_api_key(api_key TEXT)
RETURNS BOOLEAN AS $$
SELECT EXISTS (
SELECT 1 FROM api.api_keys
WHERE key_hash = crypt(api_key, key_hash)
AND is_active = TRUE
AND (expires_at IS NULL OR expires_at > NOW())
);
$$ LANGUAGE SQL STABLE SECURITY DEFINER;
-- Example: Create a new API key
-- SELECT api.create_api_key('Dashboard application', 365);
-- Returns: 'abc123...' (save this key securely!)
Restrict access to known IP ranges at the firewall or reverse proxy level:
# Nginx: Allow specific IP ranges
location / {
# University network ranges
allow 130.75.0.0/16; # LUH network example
allow 192.168.0.0/16; # Internal ranges
# Partner institutions
allow 141.51.0.0/16; # Partner university example
# Deny all others
deny all;
proxy_pass http://postgrest-internal:3000;
}
For public data sharing, create strictly read-only views:
-- Public schema with limited data
CREATE SCHEMA api_public;
-- Read-only view with non-sensitive fields only
CREATE OR REPLACE VIEW api_public.trees_summary AS
SELECT
gisid,
art AS species,
ST_AsGeoJSON(ST_Transform(geom, 4326))::json AS geometry
FROM trees.inventory_current;
-- Anonymous role with minimal permissions
CREATE ROLE api_public_anon NOLOGIN;
GRANT USAGE ON SCHEMA api_public TO api_public_anon;
GRANT SELECT ON api_public.trees_summary TO api_public_anon;
-- No INSERT, UPDATE, DELETE permissions
Limit Query Complexity:
# PostgREST configuration
max-rows = 1000
db-max-rows = 10000
db-pre-request = "api.check_request"
Pre-Request Validation Function:
CREATE OR REPLACE FUNCTION api.check_request()
RETURNS VOID AS $$
DECLARE
req_path TEXT := current_setting('request.path', true);
req_method TEXT := current_setting('request.method', true);
BEGIN
-- Block DELETE operations (HTTP DELETE method)
IF req_method = 'DELETE' THEN
RAISE EXCEPTION 'Operation not permitted'
USING HINT = 'DELETE operations are disabled on this API';
END IF;
-- Log all requests for audit
INSERT INTO api.request_log (path, method, timestamp, ip_address)
VALUES (
req_path,
req_method,
NOW(),
current_setting('request.header.x-real-ip', true)
);
END;
$$ LANGUAGE plpgsql;
PostgREST automatically generates OpenAPI documentation accessible at the root endpoint:
# Get OpenAPI spec
curl https://<api-hostname>/
# Use with Swagger UI
# Deploy Swagger UI pointing to your API spec
Docker Compose Addition for Swagger UI:
services:
swagger-ui:
image: swaggerapi/swagger-ui
ports:
- "8080:8080"
environment:
API_URL: https://<api-hostname>/
Provide ready-to-use examples for common use cases:
Python (requests):
import requests
API_URL = "https://<api-hostname>"
API_KEY = "your-api-key"
# Get trees with high drought risk
response = requests.get(
f"{API_URL}/tree_risk_scores",
headers={"Authorization": f"Bearer {API_KEY}"},
params={"overall_drought_risk": "gte.0.7", "limit": 100}
)
trees = response.json()
JavaScript (fetch):
const API_URL = "https://<api-hostname>";
async function getHighRiskTrees() {
const response = await fetch(
`${API_URL}/tree_risk_scores?overall_drought_risk=gte.0.7&limit=100`,
{
headers: {
"Authorization": "Bearer your-api-key",
"Accept": "application/json"
}
}
);
return await response.json();
}
Excel/Power Query:
// Power Query M formula
let
Source = Json.Document(
Web.Contents(
"https://<api-hostname>/trees_public",
[Headers=[Authorization="Bearer your-api-key"]]
)
)
in
Source
Enable cross-origin requests for browser-based applications. Here’s a robust approach using Nginx map directive:
# Add to nginx.conf http block
map $request_method $cors_method {
OPTIONS 1;
default 0;
}
# In server block
location / {
# CORS headers for all responses
add_header 'Access-Control-Allow-Origin' 'https://<dashboard-hostname>' always;
add_header 'Access-Control-Allow-Methods' 'GET, POST, OPTIONS' always;
add_header 'Access-Control-Allow-Headers' 'Authorization, Content-Type' always;
add_header 'Access-Control-Max-Age' 86400 always;
# Handle preflight requests
if ($cors_method) {
return 204;
}
proxy_pass http://postgrest-internal:3000;
}
Alternative: Handle CORS in PostgREST (simpler approach):
Use the db-extra-search-path to include a schema with CORS-handling functions, or configure CORS at the reverse proxy level as shown above. For development environments only, tools like cors-anywhere can be useful for testing but should not be used in production.
-- Create audit log table
CREATE TABLE api.request_log (
id SERIAL PRIMARY KEY,
path TEXT,
method TEXT,
timestamp TIMESTAMP DEFAULT NOW(),
ip_address TEXT,
user_agent TEXT,
response_status INTEGER,
response_time_ms INTEGER
);
-- Index for efficient queries
CREATE INDEX idx_request_log_timestamp ON api.request_log (timestamp DESC);
-- Simple health check function
CREATE OR REPLACE FUNCTION api.health()
RETURNS JSON AS $$
SELECT json_build_object(
'status', 'healthy',
'timestamp', NOW(),
'database', current_database(),
'version', version()
);
$$ LANGUAGE SQL STABLE;
Monitoring Integration:
# Health check for monitoring systems
curl -f https://<api-hostname>/rpc/health || exit 1
| Component | Recommendation |
|---|---|
| TLS Termination | Reverse proxy (Nginx/Caddy) with Let’s Encrypt |
| Authentication | JWT tokens for users, API keys for services |
| Authorization | PostgreSQL roles + Row-Level Security |
| Rate Limiting | Nginx limit_req or Caddy rate limiting |
| IP Filtering | Optional whitelist for sensitive endpoints |
| Logging | PostgreSQL audit tables + reverse proxy logs |
| Documentation | Auto-generated OpenAPI + Swagger UI |
This approach provides enterprise-grade security while maintaining the simplicity that makes PostgREST attractive for rapid API development.
| Feature | Benefit |
|---|---|
| Native PostgreSQL execution | Query optimization by PostgreSQL planner |
| Connection pooling | Efficient database connection reuse |
| Minimal overhead | Single binary, ~20 MB memory |
| Stateless design | Easy horizontal scaling |
| Caching support | HTTP caching headers, CDN-ready |
limit and offset for large result setsselect parameterExample Optimized Query:
GET /trees_risk?select=gisid,species,risk_score&order=risk_score.desc&limit=100
Add to existing docker-compose.yml:
services:
# ... existing PostGIS service ...
postgrest:
image: postgrest/postgrest:latest
ports:
- "3000:3000"
environment:
PGRST_DB_URI: postgres://bgcki_admin:${POSTGRES_PASSWORD}@postgis:5432/bgcki
PGRST_DB_SCHEMA: api
PGRST_DB_ANON_ROLE: api_anon
PGRST_JWT_SECRET: ${JWT_SECRET}
depends_on:
- postgis
-- Create API schema
CREATE SCHEMA IF NOT EXISTS api;
-- Create read-only view for external access
CREATE OR REPLACE VIEW api.trees_public AS
SELECT
gisid,
art AS species,
vitalitaet AS vitality,
pflanzjahr AS planting_year,
ST_AsGeoJSON(ST_Transform(geom, 4326))::json AS geometry
FROM trees.inventory_current;
-- Create anonymous role
CREATE ROLE api_anon NOLOGIN;
GRANT USAGE ON SCHEMA api TO api_anon;
GRANT SELECT ON api.trees_public TO api_anon;
# Get all trees (limited)
curl "http://localhost:3000/trees_public?limit=10"
# Filter by vitality
curl "http://localhost:3000/trees_public?vitality=lt.3"
# Get OpenAPI specification
curl "http://localhost:3000/"
Before implementing PostgREST for BlueGreenCity-KI, consider:
PostgREST offers significant benefits for BlueGreenCity-KI:
| Benefit | Impact |
|---|---|
| Rapid API Development | Enable external data access without custom backend |
| Geospatial Support | Native integration with PostGIS for spatial queries |
| Security | Leverage PostgreSQL’s robust access control |
| Performance | Direct database execution with minimal overhead |
| Maintainability | Reduce codebase complexity |
| Standards | REST/OpenAPI compliance for interoperability |
PostgREST is particularly valuable for scenarios where data needs to be accessed by external systems, dashboards, or mobile applications while maintaining the integrity and security of the underlying database.
Document Version: 1.1
Last Updated: 2025-11-26
Maintainer: BlueGreenCity-KI Team