98 lines
3.1 KiB
Bash
98 lines
3.1 KiB
Bash
#!/usr/bin/env bash
|
|
# wget -O /tmp/postgres_new_db_user.sh https://git.technozone.com.au/vijay/Scripts/raw/branch/main/postgres_new_db_user && bash /tmp/postgres_new_db_user.sh && rm -f /tmp/postgres_new_db_user.sh
|
|
# The script lists the docker containers to select or run it on the postgres server direclty.
|
|
|
|
|
|
#!/bin/sh
|
|
|
|
echo "=== PostgreSQL Docker Database & User Setup (POSIX SH Mode) ==="
|
|
|
|
printf "Enter new database name: "
|
|
read DB_NAME
|
|
printf "Enter new username: "
|
|
read DB_USER
|
|
printf "Enter new password: "
|
|
read DB_PASS
|
|
|
|
echo ""
|
|
echo "Scanning Docker containers exposing PostgreSQL ports (5432)..."
|
|
|
|
# Capture containers with 5432 published
|
|
CONTAINER_LIST=$(docker ps --format "{{.Names}} {{.Ports}}" | grep "5432/tcp" | awk '{print $1}')
|
|
|
|
# Check if any match
|
|
if [ -z "$CONTAINER_LIST" ]; then
|
|
echo "❌ No containers exposing port 5432 found."
|
|
echo "Available containers:"
|
|
docker ps --format " - {{.Names}} | {{.Image}} | {{.Ports}}"
|
|
exit 1
|
|
fi
|
|
|
|
echo ""
|
|
echo "Select Postgres container:"
|
|
|
|
i=1
|
|
echo "$CONTAINER_LIST" | while IFS= read -r cname; do
|
|
echo "$i) $cname"
|
|
i=$((i+1))
|
|
done
|
|
|
|
# Re-print list into an indexed variable storage
|
|
i=1
|
|
for cname in $CONTAINER_LIST; do
|
|
eval "C$i=\"$cname\""
|
|
i=$((i+1))
|
|
done
|
|
|
|
TOTAL=$((i-1))
|
|
|
|
printf "Enter choice [1-$TOTAL]: "
|
|
read choice
|
|
|
|
# Validate choice
|
|
case $choice in
|
|
''|*[!0-9]*)
|
|
echo "❌ Invalid input"
|
|
exit 1
|
|
;;
|
|
esac
|
|
|
|
if [ "$choice" -lt 1 ] || [ "$choice" -gt "$TOTAL" ]; then
|
|
echo "❌ Choice out of range"
|
|
exit 1
|
|
fi
|
|
|
|
# Get selected container
|
|
eval "PG_CONTAINER=\$C$choice"
|
|
|
|
echo ""
|
|
echo "✔ Selected container: $PG_CONTAINER"
|
|
|
|
# Wrapper for docker psql
|
|
run_psql() {
|
|
docker exec -i "$PG_CONTAINER" sh -c "$1"
|
|
}
|
|
|
|
echo "Creating role if missing..."
|
|
run_psql "psql -U postgres -tc \"SELECT 1 FROM pg_roles WHERE rolname='$DB_USER'\" | grep -q 1 || psql -U postgres -c \"CREATE ROLE $DB_USER WITH LOGIN PASSWORD '$DB_PASS';\""
|
|
|
|
echo "Creating database if missing..."
|
|
run_psql "psql -U postgres -tc \"SELECT 1 FROM pg_database WHERE datname='$DB_NAME'\" | grep -q 1 || psql -U postgres -c \"CREATE DATABASE $DB_NAME OWNER $DB_USER;\""
|
|
|
|
echo "Granting privileges..."
|
|
run_psql "psql -U postgres -d $DB_NAME -c \"GRANT CONNECT, TEMPORARY ON DATABASE $DB_NAME TO $DB_USER;\""
|
|
run_psql "psql -U postgres -d $DB_NAME -c \"GRANT USAGE, CREATE ON SCHEMA public TO $DB_USER;\""
|
|
run_psql "psql -U postgres -d $DB_NAME -c \"ALTER SCHEMA public OWNER TO $DB_USER;\""
|
|
run_psql "psql -U postgres -d $DB_NAME -c \"GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO $DB_USER;\""
|
|
run_psql "psql -U postgres -d $DB_NAME -c \"GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO $DB_USER;\""
|
|
|
|
run_psql "PGPASSWORD='$DB_PASS' psql -U $DB_USER -d $DB_NAME -c \"ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO $DB_USER;\""
|
|
run_psql "PGPASSWORD='$DB_PASS' psql -U $DB_USER -d $DB_NAME -c \"ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO $DB_USER;\""
|
|
|
|
echo ""
|
|
echo "=== Setup Complete ==="
|
|
echo "Database: $DB_NAME"
|
|
echo "User: $DB_USER"
|
|
echo "Container: $PG_CONTAINER"
|
|
echo "Permissions: Full access granted"
|