How To Secure PostgreSQL Server

PostgreSQL, also known as Postgres, is a general-purpose object-relational database management system. It is one of the most advanced open-source databases available. However, there are many security concerns and potential vulnerabilities if the application is not secured correctly.

If you are a system or database administrator, you need to know how to protect Postgres prior to going into production.

In this tutorial, we will show you how to secure the PostgreSQL server on Ubuntu18.04.

Step 1 – Install PostgreSQL

By default, the PostgreSQL server package is available in the Ubuntu 18.04 default repository. You can install it by running the following command:

apt-get update -y
apt-get install postgresql postgresql-contrib -y

Once the installation has been completed, start the PostgreSQL server and enable it to start at reboot:

systemctl start postgresql
systemctl enable postgresql

Step 2 – Create Database and User

Next, you will need to create a database and user for testing purposes.

First, log in to PostgreSQL shell with the following command:

sudo -i -u postgres psql

You should see the following output:

psql (10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
Type "help" for help.

postgres=#

Next, create a user named testuser and set the password.

postgres=# CREATE USER testuser WITH PASSWORD 'password';

Next, create a database named testdb and grant full access to the new user:

postgres=# CREATE DATABASE testdb OWNER testuser;

Next, exit from the PostgreSQL shell with the following command:

postgres=# \q

Step 3 – Configure Listening Address

By default, the PostgreSQL server is listening on the localhost and can not be accessed from outside the network.

For the production environment, you will need to configure PostgreSQL to listen on a public interface.

You can change this setting by editing the file /postgresql.conf:

nano /etc/postgresql/10/main/postgresql.conf

Find the following line:

#listen_addresses = 'localhost'

Replace it with the following:

listen_addresses = 'localhost,your-server-ip'

Save and close the file when you are finished. Then, restart the PostgreSQL service to implement the changes:

systemctl restart postgresql

Step 4 – Configure the Allowed Hosts

For better security, it is recommended to allow only specific IPs to access and modify the PostgreSQL database. You can do it by editing the file pg_hba.conf:

nano /etc/postgresql/10/main/pg_hba.conf

Find the following line:

# local      DATABASE  USER  METHOD  [OPTIONS]

Add the following line below the above line:

host  testdb  testuser   client-ip-address/32   md5

Save and close the file when you are finished. Then, restart the PostgreSQL service to implement the changes:

systemctl restart postgresql

Where:

host : host is a plain or SSL-encrypted TCP/IP socket.
database : testdb is the name of the database the host can connect to.
user : testuser is the name of the user that is allowed to make the connection.
address : client-ip-address/32 specify the IP address of the client computer.
auth-method : md5 is the name of the authentication method.

Step 5 – Configure UFW Firewall

By default, the UFW firewall comes pre-installed in all Debian based distributions. If not installed, you can install it with the following command:

apt-get install ufw -y

Next, it is recommended to configure the UFW firewall rule to grant access to the PostgreSQL default port 5432 to only the client’s IP.

IMPORTANT: Before starting, you will need to allow incoming SSH connection through UFW, as that will make you lose shell access. You will be locked and unable to connect to your instance.

You can allow SSH service using the following command:

ufw allow ssh

Next, enable the UFW firewall with the following command:

ufw enable

Next, allow PostgreSQL port 5432 to only client’s IP address using the following command:

ufw allow from client-ip-address to any port 5432

Next, check the status of the UFW rule with the following command:

ufw status

You should get the following output:

Status: active

To                         Action      From
--                         ------      ----
22/tcp                     ALLOW       Anywhere                 
5432                       ALLOW       client-ip-address
22/tcp (v6)                ALLOW       Anywhere (v6)

Step 6 – Verify the Remote Connection

At this point, the PostgreSQL server is secured and accessible only from the client’s IP.

To verify it, connect your PostgreSQL database from the client’s system:

psql -U testuser -h postgres-server-ip -d testdb

You will be asked to provide the password for testuser, as shown below:

Password for user testuser:

Type your password and hit Enter. Once the connection has been established, you should get the following output:

psql (9.3.24, server 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1))
WARNING: psql major version 9.3, server major version 10.
         Some psql features might not work.
SSL connection (cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256)
Type "help" for help.

testdb=>

That’s it for now.

Conclusion

In the above guide, you learned how to secure the PostgreSQL by configuring PostgreSQL to grant access to only specific hosts. You have also learned how to configure UFW to allow connections only from specific hosts. You should now be able to protect your PostgreSQL server from certain kinds of attacks. Get started today with VPS hosting from Atlantic.Net!