Shell script to create database and database ser

Use this script to create database, database user and assign ownership of newly created database t new user

#!/usr/bin/env bash
# Creates database database_name_db and its user database_name_db_user
# inside the tt-postgres container, giving the user full access to the database.
# Safe to re-run: existing role/database are kept, password is (re)set.
#
# Usage:
#   bash create_spliteasy_db.sh                      # prompts for the password
#   DB_PASSWORD='s3cret' bash create_spliteasy_db.sh # non-interactive
#
# Optional overrides: CONTAINER (default tt-postgres), PG_SUPERUSER (default postgres)

set -euo pipefail

CONTAINER="${CONTAINER:-tt-postgres}"
PG_SUPERUSER="${PG_SUPERUSER:-postgres}"
DB_NAME="database_name_db"
DB_USER="database_name_db_user"

if [[ -z "${DB_PASSWORD:-}" ]]; then
  read -rsp "Password for ${DB_USER}: " DB_PASSWORD
  echo
fi
[[ -n "$DB_PASSWORD" ]] || { echo "Password can't be empty" >&2; exit 1; }

docker exec -i "$CONTAINER" psql -U "$PG_SUPERUSER" -d postgres \
  -v ON_ERROR_STOP=1 \
  -v db="$DB_NAME" -v usr="$DB_USER" -v pw="$DB_PASSWORD" <<'SQL'
-- 1. Login role: create if missing, then always set the password
SELECT format('CREATE ROLE %I LOGIN', :'usr')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = :'usr') \gexec
ALTER ROLE :"usr" WITH LOGIN PASSWORD :'pw';

-- 2. Database owned by the new user
SELECT format('CREATE DATABASE %I OWNER %I', :'db', :'usr')
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = :'db') \gexec
ALTER DATABASE :"db" OWNER TO :"usr";
GRANT ALL PRIVILEGES ON DATABASE :"db" TO :"usr";

-- 3. Inside the new database: hand over the public schema
--    (required on PostgreSQL 15+, where PUBLIC lost CREATE on it)
\connect :db
ALTER SCHEMA public OWNER TO :"usr";
GRANT ALL ON SCHEMA public TO :"usr";
SQL

echo "Done. Connection URL for containers on tt-network:"
echo "  postgresql://${DB_USER}:<password>@${CONTAINER}:5432/${DB_NAME}"

About the network. docker exec runs inside the container itself, so it doesn’t need docker-network. If you’d rather connect over the network from a throwaway client container, use:

docker run --rm -it --network docker-network postgres:16 psql -h <docker-postgress> -U postgres

Your app containers on docker-network can then connect with postgresql://db_name_db_user:<password>@tt-postgres:5432/db_name_db.