How To Secure PostgreSQL on Ubuntu 24.04 or Newer

Securing a database is a paramount task for any organization, especially when dealing with sensitive information. PostgreSQL, one of the most powerful open-source relational database systems, is widely used across various applications, making its security a top priority. In this blog post, we will walk you through essential steps to secure PostgreSQL on Ubuntu 24.04 or newer, ensuring your data remains protected against unauthorized access and vulnerabilities.

1. Install PostgreSQL Securely

Before diving into security configurations, ensure that you install PostgreSQL securely. Use the official repositories to get the latest stable version:

sudo apt update
sudo apt install postgresql

2. Configure PostgreSQL Authentication

PostgreSQL uses a method called “peer authentication” by default, which can be less secure in certain configurations. Modify the pg_hba.conf file to enforce stronger authentication methods. You can find this file in the /etc/postgresql/<version>/main/ directory.

sudo nano /etc/postgresql/<version>/main/pg_hba.conf

Change the authentication method for local connections to md5 or scram-sha-256:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     scram-sha-256

After making changes, restart PostgreSQL:

sudo systemctl restart postgresql

3. Use Strong Passwords for Users

Ensure that all PostgreSQL user accounts have strong, unique passwords. You can change a user’s password using the PostgreSQL command-line utility:

sudo -u postgres psql
ALTER USER your_username WITH PASSWORD 'your_strong_password';

4. Limit User Privileges

Adopt the principle of least privilege by ensuring users only have access to the resources they need. Create roles with specific privileges and assign users to these roles accordingly:

CREATE ROLE limited_user LOGIN PASSWORD 'password';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO limited_user;

5. Enable SSL Connections

To secure data in transit, enable SSL connections. First, generate a self-signed certificate or obtain one from a trusted Certificate Authority (CA). Follow these steps to enable SSL in PostgreSQL:

1. Generate a self-signed certificate:

   sudo openssl req -new -text -out server.req
   sudo openssl rsa -in privkey.pem -out server.key
   sudo openssl x509 -req -in server.req -signkey server.key -out server.crt

2. Place the certificate files in the PostgreSQL data directory:

   sudo cp server.key /etc/postgresql/<version>/main/
   sudo cp server.crt /etc/postgresql/<version>/main/

3. Update the postgresql.conf file to enable SSL:

   sudo nano /etc/postgresql/<version>/main/postgresql.conf

Find and set the following parameters:

   ssl = on
   ssl_cert_file = 'server.crt'
   ssl_key_file = 'server.key'

4. Restart PostgreSQL:

   sudo systemctl restart postgresql

6. Configure Firewall Rules

Use ufw (Uncomplicated Firewall) to restrict access to your PostgreSQL server. By default, PostgreSQL listens on port 5432. Allow access only from trusted IP addresses:

sudo ufw allow from your_trusted_ip to any port 5432

Be sure to enable the firewall if it’s not already running:

sudo ufw enable

7. Regular Updates and Backups

Regularly update PostgreSQL and the underlying Ubuntu system to patch any known vulnerabilities:

sudo apt update
sudo apt upgrade

Additionally, implement a robust backup strategy to safeguard your data. Use pg_dump for regular backups:

sudo -u postgres pg_dump your_database_name > backup_file.sql

8. Monitor and Audit

Monitoring and auditing are crucial for maintaining long-term security. Implement logging by modifying the postgresql.conf file:

logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

Regularly review the logs for any suspicious activities.

Conclusion

Securing PostgreSQL on Ubuntu 24.04 or newer is a multifaceted approach that requires diligent configuration and maintenance. By following the steps outlined in this blog post, you can significantly enhance the security of your PostgreSQL database, protecting your data from unauthorized access and potential breaches. Remember, security is not a one-time task but an ongoing process, so keep your systems updated and continuously monitor for vulnerabilities.

For further assistance or more tips on managing your PostgreSQL database, feel free to reach out to us at Greenhost.cloud!