【PG】创建数据库，账户以及schema

#!/bin/bash

# Check if running as the postgres user
if [[ "$USER" != "postgres" ]]; then
  echo "Error: This script must be run as the postgres user."
  exit 1
fi

# Check input parameters
if [ $# -ne 3 ]; then
  echo "Error: Incorrect number of parameters."
  echo "Usage: $0 <database_name> <account_name> <schema_name>"
  exit 1
fi

# Extract input parameters
db_name=$1
account_name=$2
schema_name=$3

# Drop the database
echo "Dropping the database $db_name"
psql -c "DROP DATABASE IF EXISTS $db_name;"

# Drop the account
echo "Dropping the account $account_name"
psql -c "DROP USER IF EXISTS $account_name;"

# Create the database
echo "Creating the database $db_name"
psql -c "CREATE DATABASE $db_name;"

# Create the account
echo "Creating the account $account_name and granting privileges on the database $db_name"
psql -c "CREATE USER $account_name WITH ENCRYPTED PASSWORD 'password';"
psql -c "GRANT ALL PRIVILEGES ON DATABASE $db_name TO $account_name;"
psql -c "alter DATABASE $db_name owner TO $account_name;"

echo " "

# Switch to the target database
echo "Switching to the database $db_name"
psql -d $db_name -c "CREATE SCHEMA $schema_name;"

echo " "
# Backup pg_hba.conf
backup_file="pg_hba.conf_$(date +'%m_%d_%Y_%H%M%S')"
echo "Backing up pg_hba.conf to $backup_file"
cp /etc/postgresql/<version>/main/pg_hba.conf "/etc/postgresql/<version>/main/$backup_file"

# Add entries to pg_hba.conf
echo "Adding entries to pg_hba.conf"
echo "host    $db_name    $account_name    127.0.0.1/32    scram-sha-256" >> /etc/postgresql/<version>/main/pg_hba.conf
echo "local    $db_name    $account_name    ::1/128         trust" >> /etc/postgresql/<version>/main/pg_hba.conf

echo " "
# Reload PostgreSQL configuration
echo "Reloading PostgreSQL configuration"
psql -c "SELECT pg_reload_conf();"

echo "Operation completed."

echo " "



echo "Operation completed."