create-starter-line.sh

#!/usr/bin/env sh

set -e

export TEST_SQL="select user || ' connection to ' || (select current_database()) || ' ✅'"

### GET CONNECTION DETAILS AND TEST IT

echo "First please provide superuser credentials and endpoint"

read -p 'ENDPOINT: ' DATABASE_HOST
read -p 'SUPERUSER: ' DATABASE_SUPERUSER
read -sp 'PASSWORD: ' DATABASE_SUPERUSER_PASSWORD

export SUPERUSER_TEMPLATE1_URL="postgres://${DATABASE_SUPERUSER}:${DATABASE_SUPERUSER_PASSWORD}@${DATABASE_HOST}/template1"
psql -d "$SUPERUSER_TEMPLATE1_URL" -c "$TEST_SQL"

# GET NEW DATABASE AND USERS DETAILS TO CREATE THEM
echo "Details for new user (database owner and authenticator)"

read -p 'DATABASE NAME: ' DATABASE_NAME

export DATABASE_OWNER="${DATABASE_NAME}"
read -p "OWNER [$DATABASE_OWNER]: " DATABASE_OWNER
read -sp 'OWNER PASSWORD: ' DATABASE_OWNER_PASSWORD
export DATABASE_OWNER="${DATABASE_OWNER:-$DATABASE_NAME}"
echo ""

export DATABASE_AUTHENTICATOR="${DATABASE_OWNER}_authenticator"
read -p "AUTHENTICATOR [$DATABASE_AUTHENTICATOR]: " DATABASE_AUTHENTICATOR
read -sp 'AUTHENTICATOR PASSWORD: ' DATABASE_AUTHENTICATOR_PASSWORD
export DATABASE_AUTHENTICATOR="${DATABASE_AUTHENTICATOR:-${DATABASE_OWNER}_authenticator}"
echo ""

export DATABASE_VISITOR="${DATABASE_OWNER}_visitor"
read -p "VISITOR [$DATABASE_VISITOR]: " DATABASE_VISITOR
export DATABASE_VISITOR="${DATABASE_VISITOR:-${DATABASE_OWNER}_visitor}"
echo ""

psql -Xv ON_ERROR_STOP=1 "${SUPERUSER_TEMPLATE1_URL}" <<HERE
CREATE ROLE ${DATABASE_OWNER} WITH LOGIN PASSWORD '${DATABASE_OWNER_PASSWORD}';
GRANT ${DATABASE_OWNER} TO ${DATABASE_SUPERUSER};
CREATE ROLE ${DATABASE_AUTHENTICATOR} WITH LOGIN PASSWORD '${DATABASE_AUTHENTICATOR_PASSWORD}' NOINHERIT;
CREATE ROLE ${DATABASE_VISITOR};
GRANT ${DATABASE_VISITOR} TO ${DATABASE_AUTHENTICATOR};
-- Create database
CREATE DATABASE ${DATABASE_NAME} OWNER ${DATABASE_OWNER};
-- Database permissions
REVOKE ALL ON DATABASE ${DATABASE_NAME} FROM PUBLIC;
GRANT ALL ON DATABASE ${DATABASE_NAME} TO ${DATABASE_OWNER};
GRANT CONNECT ON DATABASE ${DATABASE_NAME} TO ${DATABASE_AUTHENTICATOR};
HERE

# TEST CONNECTION TO NEW DATABASE
export DATABASE_URL="postgres://${DATABASE_OWNER}:${DATABASE_OWNER_PASSWORD}@${DATABASE_HOST}/${DATABASE_NAME}"
psql -d "$DATABASE_URL" -c "$TEST_SQL"