Shell Script to create Postgres database and database user

Here is sample shell script to create database and database user. Before running the script, run following command, it will add execute permission to a file so it can be run as a program/script.

chmod +x script_name

How to run the script

./script_name

Here is sample shell script

#!/bin/bash
set -e
# ====== CONFIG VARIABLES ======
DB_HOST="localhost"
DB_PORT="5432"
ADMIN_USER="postgres"
ADMIN_PASSWORD="postgres_password"

NEW_DB_NAME="new_database_name"
NEW_DB_USER="new_database_user_name"
NEW_DB_PASSWORD="new_database_user_password"

# ==============================

export PGPASSWORD="$ADMIN_PASSWORD"

echo "Checking PostgreSQL connection..."
psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -c '\q' || {
  echo "❌ Unable to connect to PostgreSQL"
  exit 1
}

echo "Checking if user exists..."
USER_EXISTS=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -tAc "SELECT 1 FROM pg_roles WHERE rolname='$NEW_DB_USER'")

if [ "$USER_EXISTS" = "1" ]; then
  echo "⚠️  User '$NEW_DB_USER' already exists. Skipping creation."
else
  echo "Creating user '$NEW_DB_USER'..."
  psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -c "CREATE USER $NEW_DB_USER WITH PASSWORD '$NEW_DB_PASSWORD';"
fi

echo "Checking if database exists..."
DB_EXISTS=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -tAc "SELECT 1 FROM pg_database WHERE datname='$NEW_DB_NAME'")

if [ "$DB_EXISTS" = "1" ]; then
  echo "⚠️  Database '$NEW_DB_NAME' already exists. Skipping creation."
else
  echo "Creating database '$NEW_DB_NAME'..."
  psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -c "CREATE DATABASE $NEW_DB_NAME OWNER $NEW_DB_USER;"
fi

echo "Applying schema privileges on '$NEW_DB_NAME'..."

psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d "$NEW_DB_NAME" <<SQL

GRANT ALL ON SCHEMA public TO $NEW_DB_USER;
ALTER SCHEMA public OWNER TO $NEW_DB_USER;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO $NEW_DB_USER;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO $NEW_DB_USER;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO $NEW_DB_USER;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO $NEW_DB_USER;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO $NEW_DB_USER;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO $NEW_DB_USER;

GRANT CREATE ON SCHEMA public TO $NEW_DB_USER;

SQL

unset PGPASSWORD

echo "✅ Database setup process completed."