Postgres Boilerplate Script

APP_USER="user"
APP_DB="user_db"
APP_DB_PASSWORD="password"

cat <<EOF
CREATE USER ${APP_USER} WITH PASSWORD '${APP_DB_PASSWORD}';
CREATE DATABASE ${APP_DB} OWNER ${APP_USER};
GRANT ALL PRIVILEGES ON DATABASE ${APP_DB} TO ${APP_USER};

\c ${APP_DB}
GRANT USAGE ON SCHEMA public TO ${APP_USER};
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ${APP_USER};
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ${APP_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO ${APP_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO ${APP_USER};
EOF

#!/bin/bash

# ==============================================================================
# INTERACTIVE POSTGRESQL CONFIGURATION GENERATOR
# ==============================================================================
# This script prompts for database credentials and generates the necessary SQL
# commands to set up a user, a database, and granular permissions.
# ==============================================================================

# 1. Interactive Input
# ------------------------------------------------------------------------------
# We use 'read' to capture user input.
# The syntax ${VAR:-default} sets a default value if the user just presses Enter.

echo "--- Configuration Setup ---"

# Ask for the Username
read -p "Enter the desired Database Username [default: user]: " INPUT_USER
APP_USER=${INPUT_USER:-user}

# Ask for the Database Name
read -p "Enter the desired Database Name [default: user_db]: " INPUT_DB
APP_DB=${INPUT_DB:-user_db}

# Ask for the Password securely
# -s hides the input (silent mode) so the password isn't shown on screen.
while true; do
    read -s -p "Enter the desired Password: " APP_DB_PASSWORD
    echo "" # Print a new line for formatting
    read -s -p "Confirm Password: " APP_DB_PASSWORD_CONFIRM
    echo "" 

    if [ "$APP_DB_PASSWORD" = "$APP_DB_PASSWORD_CONFIRM" ] && [ ! -z "$APP_DB_PASSWORD" ]; then
        break
    else
        echo "Passwords do not match or are empty. Please try again."
    fi
done

echo "--- Generating SQL ---"
echo ""

# 2. SQL Generation
# ------------------------------------------------------------------------------
# We use a "Heredoc" (<<EOF) to print a multi-line block of text.
# This outputs valid SQL that can be piped directly into psql.

cat <<EOF
-- -----------------------------------------------------------------------------
-- 1. CREATE LOGIN ROLE (User)
-- -----------------------------------------------------------------------------
-- We create a dedicated user rather than using the 'postgres' superuser.
-- This follows the principle of "Least Privilege".
CREATE USER ${APP_USER} WITH PASSWORD '${APP_DB_PASSWORD}';

-- -----------------------------------------------------------------------------
-- 2. CREATE DATABASE
-- -----------------------------------------------------------------------------
-- We create the database container and immediately assign ownership to the new user.
CREATE DATABASE ${APP_DB} OWNER ${APP_USER};

-- -----------------------------------------------------------------------------
-- 3. GRANT CONNECT PRIVILEGES
-- -----------------------------------------------------------------------------
-- Just because a user owns a DB doesn't always mean they can connect to it 
-- depending on pg_hba.conf settings. This explicitly allows connection.
GRANT ALL PRIVILEGES ON DATABASE ${APP_DB} TO ${APP_USER};

-- Note: To apply the following permissions, you must be connected to the specific
-- database ('${APP_DB}'), not the default 'postgres' database.
\c ${APP_DB}

-- -----------------------------------------------------------------------------
-- 4. GRANT SCHEMA USAGE
-- -----------------------------------------------------------------------------
-- In PostgreSQL, tables live inside 'Schemas'. The default is 'public'.
-- Think of the Schema as a room and Tables as cabinets. 
-- 'USAGE' gives the user the key to enter the room.
GRANT USAGE ON SCHEMA public TO ${APP_USER};

-- -----------------------------------------------------------------------------
-- 5. GRANT PERMISSIONS ON EXISTING OBJECTS
-- -----------------------------------------------------------------------------
-- Grant full control over all CURRENT tables and sequences (for IDs) in the schema.
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ${APP_USER};
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ${APP_USER};

-- -----------------------------------------------------------------------------
-- 6. FUTURE-PROOFING (DEFAULT PRIVILEGES)
-- -----------------------------------------------------------------------------
-- This is the most important and often missed step! 
-- Standard GRANTs only apply to tables that exist RIGHT NOW.
-- ALTER DEFAULT PRIVILEGES ensures that if the superuser creates a NEW table
-- in the future, our application user will automatically get access to it.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO ${APP_USER};
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO ${APP_USER};

EOF