mirror of
https://github.com/dredx/prole.git
synced 2026-09-23 11:03:59 +00:00
57886f9268
- Copy pg-knoe-auth/ wholesale from upstream/knoe-db/20260523 (Task 1 of docs/plans/junie/upstream-knoe-db-20260523-integration.md). - Extension: PG18 OAUTHBEARER JWT validator using libcurl + OpenSSL RS256. - knoe-db/Dockerfile: add libcurl4-openssl-dev to dev deps; COPY src/ and build with make USE_PGXS=1 install after tds_fdw. - NOT enabled in the default database build (absent from 20_create_extensions.sh). To enable: CREATE EXTENSION pg_knoe_auth; (requires pg_hba.conf oauth_issuer). Closes Task 1 of upstream-knoe-db-20260523-integration.md.
280 lines
15 KiB
Docker
280 lines
15 KiB
Docker
# 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 pkg-config \
|
|
; \
|
|
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
|
|
|
|
# Percona installation
|
|
RUN set -eux; \
|
|
apt-get update; \
|
|
wget -q "https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb"; \
|
|
dpkg -i "percona-release_latest.$(lsb_release -sc)_all.deb"; \
|
|
rm -f "percona-release_latest.$(lsb_release -sc)_all.deb"; \
|
|
apt-get update; \
|
|
percona-release setup ppg-18; \
|
|
apt-get update; \
|
|
apt-get install -y --no-install-recommends \
|
|
percona-postgresql-18 \
|
|
percona-postgresql-server-dev-18 \
|
|
; \
|
|
rm -rf /var/lib/apt/lists/*
|
|
|
|
ENV PATH=/usr/lib/postgresql/18/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 \
|
|
libcurl4-openssl-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; \
|
|
apt-get install -y --no-install-recommends percona-postgresql-18-postgis-3; \
|
|
apt-get install -y --no-install-recommends percona-postgresql-18-pgvector; \
|
|
apt-get install -y --no-install-recommends percona-postgresql-18-pgaudit; \
|
|
apt-get install -y --no-install-recommends percona-postgresql-18-repack; \
|
|
apt-get install -y --no-install-recommends percona-pg-stat-monitor18; \
|
|
apt-get install -y --no-install-recommends percona-pgbadger; \
|
|
apt-get install -y --no-install-recommends percona-pg-tde18; \
|
|
apt-get install -y --no-install-recommends \
|
|
percona-postgresql-contrib \
|
|
freetds-dev \
|
|
libsodium-dev \
|
|
; \
|
|
rm -rf /var/lib/apt/lists/*
|
|
|
|
# Build and install pgsodium (required by supabase_vault)
|
|
RUN set -eux; \
|
|
cd /tmp; \
|
|
git clone https://github.com/michelp/pgsodium.git; \
|
|
cd pgsodium; \
|
|
make; \
|
|
make install; \
|
|
cd /; \
|
|
rm -rf /tmp/pgsodium
|
|
|
|
# Build and install supabase_vault
|
|
RUN set -eux; \
|
|
cd /tmp; \
|
|
git clone https://github.com/supabase/vault.git supabase_vault; \
|
|
cd supabase_vault; \
|
|
make; \
|
|
make install; \
|
|
cd /; \
|
|
rm -rf /tmp/supabase_vault
|
|
|
|
# Install Rust toolchain for pgrx-based extensions (pgmq, wrappers)
|
|
RUN set -eux; \
|
|
curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs | sh -s -- -y --default-toolchain stable; \
|
|
/root/.cargo/bin/cargo install --locked cargo-pgrx
|
|
|
|
ENV PATH=/root/.cargo/bin:$PATH
|
|
|
|
# Build and install pgmq
|
|
RUN set -eux; \
|
|
cd /tmp; \
|
|
git clone https://github.com/tembo-io/pgmq.git; \
|
|
if [ -f pgmq/pgmq-extension/Cargo.toml ]; then \
|
|
cd pgmq/pgmq-extension; \
|
|
cargo pgrx init --pg18=$(which pg_config); \
|
|
cargo pgrx install --release; \
|
|
elif [ -f pgmq/pgmq-extension/Makefile ]; then \
|
|
make -C pgmq/pgmq-extension USE_PGXS=1; \
|
|
make -C pgmq/pgmq-extension USE_PGXS=1 install; \
|
|
else \
|
|
echo "Unable to locate pgmq extension build files"; \
|
|
exit 1; \
|
|
fi; \
|
|
cd /; \
|
|
rm -rf /tmp/pgmq
|
|
|
|
# Build and install wrappers (Supabase FDW)
|
|
RUN set -eux; \
|
|
apt-get update; \
|
|
apt-get install -y --no-install-recommends \
|
|
pkg-config \
|
|
libclang-dev \
|
|
; \
|
|
rm -rf /var/lib/apt/lists/*; \
|
|
cd /tmp; \
|
|
git clone https://github.com/supabase/wrappers.git; \
|
|
cd wrappers/wrappers; \
|
|
/root/.cargo/bin/cargo install --locked cargo-pgrx --version 0.16.1; \
|
|
cargo pgrx init --pg18=$(which pg_config); \
|
|
cargo pgrx install --release; \
|
|
cd /; \
|
|
rm -rf /tmp/wrappers
|
|
|
|
# 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
|
|
|
|
# Build and install pg_knoe_auth (PG18 OAUTHBEARER JWT validator)
|
|
# Compiled and installed here; NOT enabled in the default database build.
|
|
# To enable: CREATE EXTENSION pg_knoe_auth; (requires pg_hba.conf oauth_issuer)
|
|
COPY pg-knoe-auth/src/ /tmp/pg_knoe_auth_src/
|
|
RUN set -eux; \
|
|
cd /tmp/pg_knoe_auth_src; \
|
|
make USE_PGXS=1; \
|
|
make USE_PGXS=1 install; \
|
|
cd /; \
|
|
rm -rf /tmp/pg_knoe_auth_src
|
|
|
|
# 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; \
|
|
echo " -- Core extensions" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS postgis SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " DO \\\$\\\$ BEGIN" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " IF EXISTS (SELECT 1 FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace WHERE e.extname = 'postgis' AND n.nspname <> 'knoe') THEN" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " ALTER EXTENSION postgis SET SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " END IF;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " END \\\$\\\$;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " ALTER EXTENSION postgis OWNER TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS postgis_topology SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS vector SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pgcrypto SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pgaudit;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pg_repack;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pg_stat_statements;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pg_buffercache SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pg_freespacemap SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pgrowlocks SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS postgres_fdw SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS dblink SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pg_stat_monitor;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
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 " -- New extensions: pg_cron, pgmq, wrappers, supabase_vault" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pg_cron;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " GRANT USAGE ON SCHEMA cron TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " REVOKE USAGE ON SCHEMA cron FROM PUBLIC;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " ALTER EXTENSION pg_cron OWNER TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pgmq;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " GRANT USAGE ON SCHEMA pgmq TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " REVOKE USAGE ON SCHEMA pgmq FROM PUBLIC;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " ALTER EXTENSION pgmq OWNER TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS wrappers SCHEMA knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " ALTER EXTENSION wrappers OWNER TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS pgsodium;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " ALTER EXTENSION pgsodium OWNER TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " CREATE EXTENSION IF NOT EXISTS supabase_vault;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " ALTER EXTENSION supabase_vault OWNER TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " GRANT USAGE ON SCHEMA vault TO knoe;" >> /docker-entrypoint-initdb.d/20_create_extensions.sh; \
|
|
echo " REVOKE USAGE ON SCHEMA vault FROM PUBLIC;" >> /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/18/main/
|
|
RUN chown postgres:postgres /etc/postgresql/18/main/*.conf
|
|
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"]
|