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.
Table of Contents
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.
Installing PostgreSQL Server on Debian 10
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.
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
Do not miss the related articles.
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.
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.
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.