Coolify PostgreSQL

How to launch a PostgreSQL service using Coolify 3.x, ensure that we secure the server, harden the PostgreSQL instance, and perform necessary administrative tasks at the Ubuntu 24.04 VPS layer.

Step-by-Step Instructions for Launching PostgreSQL on Coolify 3.x

1. Log in to Coolify

First, we log into the Coolify 3.x dashboard using our credentials.

2. Create a New Service

From the sidebar, we navigate to "Services" and click "Add new service".
We select PostgreSQL from the list of available services.
Coolify will prompt us to configure the PostgreSQL instance. Here’s how we configure it:
- Version: Select the latest stable PostgreSQL version (Coolify typically offers the latest available version).
- Database Name: Enter the desired name for our database.
- User and Password: Set a strong username and password.
- Port: By default, PostgreSQL runs on port 5432, but we can change this if needed.

3. Deploy PostgreSQL

After configuring the details, we click "Deploy". Coolify will automatically pull the PostgreSQL Docker image, create a container, and start the service.
Once deployed, we should be able to access the database using the provided credentials and connection details.

Securing and Hardening PostgreSQL and the VPS

A. Securing the VPS (Ubuntu 24.04 Layer)

Update the System We first update the package lists and install the latest security updates.
```
sudo apt update && sudo apt upgrade -y
```
Firewall Configuration We enable the UFW (Uncomplicated Firewall) and only allow necessary ports. For PostgreSQL, we will allow port 5432 (or the custom port we configured) if external connections are needed. For Coolify and web applications, we’ll need ports 80 (HTTP) and 443 (HTTPS).
```
sudo ufw allow 22/tcp    # SSH
sudo ufw allow 80/tcp    # HTTP
sudo ufw allow 443/tcp   # HTTPS
sudo ufw allow 5432/tcp  # PostgreSQL (if external connections are needed)
sudo ufw enable
```
SSH Security
- Disable root login and enforce key-based SSH authentication for better security.
- Edit the SSH configuration file:
```
sudo nano /etc/ssh/sshd_config
```
- Set the following options:
```
PermitRootLogin no
PasswordAuthentication no
```
- Then restart the SSH service:
```
sudo systemctl restart sshd
```
Install Fail2Ban Fail2Ban helps protect against brute-force attacks. We can install it and enable the default rules to monitor SSH:
```
sudo apt install fail2ban
sudo systemctl enable fail2ban
sudo systemctl start fail2ban
```

B. Hardening PostgreSQL

Secure PostgreSQL with a Strong Password
- When deploying PostgreSQL, we’ll have already set a strong password for the database user. We recommend using a password manager to generate and store this securely.
Restrict Remote Access
- By default, PostgreSQL is bound to localhost. If we need remote access, it’s crucial to restrict connections to only trusted IP addresses.
- Edit the PostgreSQL configuration file:
```
sudo nano /etc/postgresql/14/main/postgresql.conf
```
- Look for listen_addresses and set it as follows:
```
listen_addresses = 'localhost'  # Or specify specific IPs for external access
```
Configure pg_hba.conf for Secure Authentication
- The pg_hba.conf file controls client authentication. We should configure it to enforce stronger authentication methods.
- Edit the file:
```
sudo nano /etc/postgresql/14/main/pg_hba.conf
```
- Enforce MD5 or SCRAM-SHA-256 for authentication, which is more secure than the default trust authentication.
```
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
```
Enable SSL/TLS for PostgreSQL Connections
- If remote connections are necessary, we should enable SSL to encrypt communication between the client and the server.
- Ensure the ssl parameter in the postgresql.conf is enabled:
```
ssl = on
```
- We would also need to generate SSL certificates and configure PostgreSQL to use them for secure communication.
Set Up Backups We should schedule regular backups to avoid data loss. We can use pg_dump for logical backups or set up physical backups using a tool like pg_basebackup.

