Skip to content

Instantly share code, notes, and snippets.

@hos
Created April 14, 2020 13:30
Show Gist options
  • Save hos/d3943340d0ad029aed3ecee5a666aa47 to your computer and use it in GitHub Desktop.
Save hos/d3943340d0ad029aed3ecee5a666aa47 to your computer and use it in GitHub Desktop.
#!/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"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment