Skip to content

Instantly share code, notes, and snippets.

@jbkunst

jbkunst/setup_postgres.sh

Created January 2, 2025 14:24
Show Gist options
  • Save jbkunst/32bf5f6e04d1c76f48e707e8c5ecd2bf to your computer and use it in GitHub Desktop.
Save jbkunst/32bf5f6e04d1c76f48e707e8c5ecd2bf to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. jbkunst created this gist Jan 2, 2025.
    45 changes: 45 additions & 0 deletions setup_postgres.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,45 @@
    #!/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"