#!/bin/bash

# Update the package list and upgrade the system
echo "Updating system packages..."
sudo apt-get update -y && sudo apt-get upgrade -y

# Install PostgreSQL
echo "Installing PostgreSQL..."
sudo apt-get install -y postgresql postgresql-contrib

# Switch to the postgres user to configure the database
echo "Switching to postgres user..."
sudo -i -u postgres psql -c "CREATE USER snauser WITH PASSWORD 'your_secure_password';"

# Create the database named 'snadb'
echo "Creating the database 'snadb'..."
sudo -i -u postgres psql -c "CREATE DATABASE snadb OWNER snauser;"

# Modify PostgreSQL config to listen on all IP addresses (0.0.0.0)
echo "Configuring PostgreSQL to allow external connections..."
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" /etc/postgresql/12/main/postgresql.conf

# Allow external access to PostgreSQL by editing pg_hba.conf
echo "Updating pg_hba.conf for external access..."
echo "host    all             all             0.0.0.0/0               md5" | sudo tee -a /etc/postgresql/12/main/pg_hba.conf

# Restart PostgreSQL service to apply changes
echo "Restarting PostgreSQL service..."
sudo systemctl restart postgresql

# Allow traffic on PostgreSQL port 5432 in the firewall
echo "Allowing external access through the firewall on port 5432..."
sudo ufw allow 5432/tcp

# Enable UFW firewall if not already enabled
echo "Enabling firewall..."
sudo ufw enable

echo "PostgreSQL setup completed! You can now connect to the database 'snadb' as user 'snauser' from external IPs."

# Display connection details
echo "Connection details:"
echo "Database: snadb"
echo "User: snauser"
echo "Port: 5432"