Setup postgres on Ubuntu, run `chmod +x setup.sh` to make it executable

Miguel Buising
2025-06-01T11:05:54Z

setup.sh

| #!/bin/bash
|
| # Usage: ./setup_postgres.sh <username> <password> <dbname> <expose_flag>
|
| set -e
|
| USERNAME="$1"
| PASSWORD="$2"
| DBNAME="$3"
| EXPOSE="$4"
|
| if [ "$#" -ne 4 ]; then
| echo "Usage: $0 <username> <password> <dbname> <expose_to_0.0.0.0 (true|false)>"
| exit 1
| fi
|
| # Convert to lowercase
| USERNAME=$(echo "$USERNAME" | tr '[:upper:]' '[:lower:]')
| DBNAME=$(echo "$DBNAME" | tr '[:upper:]' '[:lower:]')
|
| # Validate identifiers
| validate_identifier() {
| local NAME="$1"
| if ! [[ "$NAME" =~ ^[a-z_][a-z0-9_]{0,62}$ ]]; then
| echo "❌ Error: '$NAME' is not a valid PostgreSQL identifier."
| echo " - Must start with a letter or underscore"
| echo " - Can only contain lowercase letters, digits, and underscores"
| echo " - Must be 63 characters or fewer"
| exit 1
| fi
| }
|
| validate_identifier "$USERNAME"
| validate_identifier "$DBNAME"
|
| echo "Installing PostgreSQL and UFW..."
| sudo apt update
| sudo apt install -y postgresql postgresql-contrib ufw
|
| echo "Configuring PostgreSQL..."
| sudo systemctl enable postgresql
| sudo systemctl start postgresql
|
| echo "Creating PostgreSQL user and database..."
| sudo -u postgres psql <<EOF
| DO \$\$
| BEGIN
| IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '${USERNAME}') THEN
| CREATE ROLE ${USERNAME} WITH LOGIN PASSWORD '${PASSWORD}';
| END IF;
| END
| \$\$;
|
| CREATE DATABASE ${DBNAME} OWNER ${USERNAME};
| EOF
|
| if [ "$EXPOSE" = "true" ]; then
| echo "Exposing PostgreSQL to 0.0.0.0..."
| sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" /etc/postgresql/*/main/postgresql.conf
| echo "host all all 0.0.0.0/0 md5" | sudo tee -a /etc/postgresql/*/main/pg_hba.conf > /dev/null
| sudo systemctl restart postgresql
| fi
|
| echo "Configuring UFW firewall..."
|
| sudo ufw allow 22/tcp # Always allow SSH
|
| if [ "$EXPOSE" = "true" ]; then
| sudo ufw allow 5432/tcp
| else
| sudo ufw deny 5432/tcp
| fi
|
| sudo ufw --force enable
| sudo ufw reload
|
| echo "✅ PostgreSQL setup complete!"