FWD » Core Development » Database

#!/usr/bin/env bash

set -euo pipefail

# set -x

# Defaults

PGDATA_DEFAULT="/var/lib/postgresql/data"

LOCALE_DEFAULT="en_US.UTF-8"

ENCODING_DEFAULT="UTF8"

AUTH_DEFAULT="scram-sha-256"   # alternatives: md5 | password | trust

TEMP_PORT_DEFAULT="55432"

SOCKET_DIR_DEFAULT="/tmp"

# Args

PGDATA="$PGDATA_DEFAULT"

DB_LOCALE="$LOCALE_DEFAULT"

DB_ENCODING="$ENCODING_DEFAULT"

AUTH_METHOD="$AUTH_DEFAULT"

TEMP_PORT="$TEMP_PORT_DEFAULT"

SOCKET_DIR="$SOCKET_DIR_DEFAULT"

POSTGRES_PASSWORD=""

ADMIN_USER="fwd_admin"

ADMIN_PASS=""

STANDARD_USER="fwd_user"

STANDARD_PASS=""

function show_usage()

{

   cat <<EOF

Usage: $0 [options]

Creates a PostgreSQL cluster with upstream initdb, compatible with the official

postgres Docker image. Safe to run on Ubuntu (host) or in an Ubuntu-based container.

Options:

  --pgdata DIR                 Data directory (default: $PGDATA_DEFAULT)

  --locale LOCALE              Cluster locale (default: $LOCALE_DEFAULT)

  --encoding ENC               Cluster encoding (default: $ENCODING_DEFAULT)

  --auth METHOD                pg_hba auth method: scram-sha-256 | md5 | password | trust

                               (default: $AUTH_DEFAULT)

  --postgres-password PASS     Initial password for postgres superuser

  --admin-user NAME            Admin role name (default: $ADMIN_USER)

  --admin-pass PASS            Admin role password

  --user-user NAME             Standard role name (default: $STANDARD_USER)

  --user-pass PASS             Standard role password

  --temp-port PORT             Temporary port for bootstrap (default: $TEMP_PORT_DEFAULT)

  --socket-dir DIR             Unix socket dir for bootstrap (default: $SOCKET_DIR_DEFAULT)

Examples:

  $0 --pgdata /data/pg17 --locale en_US.UTF-8 --encoding UTF8 \\

     --auth scram-sha-256 \\

     --postgres-password adminpw \\

     --admin-user app_admin --admin-pass S3cret! \\

     --user-user app_user --user-pass userpw

Notes:

- Uses initdb (NOT pg_createcluster), so the layout matches postgres Docker images.

- Ensures correct ownership and permissions on PGDATA.

EOF

}

# Parse args

while [[ $# -gt 0 ]]; do

   case "$1" in

      --pgdata)             PGDATA="$2"; shift 2 ;;

      --locale)             DB_LOCALE="$2"; shift 2 ;;

      --encoding)           DB_ENCODING="$2"; shift 2 ;;

      --auth)               AUTH_METHOD="$2"; shift 2 ;;

      --postgres-password)  POSTGRES_PASSWORD="$2"; shift 2 ;;

      --admin-user)         ADMIN_USER="$2"; shift 2 ;;

      --admin-pass)         ADMIN_PASS="$2"; shift 2 ;;

      --user-user)          STANDARD_USER="$2"; shift 2 ;;

      --user-pass)          STANDARD_PASS="$2"; shift 2 ;;

      --temp-port)          TEMP_PORT="$2"; shift 2 ;;

      --socket-dir)         SOCKET_DIR="$2"; shift 2 ;;

      -h|--help|-?)         show_usage; exit 0 ;;

      *) echo "ERROR: Unknown option: $1"; show_usage; exit 1 ;;

   esac

done

# ---- Helper: run command as the postgres system user when available ----

function run_as_postgres()

{

   if command -v gosu >/dev/null && [ "$(id -u)" = "0" ]; then

      gosu postgres "$@"

   elif [ "$(id -un)" = "postgres" ]; then

      "$@"

   else

      sudo -u postgres "$@"

   fi

}

# Build safe SQL using psql variables + format(%I,%L) to avoid injection

function create_roles_sql() {

  cat <<'SQL'

-- Create standard user if missing

SELECT format(

  'CREATE ROLE %I LOGIN PASSWORD %L NOSUPERUSER NOCREATEDB NOCREATEROLE',

  :'std_user', :'std_pass'

)

WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = :'std_user')

\gexec

-- Create admin user if missing

SELECT format(

  'CREATE ROLE %I LOGIN PASSWORD %L SUPERUSER CREATEDB CREATEROLE',

  :'admin_user', :'admin_pass'

)

WHERE NOT EXISTS (SELECT FROM pg_roles WHERE rolname = :'admin_user')

\gexec

-- Optionally set postgres password if provided

\if :{?pg_pass}

ALTER ROLE postgres WITH PASSWORD :'pg_pass';

\endif

SQL

}

# ---- Checks ----

function need()

{

   command -v "$1" >/dev/null 2>&1 || { echo "ERROR: Missing required command: $1" >&2; exit 1; }

}

# Setup full path, since sudo gets a PATH without PG

pg_path="/usr/lib/postgresql/${PG_VERSION}/bin"

need ${pg_path}/initdb

need ${pg_path}/pg_ctl

need ${pg_path}/psql

# ---- Prepare PGDATA (ownership and permissions) ----

sudo mkdir -p "$PGDATA"

# Ensure ownership by postgres user (UID/GID may be 999:999 inside official postgres image)

