How to Install PostgreSQL Database in Debian 10

PostgreSQL (sometimes referred to as Postgres) is the most advanced open-source general-purpose and object-relational database system with a proven architecture that runs on all major operating systems. It is a high performance, stable, scalable and extensible database system which provides amazing data integrity and supports powerful add-ons.

Importantly, PostgreSQL allows you to define your own data types, add custom functions, even write code from different programming languages such as C/C++, Java, etc, without recompiling your database.

PostgreSQL is being used by well-known Tech companies such as Apple, Fujitsu, Red Hat, Cisco, Juniper Network, etc.

In this article, we will show you how to install, secure and configure PostgreSQL databases server in Debian 10.

Requirement:

  1. Install a Debian 10 (Buster) Minimal Server

Installing PostgreSQL Server on Debian 10

To install PostgreSQL database server, use the default APT package manager, which will install PostgreSQL 11 server and client.

# apt install postgresql-11 postgresql-client-11
Install PostgreSQL in Debian 10

Install PostgreSQL in Debian 10

On Debian, like any other daemons, the Postgres database is initialized immediately after the package installation is complete as shown in the following screenshot.

PostgreSQL Database Initialization

PostgreSQL Database Initialization

To check if the Postgres database is truly initialized, you can use the pg_isready utility which checks the connection status of a PostgreSQL server, as follows.

# pg_isready 
Verify Database Initialization

Verify Database Initialization

Besides, under systemd, the Postgres service is also started automatically and enabled to start at system boot. To make sure that the service is up and running fine, run the following command.

# systemctl status postgresql
Check PostgreSQL Status

Check PostgreSQL Status

The following are other useful systemctl commands to manager the Postgres service under systemd.

# systemctl start postgresql
# systemctl restart postgresql
# systemctl stop postgresql
# systemctl reload postgresql 		#this reloads the service configuration

Securing and Configuring PostgreSQL Database

By default, the Postgres uses the concept of roles to manage database access permissions and database roles are conceptually completely separate from operating system users. A role can be a user or a group, and a role that has login right is called user.

A freshly initialized system always contains one predefined role called postgres, it has the same name as the operating system user account called postgres, which is used to access the psql (Postgres shell) and other database programs.

The Postgres system user account is not protected using a password, to secure it, you can create a password using the passwd utility.

# passwd postgres
Set Password for Postgres User

Set Password for Postgres User

Also, the Postgres role (or administrative database user if you like) is not secured by default. You also need to secure it with a password. Now switch to the postgres system user account and postgres role (remember to set a strong and secure password) as shown.

# su - postgres
# psql -c "ALTER USER postgres WITH PASSWORD 'securepass_here';"
Secure Postgres Admin Role

Secure Postgres Admin Role

Then exit the postgres account to proceed with the guide.

Configuring Client Authentication

The main Postgres configuration file is located at /etc/postgresql/11/main/postgresql.conf. In addition to this file, Postgres uses two other manually-edited configuration files, which control client authentication.

Client authentication is controlled by the /etc/postgresql/11/main/pg_hba.conf configuration file. Postgres provides many different client authentication methods including password-based authentication. Client connections are authenticated based on client host address, database, and user.

If you choose to use password-based authentication, you can implement one of these methods: md5 or password which operate similarly except for the way that the password is transmitted across the connection, namely MD5-hashed and clear-text respectively.

Using md5 password authentication averts password sniffing by hackers and avoids storing passwords on the server in plain text. Password method can only be securely used if the connection is protected by SSL encryption.

For this guide, we will show how to configure md5 password authentication for client authentication.

# vim /etc/postgresql/11/main/pg_hba.conf 

Look for the following line and change the authentication method to md5 as shown in the screenshot.

local   all             all                                     md5
Configure Client Authentication Method

Configure Client Authentication Method

Save the changes in the file and exit it. Then apply the recent changes by restarting the Postgres service as follows.

# systemctl restart postgresql

Creating a New Database and Database Role/User in PostgreSQL

In this last section, we will show how to create a new database user and a database role to manage it. First, switch to the postgres account and open the Postgres shell as follows.

# su - postgres
$ psql

To create a database called “test_db” run the following SQL command.

postgres=# CREATE DATABASE test_db;

Then create a database user (a role with login rights) who will manage the new database as follows.

postgres=#CREATE USER test_user PASSWORD ‘[email protected]_here’;     #assumes login function by default

To connect to the test_db as the user test_user, run the following command.

 
$ psql -d  test_db  -U test_user
Create New Postgres Database

Create New Postgres Database

For more information, see the PostgreSQL 11 documentation.

Conclusion

That’s if for now! In this guide, we have shown how to install, secure and configure PostgreSQL databases server in Debian 10. Do you have any question or thoughts to share? Use the comment form below to reach us.

Best Affordable Linux and WordPress Services For Your Business
Outsource Your Linux and WordPress Project and Get it Promptly Completed Remotely and Delivered Online.

If You Appreciate What We Do Here On TecMint, You Should Consider:

  1. Stay Connected to: Twitter | Facebook | Google Plus
  2. Subscribe to our email updates: Sign Up Now
  3. Get your own self-hosted blog with a Free Domain at ($3.45/month).
  4. Become a Supporter - Make a contribution via PayPal
  5. Support us by purchasing our premium books in PDF format.
  6. Support us by taking our online Linux courses

We are thankful for your never ending support.

Aaron Kili

Aaron Kili is a Linux and F.O.S.S enthusiast, an upcoming Linux SysAdmin, web developer, and currently a content creator for TecMint who loves working with computers and strongly believes in sharing knowledge.

Your name can also be listed here. Got a tip? Submit it here to become an TecMint author.

RedHat RHCE and RHCSA Certification Book
Linux Foundation LFCS and LFCE Certification Preparation Guide

You may also like...

Got something to say? Join the discussion.

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.