67 lines
2.8 KiB
Bash
Executable File
67 lines
2.8 KiB
Bash
Executable File
#!/bin/bash
|
|
|
|
# Exit immediately if a command exits with a non-zero status.
|
|
set -e
|
|
|
|
# Check if the DB_PASSWORD environment variable is set
|
|
if [ -z "$DB_PASSWORD" ]; then
|
|
echo "The environment variable DB_PASSWORD is not set. Please set it and rerun the script."
|
|
exit 1
|
|
fi
|
|
|
|
# Update package lists
|
|
sudo apt-get update
|
|
|
|
# Install PostgreSQL
|
|
sudo apt-get install -y postgresql postgresql-contrib
|
|
|
|
# Ensure PostgreSQL service is running
|
|
sudo systemctl start postgresql
|
|
sudo systemctl enable postgresql
|
|
|
|
# Switch to the postgres user
|
|
sudo -i -u postgres bash << EOF
|
|
|
|
# Create user 'ente' with the password from the environment variable
|
|
psql -c "CREATE USER ente WITH PASSWORD '$DB_PASSWORD';"
|
|
|
|
# Create a new database owned by 'ente'
|
|
psql -c "CREATE DATABASE ente_db OWNER ente;"
|
|
|
|
# Connect to the new database and create a schema named 'ente'
|
|
psql -d ente_db -c "CREATE SCHEMA ente AUTHORIZATION ente;"
|
|
|
|
EOF
|
|
|
|
# Allow TCP/IP connections by modifying the pg_hba.conf file
|
|
PG_HBA="/etc/postgresql/$(ls /etc/postgresql)/main/pg_hba.conf"
|
|
sudo sed -i "/# IPv4 local connections:/a hostssl all all 0.0.0.0/0 md5" $PG_HBA
|
|
|
|
# Allow PostgreSQL to listen on all IP addresses by modifying the postgresql.conf file
|
|
POSTGRESQL_CONF="/etc/postgresql/$(ls /etc/postgresql)/main/postgresql.conf"
|
|
sudo sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" $POSTGRESQL_CONF
|
|
|
|
# Enable SSL and specify SSL key and certificate paths
|
|
sudo sed -i "s/#ssl = off/ssl = on/g" $POSTGRESQL_CONF
|
|
sudo sed -i "s|#ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'|ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'|g" $POSTGRESQL_CONF
|
|
sudo sed -i "s|#ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'|ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'|g" $POSTGRESQL_CONF
|
|
|
|
# Generate self-signed SSL certificate
|
|
sudo openssl req -new -x509 -days 365 -nodes -text -out /etc/ssl/certs/postgresql.crt -keyout /etc/ssl/private/postgresql.key -subj "/CN=postgresql"
|
|
sudo chmod 600 /etc/ssl/private/postgresql.key
|
|
sudo chown postgres:postgres /etc/ssl/private/postgresql.key
|
|
sudo chown postgres:postgres /etc/ssl/certs/postgresql.crt
|
|
|
|
# Update PostgreSQL configuration to use the generated certificate and key
|
|
sudo sed -i "s|ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'|ssl_cert_file = '/etc/ssl/certs/postgresql.crt'|g" $POSTGRESQL_CONF
|
|
sudo sed -i "s|ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'|ssl_key_file = '/etc/ssl/private/postgresql.key'|g" $POSTGRESQL_CONF
|
|
|
|
# Reload PostgreSQL service to apply changes
|
|
sudo systemctl restart postgresql
|
|
|
|
# Confirm PostgreSQL is running and listening on the correct port
|
|
sudo systemctl status postgresql
|
|
netstat -plnt | grep postgres
|
|
|
|
echo "PostgreSQL has been set up successfully with SSL. User 'ente' has been created with access via TCP/IP and SSL."
|