if id postgres >/dev/null 2>&1; then

   sudo chown -R postgres:postgres "$PGDATA" || true

fi

sudo chmod 700 "$PGDATA" || true

# ---- Initialize cluster if needed. Bail if it is there. ----

if [[ ! -s "$PGDATA/PG_VERSION" ]]; then

   echo "INFO: Initializing cluster in $PGDATA (locale=$DB_LOCALE, encoding=$DB_ENCODING)..."

   PWFILE=""

   if [[ -n "$POSTGRES_PASSWORD" ]]; then

      PWFILE="$(mktemp)"

      printf '%s' "$POSTGRES_PASSWORD" > "$PWFILE"

   fi

   initdb_args=( -D "$PGDATA" --locale="$DB_LOCALE" --encoding="$DB_ENCODING" )

   # Set bootstrap superuser name explicitly (default is 'postgres' anyway)

   initdb_args+=( -U postgres )

   if [[ -n "$PWFILE" ]]; then

      initdb_args+=( --pwfile="$PWFILE" )

   fi

   run_as_postgres ${pg_path}/initdb "${initdb_args[@]}"

   [[ -n "$PWFILE" ]] && rm -f "$PWFILE"

else

   echo "WARNING: Cluster already exists at $PGDATA; exiting."

   exit 1

fi

# ---- Configure password_encryption if SCRAM selected ----

POSTGRESQL_CONF="$PGDATA/postgresql.conf"

if [[ "$AUTH_METHOD" == "scram-sha-256" ]]; then

   if ! grep -q '^[# ]*password_encryption[[:space:]]*=' "$POSTGRESQL_CONF" 2>/dev/null; then

      echo "password_encryption = scram-sha-256" | run_as_postgres tee -a "$POSTGRESQL_CONF" > /dev/null

   else

      run_as_postgres sed -i -E "s|^[# ]*password_encryption[[:space:]]*=.*|password_encryption = scram-sha-256|g" "$POSTGRESQL_CONF"

   fi

fi

# Ensure server will accept TCP later (listen on all; comment/uncomment as needed)

if ! grep -q '^[# ]*listen_addresses[[:space:]]*=' "$POSTGRESQL_CONF" 2>/dev/null; then

   echo "listen_addresses = '*'" | run_as_postgres tee -a "$POSTGRESQL_CONF" > /dev/null

else

   run_as_postgres sed -i -E "s|^[# ]*listen_addresses[[:space:]]*=.*|listen_addresses = '*'|g" "$POSTGRESQL_CONF"

fi

# ---- Configure pg_hba.conf ----

PG_HBA="$PGDATA/pg_hba.conf"

echo "INFO: Configuring pg_hba.conf with auth method: $AUTH_METHOD"

# Keep any existing lines, but ensure we have our standard baseline at the end

run_as_postgres tee -a "$PG_HBA" > /dev/null <<HBA

# Added by create_pg_cluster.sh

# Local socket connections

local   all             all                                     $AUTH_METHOD

# IPv4 localhost

host    all             all             127.0.0.1/32            $AUTH_METHOD

# IPv6 localhost

host    all             all             ::1/128                 $AUTH_METHOD

# (Optional) allow all hosts - restrict in production as needed

# host   all             all             0.0.0.0/0               $AUTH_METHOD

# host   all             all             ::/0                    $AUTH_METHOD

HBA

# ---- Bootstrap: start server locally (socket only) to create roles ----

echo "INFO: Starting temporary server for role creation..."

run_as_postgres ${pg_path}/pg_ctl -D "$PGDATA" \

  -o "-c listen_addresses='' -c unix_socket_directories='$SOCKET_DIR' -p $TEMP_PORT" \

  -w start

# Setup 'set' variables

PSQL_ARGS=(

  --set=std_user="${STANDARD_USER}"

  --set=std_pass="${STANDARD_PASS:-}"

  --set=admin_user="${ADMIN_USER}"

  --set=admin_pass="${ADMIN_PASS:-}"

)

[[ -n "$POSTGRES_PASSWORD" ]] && PSQL_ARGS+=(--set=pg_pass="$POSTGRES_PASSWORD")

# Run SQL in create_roles_sql function

echo "INFO: Creating roles..."

create_roles_sql | run_as_postgres psql -h "$SOCKET_DIR" -p "$TEMP_PORT" -U postgres -d postgres \

                                       "${PSQL_ARGS[@]}" \

                                       -v ON_ERROR_STOP=1

# Stop temp server

echo "INFO: Stopping temporary server..."

run_as_postgres ${pg_path}/pg_ctl -D "$PGDATA" -w stop

# Final ownership/permissions setup

if id postgres >/dev/null 2>&1; then

   sudo chown -R postgres:postgres "$PGDATA" || true

fi

sudo chmod 700 "$PGDATA" || true

# Show some useful information

cat <<SUM

✅ Cluster is ready at: $PGDATA

Next steps:

- To run with the official postgres image:

    docker run -e POSTGRES_PASSWORD='${POSTGRES_PASSWORD:-<set>}' \\

               -v <volume_name>:"$PGDATA" \\

               -p 5432:5432 \\

               postgres:${PG_VERSION}

- Or start locally (outside Docker):

    as postgres:  ${pg_path}/pg_ctl -D "$PGDATA" -w start

Notes:

- listen_addresses='*' has been set. Adjust pg_hba.conf to restrict host access before exposing ports.

- Auth method in pg_hba.conf: $AUTH_METHOD

SUM