Add-Database-PSQL/add_database_psql16.sh

#!/bin/bash
# Autor: Mattia Tadini
# File: add_database_psql16.sh
# Version: 1.00
# Date 14/11/2025

set -e
set -o pipefail
LC_ALL=C

echo "=== Creazione database cliente PostgreSQL ==="

# === Controllo privilegi ===
if [[ "$EUID" -ne 0 ]]; then
  echo "Devi eseguire questo script come root (per poter fare chown/chmod sulla tablespace)."
  exit 1
fi

# === Input nome cliente ===
read -rp "Inserisci il nome del cliente (es. ACME): " CLIENT_RAW

# Rimpiazzo spazi con underscore
CLIENT_RAW=${CLIENT_RAW// /_}

if [[ -z "$CLIENT_RAW" ]]; then
  echo "Nome cliente non valido."
  exit 1
fi

# Maiuscolo/minuscolo
CLIENT_UPPER=${CLIENT_RAW^^}
CLIENT_LOWER=${CLIENT_RAW,,}

# === Scelta prodotto / prefisso database ===
echo "Seleziona il prodotto per cui si installa il database:"
echo "  - AGO"
echo "  - HR"
echo "  - AHRW"
echo "  - ERP"
echo "  - AHI"
echo "  - ALTRO (prefisso personalizzato)"
read -rp "Prodotto (AGO/HR/AHRW/ERP/ALTRO): " PROD_RAW

PROD_RAW=${PROD_RAW^^}

DB_PREFIX=""
case "$PROD_RAW" in
  AGO|HR|AHRW|ERP)
    DB_PREFIX=${PROD_RAW,,}
    ;;
  ALTRO)
    read -rp "Inserisci il prefisso desiderato per il database (es. crm, test): " CUSTOM_PREFIX
    CUSTOM_PREFIX=${CUSTOM_PREFIX// /_}
    if [[ -z "$CUSTOM_PREFIX" ]]; then
      echo "Prefisso non valido."
      exit 1
    fi
    DB_PREFIX=${CUSTOM_PREFIX,,}
    ;;
  *)
    echo "Prodotto non valido. Usa AGO, HR, AHRW, ERP o ALTRO."
    exit 1
    ;;
esac

# === Input password (opzionale) ===
read -rsp "Inserisci la password per l'utente (lascia vuoto per generarla automaticamente): " CLIENT_PWD
echo

if [[ -z "${CLIENT_PWD}" ]]; then
  # Genera password randomica 16 caratteri [A-Za-z0-9]
  CLIENT_PWD=$(tr -dc 'A-Za-z0-9' </dev/urandom | head -c 16 || true)
  if [[ -z "$CLIENT_PWD" ]]; then
    echo "Errore nella generazione della password random."
    exit 1
  fi
  echo "Password generata automaticamente: ${CLIENT_PWD}"
fi

# Niente apici singoli
if [[ "$CLIENT_PWD" == *"'"* ]]; then
  echo "La password non può contenere l'apice singolo (')."
  exit 1
fi

# === Nomi e path in base al cliente/prodotto ===
TS_NAME="${CLIENT_UPPER}"                                       # tablespace in MAIUSCOLO
TS_DIR="/zucchetti/infinity/PostgreSQL/16/data/${CLIENT_UPPER}" # directory tablespace
DB_USER="${CLIENT_LOWER}"                                       # utente in minuscolo
DB_NAME="${DB_PREFIX}_${CLIENT_LOWER}"                          # es: hr_villamafalda

echo
echo ">>> Configurazione:"
echo "  Nome cliente    : ${CLIENT_UPPER}"
echo "  Tablespace name : ${TS_NAME}"
echo "  Tablespace dir  : ${TS_DIR}"
echo "  Database        : ${DB_NAME}"
echo "  Utente          : ${DB_USER}"
echo "  Prodotto        : ${DB_PREFIX}"
echo

# === Creazione directory tablespace con permessi richiesti ===
echo ">>> Creo la directory della tablespace..."
mkdir -p "${TS_DIR}"
chown postgres:postgres "${TS_DIR}"
chmod 700 "${TS_DIR}"

# === Connessione a Postgres come utente postgres / password postgres ===
export PGPASSWORD='postgres'
PSQL_BASE="psql -h localhost -U postgres -d postgres -v ON_ERROR_STOP=1"

