Install and configure pgAdmin 4 on Debian 13 LXC

gistfile1.sh

clear
# Install pgAdmin 4
# Reference: https://www.pgadmin.org/download/pgadmin-4-apt/

## pgAdmin 4 installation prerequisites

### Install lsb_release (not included in Debian 13) 
sudo apt install lsb-release

### Setup the repository
#### Install the public key for the repository (if not done previously):
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

#### Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

## Install pgAdmin
### Install for web mode only 
sudo apt install -y pgadmin4-web 

clear
### Set the PGADMIN_PLATFORM_TYPE variable to avoid bug in script where Debian 13 is not detected
sudo nano /usr/pgadmin4/bin/setup-web.sh

#### Add the following line to the setup script
#### PGADMIN_PLATFORM_TYPE='debian'

clear
### Configure the web server
sudo /usr/pgadmin4/bin/setup-web.sh

# Verify basic installation by browsing to http://pgadmin.corp.techtoolbox.us/pgadmin4/
#
# Note the website is not secure (HTTP, not HTTPS) and requires /pgadmin4 to be specified in the URL

clear
# Configure SSL for pgAdmin

## Download Apache configuration for pgAdmin

sudo curl -fsS https://gist.githubusercontent.com/jeremy-jameson/c8b88d2740b1ebc21c89bc6ddc51c78f/raw/5f8d3e51068a84041478adbda90a940df6fbbbb8/pgadmin4.conf \
	-o /etc/apache2/sites-available/pgadmin4.conf

clear
# Replace pgAdmin Apache module with site
sudo a2disconf pgadmin4
sudo a2ensite pgadmin4
sudo a2dissite 000-default

# Configure SSL for pgAdmin
sudo a2enmod ssl

clear
# Create system user for ACME
sudo adduser acme --system --group --home /home/acme --shell /bin/bash

## Fix permissions for home directory
sudo chmod 700 /home/acme

## Copy "skeleton" files to home directory
sudo cp /etc/skel/.* /home/acme
sudo chown -R acme:acme /home/acme

clear
# Install and configure acme.sh
## Switch to ACME system user
sudo -u acme -i

clear
## Install acme.sh
curl https://get.acme.sh | sh -s [email protected]

clear
## Configure Cloudflare token for DNS API
nano .acme.sh/acme.sh.env

### Add the Cloudflare API token for modifying DNS
### export CF_Token="LuYkh..."

exit

clear
# Allow acme user to restart Apache
sudo visudo

## Add the following

## # Allow specific users without passwords to execute sudo commands
## acme ALL=(ALL) NOPASSWD:/bin/systemctl restart apache2

clear
# Create and configure directory for certificates
sudo mkdir -p /etc/acme/certs
sudo chown -R acme:acme /etc/acme/certs
sudo chmod 775 /etc/acme/certs

# Configure permissions for certificate files
sudo touch /etc/acme/certs/pgadmin.corp.techtoolbox.us.crt
sudo touch /etc/acme/certs/pgadmin.corp.techtoolbox.us.key

# - ACME service account needs read/write permissions on the certificate files
# - Apache web server (group: www-data) needs read permission on the private key
# - Remove read permission on the private key from everyone else
sudo chown acme:www-data /etc/acme/certs/pgadmin.corp.techtoolbox.us*

sudo chmod 644 /etc/acme/certs/pgadmin.corp.techtoolbox.us.crt
sudo chmod 640 /etc/acme/certs/pgadmin.corp.techtoolbox.us.key

clear
# Create and install certificate
## Switch to ACME system user
sudo -u acme -i

clear
## Create certificate
acme.sh --issue -d pgadmin.corp.techtoolbox.us --keylength 2048 --dns dns_cf --server letsencrypt

clear
# Install certificate
acme.sh --install-cert -d pgadmin.corp.techtoolbox.us \
--fullchain-file /etc/acme/certs/pgadmin.corp.techtoolbox.us.crt  \
--key-file /etc/acme/certs/pgadmin.corp.techtoolbox.us.key  \
--reloadcmd "sudo /bin/systemctl restart apache2"

exit

clear
# Configure pgAdmin to use SSL
sudo nano /etc/apache2/sites-available/pgadmin4.conf

## Update the paths to the certificate files

##     SSLCertificateFile /etc/acme/certs/pgadmin.corp.techtoolbox.us.crt
##     SSLCertificateKeyFile /etc/acme/certs/pgadmin.corp.techtoolbox.us.key

clear
## Update configuration file in sites-enabled (from sites-available)
sudo a2dissite pgadmin4
sudo a2ensite pgadmin4

## Restart Apache
sudo sudo systemctl restart apache2

# Verify complete installation by browsing to http://pgadmin.corp.techtoolbox.us/
#
# Confirm the redirect from HTTP to HTTPS and pgAdmin 4 is served from the root (not /pgadmin4)