Cloud SQL Setup

Questa guida descrive come configurare Google Cloud SQL per i microservizi VISLA.

Overview

L'architettura VISLA utilizza 9 database PostgreSQL separati, uno per ogni microservizio:

Database	Microservizio	Descrizione
auth	Auth Service	Utenti, autenticazione, keystore JWT
devices	Devices Service	Dispositivi GPS, permessi, attributi
positions	Positions Service	Storico posizioni GPS
sharing	Sharing Service	Token condivisione dispositivi
notification	Notification Service	Notifiche, push tokens, preferenze
commands	Commands Service	Template comandi dispositivi
geofences	Geofences Service	Aree geografiche, associazioni
events	Events Service	Eventi dispositivi (SOS, batteria, etc.)
billing	Billing Service	Abbonamenti, pagamenti

---

Creazione Istanza Cloud SQL

Via Console

1. Vai su Cloud SQL in Google Cloud Console
2. Clicca Crea istanza > PostgreSQL
3. Configura:
   • ID istanza: visla-prod-db
   • Password postgres: (genera una password sicura)
   • Versione: PostgreSQL 17
   • Regione: europe-west1 (o la tua regione preferita)
   • Tipo macchina: Scegli in base al carico
4. Clicca Crea

Via gcloud CLI

gcloud sql instances create visla-prod-db \
  --database-version=POSTGRES_17 \
  --tier=db-custom-2-4096 \
  --region=europe-west1 \
  --root-password=YOUR_SECURE_PASSWORD \
  --project=visla-gps

---

Creazione Database

Via Console

1. Vai su Cloud SQL > visla-prod-db > Database
2. Clicca Crea database
3. Crea i 9 database: auth, billing, commands, devices, events, geofences, notification, positions, sharing

Via gcloud CLI

DATABASES=("auth" "billing" "commands" "devices" "events" "geofences" "notification" "positions" "sharing")

for DB in "${DATABASES[@]}"; do
  gcloud sql databases create $DB \
    --instance=visla-prod-db \
    --project=visla-gps
done

---

Creazione Utenti

Ogni microservizio ha un utente dedicato con accesso solo al proprio database.

Via Console

1. Vai su Cloud SQL > visla-prod-db > Utenti
2. Clicca Aggiungi account utente
3. Seleziona Autenticazione integrata
4. Crea gli utenti con nome uguale al database

Via gcloud CLI

# Sostituisci con password sicure!
gcloud sql users create auth --instance=visla-prod-db --password="AUTH_PASSWORD"
gcloud sql users create billing --instance=visla-prod-db --password="BILLING_PASSWORD"
gcloud sql users create commands --instance=visla-prod-db --password="COMMANDS_PASSWORD"
gcloud sql users create devices --instance=visla-prod-db --password="DEVICES_PASSWORD"
gcloud sql users create events --instance=visla-prod-db --password="EVENTS_PASSWORD"
gcloud sql users create geofences --instance=visla-prod-db --password="GEOFENCES_PASSWORD"
gcloud sql users create notification --instance=visla-prod-db --password="NOTIFICATION_PASSWORD"
gcloud sql users create positions --instance=visla-prod-db --password="POSITIONS_PASSWORD"
gcloud sql users create sharing --instance=visla-prod-db --password="SHARING_PASSWORD"

Password sicure

Usa password generate, ad esempio: openssl rand -base64 24

---

Configurazione Permessi

Dopo aver creato database e utenti, configura i permessi per isolare ogni utente.

Connessione a Cloud SQL Studio

1. Vai su Cloud SQL > visla-prod-db > Cloud SQL Studio
2. Connettiti con utente postgres

Script SQL - Passo 1: Grant Ruoli

-- Concedi i ruoli a postgres per poter cambiare ownership
GRANT auth TO postgres;
GRANT billing TO postgres;
GRANT commands TO postgres;
GRANT devices TO postgres;
GRANT events TO postgres;
GRANT geofences TO postgres;
GRANT notification TO postgres;
GRANT positions TO postgres;
GRANT sharing TO postgres;

