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!
