prole/knoe-db/Dockerfile.postgres.template

# syntax=docker/dockerfile:1.4
FROM ubuntu:24.04

ENV DEBIAN_FRONTEND=noninteractive
ENV LANG=C.UTF-8
ENV PGDATA=/var/lib/postgresql/data
ENV TZ=Etc/GMT-0

RUN set -eux; \
  apt-get update; \
  apt-get install -y --no-install-recommends \
    ca-certificates curl wget gnupg2 lsb-release \
    locales \
    gosu \
    build-essential git openssh-client \
  ; \
  rm -rf /var/lib/apt/lists/*; \
  locale-gen en_US.UTF-8

# Configure non-interactive timezone to avoid tzdata prompts
RUN set -eux; \
  echo "tzdata tzdata/Areas select Etc" | debconf-set-selections; \
  echo "tzdata tzdata/Zones/Etc select GMT-0" | debconf-set-selections; \
  apt-get update; \
  DEBIAN_FRONTEND=noninteractive apt-get install -y --no-install-recommends tzdata; \
  rm -rf /var/lib/apt/lists/*; \
  ln -snf /usr/share/zoneinfo/$TZ /etc/localtime; \
  echo $TZ > /etc/timezone; \
  dpkg-reconfigure -f noninteractive tzdata

RUN set -eux; \
  mkdir -p /root/.ssh; \
  ssh-keyscan github.com >> /root/.ssh/known_hosts

# PostgreSQL installation
RUN set -eux; \
  apt-get update; \
  wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -; \
  echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list; \
  apt-get update; \
  apt-get install -y --no-install-recommends \
    postgresql-{{MAJOR_VERSION}} \
    postgresql-server-dev-{{MAJOR_VERSION}} \
  ; \
  rm -rf /var/lib/apt/lists/*

ENV PATH=/usr/lib/postgresql/{{MAJOR_VERSION}}/bin:$PATH

RUN set -eux; \
  groupadd -r postgres --gid=999 || true; \
  useradd  -r -g postgres --uid=999 --home-dir=/var/lib/postgresql --shell=/bin/bash postgres || true; \
  mkdir -p /var/lib/postgresql /var/run/postgresql "$PGDATA" /docker-entrypoint-initdb.d /etc/knoe; \
  chown -R postgres:postgres /var/lib/postgresql /var/run/postgresql /docker-entrypoint-initdb.d; \
  chmod 3777 /var/run/postgresql

RUN set -eux; \
  apt-get update; \
  DEBIAN_FRONTEND=noninteractive apt-get install -y --no-install-recommends \
    cmake \
    libssl-dev \
    libgdal-dev \
    libproj-dev \
    libgeos-dev \
    libxml2-dev \
    libjson-c-dev \
    libprotobuf-c-dev \
    protobuf-c-compiler \
  ; \
  rm -rf /var/lib/apt/lists/*

# Extension installation
RUN set -eux; \
  apt-get update; \
{{EXTENSION_INSTALL_STEPS}}
  apt-get install -y --no-install-recommends \
    postgresql-contrib-{{MAJOR_VERSION}} \
    freetds-dev \
  ; \
  rm -rf /var/lib/apt/lists/*

# Build and install tds_fdw
RUN --mount=type=ssh set -eux; \
  cd /tmp; \
  git clone https://github.com/tds-fdw/tds_fdw.git; \
  cd tds_fdw; \
  make USE_PGXS=1; \
  make USE_PGXS=1 install; \
  cd /; \
  rm -rf /tmp/tds_fdw

# Create knoe owner role + schema bootstrap script
RUN set -eux; \
  echo "#!/bin/bash" > /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "set -e" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo 'psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" -v knoe_password="$POSTGRES_PASSWORD" <<-EOSQL' >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    -- Create/update knoe owner role from provisioning master password" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    DO \\\$\\\$" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    DECLARE" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "      knoe_password text := :'knoe_password';" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    BEGIN" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "      IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'knoe') THEN" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "        EXECUTE format('CREATE ROLE knoe LOGIN PASSWORD %L NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT', knoe_password);" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "      ELSE" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "        EXECUTE format('ALTER ROLE knoe LOGIN PASSWORD %L NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT', knoe_password);" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "      END IF;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    END" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    \\\$\\\$;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    CREATE SCHEMA IF NOT EXISTS knoe AUTHORIZATION knoe;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    ALTER SCHEMA knoe OWNER TO knoe;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    REVOKE ALL ON SCHEMA knoe FROM PUBLIC;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    ALTER ROLE knoe SET search_path TO knoe, public;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    -- Grant scaffold for rotating schema-owner roles" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    DO \\\$\\\$ BEGIN" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "      IF NOT EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = 'knoe_catalog_executor') THEN" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "        CREATE ROLE knoe_catalog_executor NOLOGIN;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "      END IF;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    END \\\$\\\$;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    COMMENT ON ROLE knoe_catalog_executor IS 'Grant this to rotating schema-owner roles for EXECUTE access on knoe catalog objects.';" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    GRANT USAGE ON SCHEMA knoe TO knoe_catalog_executor;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA knoe TO knoe_catalog_executor;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "    ALTER DEFAULT PRIVILEGES FOR ROLE knoe IN SCHEMA knoe GRANT EXECUTE ON FUNCTIONS TO knoe_catalog_executor;" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  echo "EOSQL" >> /docker-entrypoint-initdb.d/05_create_knoe_owner.sh; \
  chmod +x /docker-entrypoint-initdb.d/05_create_knoe_owner.sh

# Create extension registration script
RUN set -eux; \
  echo "#!/bin/bash" > /docker-entrypoint-initdb.d/20_create_extensions.sh; \
  echo "set -e" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
  echo 'psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL' >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
  echo "    -- knoe role/schema are provisioned in 05_create_knoe_owner.sh" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
  echo "    CREATE SCHEMA IF NOT EXISTS knoe AUTHORIZATION knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
  echo "    ALTER SCHEMA knoe OWNER TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
  echo "    REVOKE ALL ON SCHEMA knoe FROM PUBLIC;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
  echo "    GRANT USAGE ON SCHEMA knoe TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
{{EXTENSION_CREATE_STEPS}}
  echo "    CREATE EXTENSION IF NOT EXISTS tds_fdw SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
  echo "    GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA knoe TO knoe_catalog_executor;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
  echo "EOSQL" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
  chmod +x /docker-entrypoint-initdb.d/20_create_extensions.sh

# Barman
COPY requirements.txt /
RUN set -xe; apt-get update; apt-get install -y --no-install-recommends build-essential python3-dev python3-pip python3-psycopg2 python3-setuptools
RUN pip3 install --break-system-packages -r /requirements.txt
RUN apt-get remove -y --purge --autoremove build-essential python3-dev && rm -rf /var/lib/apt/lists/*

COPY docker-entrypoint.sh /usr/local/bin/docker-entrypoint.sh
COPY knoe-db-entrypoint.sh /usr/local/bin/knoe-entrypoint.sh
COPY 10_pg_tde_openbao.sh /docker-entrypoint-initdb.d/10_pg_tde_openbao.sh
COPY knoe-db-ssh-openbao.sh /usr/local/bin/knoe-db-ssh-openbao.sh

COPY postgresql/*.conf /etc/postgresql/{{MAJOR_VERSION}}/main/
RUN chown postgres:postgres /etc/postgresql/{{MAJOR_VERSION}}/main/*.conf

ARG PROLE_USER=knoe
RUN set -eux; \
    if [ -n "$PROLE_USER" ] && [ "$PROLE_USER" != "root" ] && [ "$PROLE_USER" != "postgres" ]; then \
        if ! id -u "$PROLE_USER" >/dev/null 2>&1; then \
            useradd -m -s /bin/bash "$PROLE_USER"; \
        fi; \
    fi

RUN set -eux; \
  chmod +x /usr/local/bin/docker-entrypoint.sh /usr/local/bin/knoe-entrypoint.sh /usr/local/bin/knoe-db-ssh-openbao.sh /docker-entrypoint-initdb.d/10_pg_tde_openbao.sh

EXPOSE 5432
CMD ["postgres"]