Script SQL - Passo 2: Assegna Ownership

-- Ogni utente diventa owner del proprio database
ALTER DATABASE auth OWNER TO auth;
ALTER DATABASE billing OWNER TO billing;
ALTER DATABASE commands OWNER TO commands;
ALTER DATABASE devices OWNER TO devices;
ALTER DATABASE events OWNER TO events;
ALTER DATABASE geofences OWNER TO geofences;
ALTER DATABASE notification OWNER TO notification;
ALTER DATABASE positions OWNER TO positions;
ALTER DATABASE sharing OWNER TO sharing;

Script SQL - Passo 3: Revoca Accesso Pubblico

-- Rimuovi accesso pubblico ai database
REVOKE ALL ON DATABASE auth FROM PUBLIC;
REVOKE ALL ON DATABASE billing FROM PUBLIC;
REVOKE ALL ON DATABASE commands FROM PUBLIC;
REVOKE ALL ON DATABASE devices FROM PUBLIC;
REVOKE ALL ON DATABASE events FROM PUBLIC;
REVOKE ALL ON DATABASE geofences FROM PUBLIC;
REVOKE ALL ON DATABASE notification FROM PUBLIC;
REVOKE ALL ON DATABASE positions FROM PUBLIC;
REVOKE ALL ON DATABASE sharing FROM PUBLIC;

Esegui in ordine

Esegui i tre blocchi SQL separatamente e in ordine.

---

Cloud SQL Proxy

Il Cloud SQL Proxy fornisce una connessione sicura ai database senza esporre IP pubblici.

Setup Service Account

# Crea service account
gcloud iam service-accounts create cloudsql-proxy \
  --display-name="Cloud SQL Proxy"

# Assegna permessi
gcloud projects add-iam-policy-binding visla-gps \
  --member="serviceAccount:[email protected]" \
  --role="roles/cloudsql.client"

# Scarica credenziali
gcloud iam service-accounts keys create cloudsql-key.json \
  --iam-account=[email protected]

Connection Name

Ottieni il connection name dalla console Cloud SQL:

visla-gps:europe-west1:visla-prod-db

Esecuzione Locale

# Download proxy (macOS ARM)
curl -o cloud-sql-proxy https://storage.googleapis.com/cloud-sql-connectors/cloud-sql-proxy/v2.8.0/cloud-sql-proxy.darwin.arm64
chmod +x cloud-sql-proxy

# Avvia proxy
./cloud-sql-proxy --credentials-file=./cloudsql-key.json \
  "visla-gps:europe-west1:visla-prod-db"

Connection string per i microservizi:

postgresql://auth:PASSWORD@localhost:5432/auth

---

Docker Compose

Configura il Cloud SQL Proxy come sidecar container.

docker-compose.yml

version: '3.8'

services:
  cloudsql-proxy:
    image: gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.8.0
    container_name: cloudsql-proxy
    command:
      - "visla-gps:europe-west1:visla-prod-db"
      - "--address=0.0.0.0"
      - "--port=5432"
    volumes:
      - ./secrets/cloudsql-key.json:/config/key.json:ro
    environment:
      - GOOGLE_APPLICATION_CREDENTIALS=/config/key.json
    ports:
      - "5432:5432"
    restart: unless-stopped
    networks:
      - visla-network

  auth:
    image: visla/auth:latest
    environment:
      - DATABASE_URL=postgresql://auth:${AUTH_DB_PASSWORD}@cloudsql-proxy:5432/auth
    depends_on:
      - cloudsql-proxy
    networks:
      - visla-network

  devices:
    image: visla/devices:latest
    environment:
      - DATABASE_URL=postgresql://devices:${DEVICES_DB_PASSWORD}@cloudsql-proxy:5432/devices
    depends_on:
      - cloudsql-proxy
    networks:
      - visla-network

  # ... altri servizi ...