echo ">>> Verifico/creo ROLE utente con privilegi (superuser, createdb, createrole, inherit, bypassrls)..."
ROLE_EXISTS=$($PSQL_BASE -tAc "SELECT 1 FROM pg_roles WHERE rolname='${DB_USER}'" || true)
if [[ -z "$ROLE_EXISTS" ]]; then
  $PSQL_BASE -c "CREATE ROLE ${DB_USER}
    WITH LOGIN SUPERUSER CREATEDB CREATEROLE INHERIT BYPASSRLS PASSWORD '${CLIENT_PWD}';"
else
  echo "Ruolo ${DB_USER} già esistente, aggiorno privilegi e password..."
  $PSQL_BASE -c "ALTER ROLE ${DB_USER}
    WITH LOGIN SUPERUSER CREATEDB CREATEROLE INHERIT BYPASSRLS PASSWORD '${CLIENT_PWD}';"
fi

echo ">>> Verifico/creo TABLESPACE (owner = utente cliente)..."
TS_EXISTS=$($PSQL_BASE -tAc "SELECT 1 FROM pg_tablespace WHERE spcname='${TS_NAME}'" || true)
if [[ -z "$TS_EXISTS" ]]; then
  $PSQL_BASE -c "CREATE TABLESPACE ${TS_NAME} OWNER ${DB_USER} LOCATION '${TS_DIR}';"
else
  echo "Tablespace ${TS_NAME} già esistente, imposto owner = ${DB_USER}..."
  $PSQL_BASE -c "ALTER TABLESPACE ${TS_NAME} OWNER TO ${DB_USER};"
fi

echo ">>> Verifico/creo DATABASE..."
DB_EXISTS=$($PSQL_BASE -tAc "SELECT 1 FROM pg_database WHERE datname='${DB_NAME}'" || true)
if [[ -z "$DB_EXISTS" ]]; then
  $PSQL_BASE -c "CREATE DATABASE ${DB_NAME}
    OWNER ${DB_USER}
    TABLESPACE ${TS_NAME}
    ENCODING 'UTF8'
    LC_COLLATE 'it_IT.UTF-8'
    LC_CTYPE 'it_IT.UTF-8'
    TEMPLATE template0;"
else
  echo "Database ${DB_NAME} già esistente, cambio owner se necessario..."
  $PSQL_BASE -c "ALTER DATABASE ${DB_NAME} OWNER TO ${DB_USER};"
fi

echo ">>> Sistemo privilegi sul database (grantor = postgres)..."
$PSQL_BASE -c "REVOKE ALL ON DATABASE ${DB_NAME} FROM ${DB_USER};"
$PSQL_BASE -c "GRANT ALL PRIVILEGES ON DATABASE ${DB_NAME} TO ${DB_USER};"

echo ">>> Sistemo privilegi sulla tablespace (grantor = postgres)..."
$PSQL_BASE -c "REVOKE ALL ON TABLESPACE ${TS_NAME} FROM ${DB_USER};"
$PSQL_BASE -c "GRANT CREATE ON TABLESPACE ${TS_NAME} TO ${DB_USER} WITH GRANT OPTION;"

# === Aggiornamento /root/db.list ===
echo ">>> Aggiorno /root/db.list..."

HOSTNAME_FQDN=$(hostname -f 2>/dev/null || hostname)
SERVER_IP=$(hostname -I 2>/dev/null | awk '{print $1}')
if [[ -z "$SERVER_IP" ]]; then
  SERVER_IP=$(ip -4 addr show scope global 2>/dev/null | awk '/inet /{print $2}' | cut -d/ -f1 | head -n1)
fi

DB_LIST_FILE="/root/db.list"

{
  echo "Host: ${HOSTNAME_FQDN}"
  echo "IP: ${SERVER_IP}"
  echo "User: ${DB_USER}"
  echo "Password: ${CLIENT_PWD}"
  echo "Database: ${DB_NAME}"
  echo "Tablespace: ${TS_DIR}"
  echo
  echo
} >> "$DB_LIST_FILE"

echo
echo ">>> Operazione completata per il cliente ${CLIENT_UPPER}."
echo
echo "Credenziali di accesso al database:"
echo "  Database : ${DB_NAME}"
echo "  Utente   : ${DB_USER}"
echo "  Password : ${CLIENT_PWD}"
echo
echo "Tablespace : ${TS_NAME}"
echo "Directory  : ${TS_DIR}"
echo
echo "Blocco aggiunto a /root/db.list"