Atlantic.Net Blog

How To Secure PostgreSQL Server

Hitesh Jethva
by Atlantic.Net (84posts) under VPS Hosting
0 Comments

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.

Prerequisites

  • A fresh Ubuntu 18.04 VPS on the Atlantic.net Cloud Platform.
  • A root password configured on your server.

Step 1 – Create an Atlantic.Net Cloud Server

First, log in to your Atlantic.Net Cloud Server.  Create a new server, choosing Ubuntu 18.04 as the operating system with at least 1GB RAM. Connect to your Cloud Server via SSH and log in using the credentials highlighted at the top of the page.

Once you are logged into your Ubuntu 18.04 server, run the following command to update your base system with the latest available packages.

apt-get update -y

Step 2 – 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 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 3 – 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 4 – 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 5 – 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 6 – 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 7 – 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!

Get A Free To Use Cloud VPS

Free Tier Includes:
G2.1GB Cloud VPS Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


Looking for a Hosting Solution?

We Provide Cloud, Dedicated, & Colocation.

  • Seven Global Data Center Locations.
  • Flexible Private, Public, & Hybrid Hosting.
  • 24x7x365 Security, Support, & Monitoring.
Contact Us Now! Med Tech Award FTC
SOC Audit HIPAA Audit HITECH Audit

Recent Posts

Get started with 12 months of free cloud VPS hosting

Free Tier includes:
G2.1GB Cloud VPS Server Free to Use for One Year
50 GB of Block Storage Free to Use for One Year
50 GB of Snapshots Free to Use for One Year


New York, NY

100 Delawanna Ave, Suite 1

Clifton, NJ 07014

United States

San Francisco, CA

2820 Northwestern Pkwy,

Santa Clara, CA 95051

United States

Dallas, TX

2323 Bryan Street,

Dallas, Texas 75201

United States

Ashburn, VA

1807 Michael Faraday Ct,

Reston, VA 20190

United States

Orlando, FL

440 W Kennedy Blvd, Suite 3

Orlando, FL 32810

United States

Toronto, Canada

20 Pullman Ct, Scarborough,

Ontario M1X 1E4

Canada

London, UK

14 Liverpool Road, Slough,

Berkshire SL1 4QZ

United Kingdom

Resources