PostgreSQL 17 Centralisé

Le homelab asops.fr utilise une instance unique et mutualisée de PostgreSQL 17 (apps/postgresql) connectée au réseau interne godpod_network.

Version 17Port interne 5432godpod_network

Cette approche mutualisée offre :

Économie massive de RAM : Évite d'exécuter 5 ou 6 instances distinctes de SGBD.
Maintenance simplifiée : Sauvegardes centralisées et monitoring unifié.
Mises à jour sécurisées : Un seul moteur critique à patcher et surveiller.

🔑 Procédure de Provisioning d'une Nouvelle Application

Depuis PostgreSQL 15+, les permissions par défaut sur le schéma public ont été restreintes pour des raisons de sécurité. Pour qu'une nouvelle application puisse créer et migrer ses tables sans erreur de droits, la procédure suivante est obligatoire :

1. Déclaration dans `.env`

Ajouter le nom de la base dans la liste d'initialisation du fichier /opt/docker/.env :

POSTGRES_MULTIPLE_DATABASES=...,<nouvelle_app>

2. Exécution des Droits et Ownership

Exécuter les commandes SQL suivantes via psql :

# 1. Création de l'utilisateur
docker compose exec postgresql psql -U postgres -c "CREATE USER <app> WITH PASSWORD '<mon_mot_de_passe>';"

# 2. Création et assignation de la base de données
docker compose exec postgresql psql -U postgres -c "CREATE DATABASE <app> OWNER <app>;"
docker compose exec postgresql psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE <app> TO <app>;"

# 3. Attribution complète des droits sur le schéma public (indispensable PG15+)
docker compose exec postgresql psql -U postgres -d <app> -c "ALTER SCHEMA public OWNER TO <app>; GRANT ALL ON SCHEMA public TO <app>; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO <app>;"

Attention ORM

Si l'étape 3 est omise, de nombreux frameworks ORM (Prisma, TypeORM, Drizzle, Alembic) échoueront lors des migrations initiales avec l'erreur permission denied for schema public.

💾 Sauvegardes & Restauration

docker compose exec postgresql pg_dump -U postgres <app> | gzip > /opt/docker/backups/<app>_$(date +%F).sql.gz