Shell Script to create Postgres database and database user

Here is sample shell script to create database and database user.

sudo nano pg_create_db.sh

Sample shell script

copy this script however change db user name and password to what you want.

#!/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. Checking CREATEDB privilege..."
  HAS_CREATEDB=$(psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -tAc "SELECT rolcreatedb FROM pg_roles WHERE rolname = '$NEW_DB_USER'")
  # psql returns 't' for true in boolean columns in text mode on some setups; accept both
  if [ "$HAS_CREATEDB" = "t" ] || [ "$HAS_CREATEDB" = "1" ]; then
    echo "User '$NEW_DB_USER' already has CREATEDB."
  else
    echo "Granting CREATEDB to '$NEW_DB_USER'..."
    psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -c "ALTER ROLE \"$NEW_DB_USER\" CREATEDB;"
  fi
else
  echo "Creating user '$NEW_DB_USER' with CREATEDB privilege..."
  psql -h "$DB_HOST" -p "$DB_PORT" -U "$ADMIN_USER" -d postgres -c "CREATE USER \"$NEW_DB_USER\" WITH PASSWORD '$NEW_DB_PASSWORD' CREATEDB;"
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."

Before running the script, run following command, it will add execute permission to a file so it can be run as a program/script.

ubuntu@imgrabbit:~/shellscripts$ ls -la
total 12
drwxr-xr-x 2 root   root   4096 Mar 13 17:55 .
drwxr-x--- 6 ubuntu ubuntu 4096 Mar 13 17:53 ..
-rw-r--r-- 1 root   root   2791 Mar 13 17:55 pg_create_db.sh
ubuntu@imgrabbit:~/shellscripts$ sudo chmod +x pg_create_db.sh
ubuntu@imgrabbit:~/shellscripts$ ls -la
total 12
drwxr-xr-x 2 root   root   4096 Mar 13 17:55 .
drwxr-x--- 6 ubuntu ubuntu 4096 Mar 13 17:53 ..
-rwxr-xr-x 1 root   root   2791 Mar 13 17:55 pg_create_db.sh

How to run the script

./pg_create_db.sh