Laboratorio

PostgreSQL es un sistema de gestión de bases de datos relacional, robusto y de código abierto, ampliamente utilizado para almacenar y administrar datos de forma segura y eficiente. En este servidor, se utiliza como base de datos principal para servicios como Nextcloud y Grafana.

pgAdmin, por su parte, es una herramienta web que permite administrar bases de datos PostgreSQL de manera visual, simplificando tareas como la creación de usuarios, bases de datos, consultas y respaldos.

El servicio de base de datos y su entorno de administración se definen en el siguiente archivo docker-compose.yml:

services:
  pgadmin4:
    image: dpage/pgadmin4:8.10
    container_name: pgadmin4
    restart: always
    env_file:
      - .env
    environment:     
      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_email}                   # Mail de acceso al portal de pgadmin
      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_pass}                 # Contraseña de acceso al portal
    ports:
        - '9002:80'
    depends_on:                                                 
      db_postgres:
        condition: service_healthy
    volumes:                                                    
    - pgadmin_data:/var/lib/pgadmin
    - ./config/servers.json:/pgadmin4/servers.json     
    networks:                                                   
    - internal_net 
    - external_net 
 
  db_postgres:
    image: postgres:16
    container_name: postgres
    env_file:
      - .env
    restart: always
    healthcheck:
      test: ["CMD-SHELL", "pg_isready"]
      interval: 10s
      timeout: 5s
      retries: 5
    environment:   
      POSTGRES_USER: ${POSTGRES_USER}          # Usuario de aplicacion que utilizara moodle para conectarse
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}  # Contraseña con la que se conectará
      POSTGRES_DB: ${POSTGRES_DB}              # Nombre de base de datos que se creará
    expose:                                    # Se opta por expose dado que la base de datos no puede estar de cara al usuario
      - 5432
    volumes:                                   
      - local_pgdata:/var/lib/postgresql/data
      - ./config/init-db.sh:/docker-entrypoint-initdb.d/init-db.sh
    networks:                                  
      - internal_net
 
# Exporter de métricas
 
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter:latest
    container_name: postgres_exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://metricas:exporter@db_postgres:5432/${POSTGRES_DB}?sslmode=disable"
    ports:
      - "9187:9187"
    depends_on:
      - db_postgres
    networks:
      - internal_net
 
volumes:
  local_pgdata:
    driver: local
  pgadmin_data:
    driver: local
 
networks:
  internal_net:
    name: net_backend
    driver: bridge
    external: true
  external_net:
    name: net_frontend
    driver: bridge

Las variables de entorno definidas en el archivo .env permiten configurar las credenciales y parámetros de conexión de ambos servicios:

POSTGRES_USER= "USUARIO_DB"
POSTGRES_PASSWORD= "PASSWORD_DB"
POSTGRES_DB= "NOMBRE_DB"
PGADMIN_email ="EMAIL_PGADMIN"
PGADMIN_pass = "PASSWORD_PGADMIN"

Esto permite mantener las contraseñas, usuarios y configuraciones fuera del archivo docker-compose.yml, evitando exponer información sensible y facilitando cambios sin modificar el compose directamente.

El archivo init-db.sh se ejecuta automáticamente la primera vez que se levanta el contenedor de PostgreSQL. Su función es crear la base de datos inicial, asignar permisos y preparar el entorno para su uso por parte de otros servicios (como Nextcloud o Grafana). El código es el siguiente:

#!/bin/bash
set -e
 
# Espera a que PostgreSQL arranque completamente
until pg_isready -U "$POSTGRES_USER"; do
  >&2 echo "Postgres está arrancando..."
  sleep 1
done
 
# Crear bases de datos y usuarios
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "postgres" <<-EOSQL
  -- Crear bases de datos
  CREATE DATABASE nextcloud;
  CREATE DATABASE grafana;
 
  -- Crear usuarios con contraseñas
  CREATE USER unextcloud WITH ENCRYPTED PASSWORD 'cometa21beta';
  CREATE USER ugrafana WITH ENCRYPTED PASSWORD 'comuna11gamma';
 
  -- Otorgar todos los privilegios sobre las bases de datos a los usuarios correspondientes
  GRANT ALL PRIVILEGES ON DATABASE nextcloud TO unextcloud;
  GRANT ALL PRIVILEGES ON DATABASE grafana TO ugrafana;
 
  -- Otorga privilegios adicionales en el esquema público
  -- Les da permiso para acceder al esquema public, pero no para crear objetos todavía
  GRANT USAGE ON SCHEMA public TO unextcloud, ugrafana;
 
  -- Permite que esos usuarios puedan crear objetos dentro del esquema public
  GRANT CREATE ON SCHEMA public TO unextcloud, ugrafana;
 
  -- Le otorga a unextcloud todos los permisos sobre el esquema
  GRANT ALL PRIVILEGES ON SCHEMA public TO unextcloud;
 
  -- Permisos sobre todas las tablas existentes dentro del esquema public
  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO unextcloud;
 
  -- Permisos sobre secuencias (autoincrementos)
  GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO unextcloud;
 
  -- Define los privilegios por defecto para nuevas tablas en el esquema public
  ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO unextcloud;
EOSQL

De esta manera, las bases de datos nextcloud y grafana se crean automáticamente y quedan listas para ser utilizadas en el arranque inicial del contenedor.

Para conectar automáticamente el servicio de PostgreSQL con pgAdmin4, se utiliza el archivo servers.json. Este archivo contiene la configuración necesaria para que pgAdmin se conecte al servidor de base de datos de manera automática al iniciarse, evitando tener que configurar la conexión manualmente.

{
 "Servers": {
  "1": {
   "Name": "DBDockers",
   "Group": "Servers",
   "Host": "postgres",
   "Port": 5432,
   "MaintenanceDB": "NOMBRE_DB",
   "Username": "USUARIO_DB",
   "Password": "PASSWORD_DB",
   "SSLMode": "prefer"
   }
 }
}

Una vez desplegado el contenedor, pgAdmin queda accesible desde el navegador en la siguiente URL:

http://<IP_PRIVADA>:9002

Al ingresar, iniciamos sesión con nuestro usuario correspondiente (de la base de datos como nuestro usuario de pgAdmin). Desde allí, tendremos acceso completo a las bases de datos previamente creadas mediante el script init-db.sh.