How to install PostgreSQL Database in Debian 10

To teach more about PostgreSQL, in this article you are going to learn How to install PostgreSQL Database in Debian 10. PostgreSQL is the most advanced open-source general-purpose and object-relational database system with a proven architecture that runs on all major operating systems.

Additionally, It is a high performance, stable, scalable, and extensible database system that provides amazing data integrity and supports powerful add-ons.

One of the most important features is that 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.

Requirement:

Initial Setup with Debian 10

How to install PostgreSQL Database in Debian 10

Follow this guide to learn how to install, secure, and configure the PostgreSQL database server in Debian 10.

It is useful for you to know PostgreSQL is being used by well-known Tech companies such as Apple, Fujitsu, Red Hat, Cisco, Juniper Network, etc.

Buy Linux Virtual Private Server

First, install the PostgreSQL database server by typing:

apt install postgresql-11 postgresql-client-11  

Point: On Debian, like any other daemons, the Postgres database is initialized immediately after the package installation is complete.

You can use the pg_isready utility which checks the connection status of a PostgreSQL server, to check if the Postgres database is truly initialized.

pg_isready

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

systemctl status postgresql  

Use the following useful commands to manage 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

The Postgres uses the concept of roles to manage database access permissions and database roles are conceptually completely separate from operating system users by default.

To explain more, the user is a role that can be a user or a group, and a role that has login right.

You need to secure the Postgres system user account as it is not protected using a password. to do this you can create a password using the passwd utility.

passwd postgres  

Besides, the Postgres role 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 as shown.

Note: Remember to set a strong and secure password.

su - postgres  sql -c "ALTER USER postgres WITH PASSWORD 'securepass_here';"

Now, you can exit the postgres account to proceed with the guide.

Configuring Client Authentication

In this step, you will review the main Postgres configuration file, which is located at /etc/postgresql/11/main/postgresql.conf. Except for this file,  Postgres uses two other manually-edited configuration files, which control client authentication.

To control the Client authentication, use 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

Try one of the following methods if you want to use password-based authentication.

md5 or password which operates similarly except for the way that the password is transmitted across the connection, namely MD5-hashed and clear-text respectively.

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

So, to configure md5 password authentication for client authentication, use the command below.

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

Then, to change the authentication method to md5, look for the following line.

local   all             all                                     md5  

Next, 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

Finally, you will see how to create a new database user and a database role to manage it.

Now, switch to the postgres account and open the Postgres shell as follows.

su - postgres  psql

Then, to create a database called “test_db” run the following SQL command.

postgres=# CREATE DATABASE test_db;  

Next, to manage the new database, create a database user (a role with login rights)

postgres=#CREATE USER test_user PASSWORD ‘securep@ss_here’;     #assumes login function by default  

Run the following command to connect to the test_db as the user test_user,

psql -d  test_db  -U test_user  

Good job! By finishing this tutorial, we have shown how to install, secure, and configure PostgreSQL database server in Debian 10.

Dear user, we wish this tutorial would be helpful for you, to ask any question or review the conversation of our users about this article, please visit Ask page. Also to improve your knowledge, there are so many useful tutorials ready for Eldernode training.