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 ‘securep@ss_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.

Hey TecMint readers,

Exciting news! Every month, our top blog commenters will have the chance to win fantastic rewards, like free Linux eBooks such as RHCE, RHCSA, LFCS, Learn Linux, and Awk, each worth $20!

Learn more about the contest and stand a chance to win by sharing your thoughts below!

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.

Each tutorial at TecMint is created by a team of experienced Linux system administrators so that it meets our high-quality standards.

Join the TecMint Weekly Newsletter (More Than 156,129 Linux Enthusiasts Have Subscribed)
Was this article helpful? Please add a comment or buy me a coffee to show your appreciation.

Got Something to Say? Join the Discussion...

Thank you for taking the time to share your thoughts with us. We appreciate your decision to leave a comment and value your contribution to the discussion. It's important to note that we moderate all comments in accordance with our comment policy to ensure a respectful and constructive conversation.

Rest assured that your email address will remain private and will not be published or shared with anyone. We prioritize the privacy and security of our users.