Example of a scheduled backup using cron:
```
crontab -e
```
Add a line like the following to back up the database daily:
```
0 2 * * * /usr/bin/pg_dump -U postgres -F c mydatabase > /path/to/backups/mydatabase_$(date +\%Y\%m\%d).backup
```
Monitor PostgreSQL Performance We can install and configure pgAdmin or another PostgreSQL monitoring tool to keep an eye on performance metrics and database health.
Update PostgreSQL Regularly
- Coolify should manage the update process for PostgreSQL containers. However, at the OS level, we need to make sure that the host is updated regularly:
```
sudo apt update && sudo apt upgrade
```

Administrative Tasks on the VPS

PostgreSQL Administrative Tasks
- Restart PostgreSQL:
```
sudo systemctl restart postgresql
```
- Check Status:
```
sudo systemctl status postgresql
```
- Access PostgreSQL Shell:
```
sudo -u postgres psql
```
Monitoring Disk Space and System Resources We should keep an eye on system performance using tools like htop or df:
```
sudo apt install htop
htop
df -h  # Check disk space
```
Log Management We should ensure that logs are rotated and properly managed to avoid filling up the disk. We can configure logrotate to handle PostgreSQL logs efficiently.

Example configuration:
```
sudo nano /etc/logrotate.d/postgresql
```
Add rotation policies:
```
/var/log/postgresql/*.log {
    daily
    missingok
    rotate 14
    compress
    delaycompress
    notifempty
    create 640 postgres adm
}
```

By following these steps, we secure our Ubuntu 24.04 VPS, harden the PostgreSQL service, and handle administrative tasks effectively. Coolify will manage the deployment and scaling of our PostgreSQL container, while we ensure the VPS and PostgreSQL remain secure and perform optimally.

The above instructions are specifically for a VPS that is a dedicated PostgreSQL server. Since our VPS is not a dedicated database server, we want to ensure that firewall changes only affect the PostgreSQL service while leaving other operations untouched. Therefore, we’ll adjust the instructions accordingly. Below are the specific sections that need modification:

Section to Modify: Firewall Configuration

The firewall setup should only affect PostgreSQL-related ports, so we will not alter ports for SSH, HTTP, or HTTPS unless they are already part of your existing configuration. We’ll also avoid enabling the firewall globally unless it’s already in place for other services.

Original Instruction:

sudo ufw allow 22/tcp    # SSH
sudo ufw allow 80/tcp    # HTTP
sudo ufw allow 443/tcp   # HTTPS
sudo ufw allow 5432/tcp  # PostgreSQL (if external connections are needed)
sudo ufw enable

Updated Instruction:

Only Allow PostgreSQL Port (5432) if it’s needed for external access:
```
sudo ufw allow 5432/tcp  # PostgreSQL (for external access if needed)
```
- This will allow access to PostgreSQL without affecting other services or existing firewall rules.
Do Not Enable UFW Globally if the firewall is not already active. We should only add the PostgreSQL-specific rule above and leave any existing firewall setup as-is.
- We’ll skip the command sudo ufw enable if UFW is not already configured.

Section to Modify: SSH Security

The original recommendation involved hardening SSH by disabling root login and password-based authentication. However, since this VPS is not a dedicated database server, and we want to avoid affecting existing SSH setups, no SSH-related changes should be made unless there is a specific need to harden SSH.

Remove the Following Instruction:

sudo nano /etc/ssh/sshd_config
PermitRootLogin no
PasswordAuthentication no
sudo systemctl restart sshd

We won’t touch the SSH configuration unless security improvements are required for this VPS as a whole.

Section to Modify: Logrotate

If log rotation for PostgreSQL is already managed at the VPS level, we don’t need to configure logrotate specifically for PostgreSQL unless the logs become unmanageable. We’ll ensure no additional logrotate configurations disrupt any current settings.

Optional Instruction:

sudo nano /etc/logrotate.d/postgresql

If log rotation is already configured for the VPS, skip adding a new configuration. If PostgreSQL is logging too much, consider adjusting log settings specific to the database service only.

Summary of Changes:

Firewall: Only add rules specific to PostgreSQL and skip enabling UFW globally unless it’s part of the existing setup.
SSH Security: Avoid any changes to SSH that could impact existing operations.
Log Management: Ensure that PostgreSQL logrotate doesn’t override any existing setup unless required.

exonomyapp/Coolify PostgreSQL.md