Script to easily provision application specific new database with associated user and password for PostgreSQL

pg_app_db_with_user.sh

#!/bin/bash

# Check if the correct number of arguments is provided
if [ "$#" -ne 3 ]; then
  echo "Usage: $0 <database_name> <username> <password>"
  exit 1
fi

# Assign arguments to variables
DB_NAME=$1
DB_USER=$2
DB_PASSWORD=$3

# Execute PostgreSQL commands
psql -U postgres <<EOF
-- Step 1: Create the database
CREATE DATABASE $DB_NAME;

-- Step 2: Create the user
CREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';

-- Step 3: Grant ownership of the database to the user
ALTER DATABASE $DB_NAME OWNER TO $DB_USER;

-- Step 4: Grant all privileges on the database to the user
GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;

-- Step 5: Connect to the database and grant schema permissions
\connect $DB_NAME

-- Grant privileges on the public schema
GRANT ALL ON SCHEMA public TO $DB_USER;
GRANT CREATE ON SCHEMA public TO $DB_USER;

EOF

echo "Database '$DB_NAME' and user '$DB_USER' have been successfully created with the specified permissions."