networks:
  visla-network:
    driver: bridge

.env file

AUTH_DB_PASSWORD=your_auth_password
DEVICES_DB_PASSWORD=your_devices_password
POSITIONS_DB_PASSWORD=your_positions_password
SHARING_DB_PASSWORD=your_sharing_password
NOTIFICATION_DB_PASSWORD=your_notification_password
COMMANDS_DB_PASSWORD=your_commands_password
GEOFENCES_DB_PASSWORD=your_geofences_password
EVENTS_DB_PASSWORD=your_events_password
BILLING_DB_PASSWORD=your_billing_password

---

Nomad

Configura il Cloud SQL Proxy per Nomad.

Cloud SQL Proxy Job

# cloudsql-proxy.nomad

job "cloudsql-proxy" {
  datacenters = ["dc1"]
  type        = "service"

  group "proxy" {
    count = 1

    network {
      port "postgres" {
        static = 5432
      }
    }

    task "proxy" {
      driver = "docker"

      config {
        image = "gcr.io/cloud-sql-connectors/cloud-sql-proxy:2.8.0"
        args = [
          "visla-gps:europe-west1:visla-prod-db",
          "--address=0.0.0.0",
          "--port=5432",
        ]
        ports = ["postgres"]
        
        volumes = [
          "secrets/cloudsql-key.json:/config/key.json:ro"
        ]
      }

      env {
        GOOGLE_APPLICATION_CREDENTIALS = "/config/key.json"
      }

      template {
        data        = <<EOF
{{ with nomadVar "nomad/jobs/cloudsql-proxy" }}{{ .cloudsql_key }}{{ end }}
EOF
        destination = "secrets/cloudsql-key.json"
      }

      service {
        name = "cloudsql-proxy"
        port = "postgres"

        check {
          type     = "tcp"
          port     = "postgres"
          interval = "10s"
          timeout  = "2s"
        }
      }

      resources {
        cpu    = 256
        memory = 256
      }
    }
  }
}

Microservizio Example (auth.nomad)

job "auth" {
  datacenters = ["dc1"]
  type        = "service"

  group "auth" {
    count = 1

    network {
      port "http" {
        to = 8000
      }
    }

    task "auth" {
      driver = "docker"

      config {
        image = "visla/auth:latest"
        ports = ["http"]
      }

      template {
        data = <<EOF
DATABASE_URL=postgresql://auth:{{ with nomadVar "nomad/jobs/auth" }}{{ .db_password }}{{ end }}@{{ range service "cloudsql-proxy" }}{{ .Address }}:{{ .Port }}{{ end }}/auth
EOF
        destination = "secrets/env"
        env         = true
      }

      service {
        name = "auth"
        port = "http"

        check {
          type     = "http"
          path     = "/health"
          interval = "10s"
          timeout  = "2s"
        }
      }

      resources {
        cpu    = 512
        memory = 512
      }
    }
  }
}

---

Verifica Connessione

Per verificare che un microservizio possa connettersi:

# Test connessione via psql
PGPASSWORD=your_password psql -h localhost -U auth -d auth -c "SELECT 1;"

# Verifica che auth NON possa accedere a devices
PGPASSWORD=auth_password psql -h localhost -U auth -d devices -c "SELECT 1;"
# Deve fallire con: permission denied

---

Troubleshooting

Errore: "must be able to SET ROLE"

Esegui prima i GRANT commands per dare a postgres i permessi necessari.

Errore: "permission denied for database"

Verifica che l'ownership sia stato assegnato correttamente:

SELECT datname, datdba, pg_catalog.pg_get_userbyid(datdba) as owner 
FROM pg_database 
WHERE datname NOT IN ('template0', 'template1');

Errore connessione Cloud SQL Proxy

1. Verifica che il service account abbia il ruolo roles/cloudsql.client
2. Verifica che il file JSON delle credenziali sia corretto
3. Controlla i log del proxy: ./cloud-sql-proxy --debug ...