PostgreSQL (sometimes referred to as Postgres) is the most advanced, versatile, object-oriented database system open-source with a proven architecture that works on all major operating systems. Is a high performance, stable, scalable and extensible database system which provides good data integrity and supports the powerful Supplement.
The important thing is that PostgreSQL allows you to define your own data types, add custom functionality, even to write code in different programming languages such as C/C++, Java, etc., without recompiling your database. PostgreSQL uses the well-known technical companies such as Apple, Fujitsu, Red Hat, Cisco, Juniper Network, etc. In this article we will show you how to install, protect and configure the PostgreSQL database server 11 Debian 10.
Installing PostgreSQL on Debian 10
To install PostgreSQL Debian 10, use the package Manager APT which will install both server and client PostgreSQL 11.
apt install postgresql 11 postgresql-client-11
In Debian, the postgres database is initialized immediately after the package installation, as shown in the following screenshot.
To check whether Postgres database is initialized, you can use the utility pg_isready, which checks the connection status of a PostgreSQL server 11 as follows:
In addition, systemd service Postgres is also automatically started and enabled at boot. To ensure that the service is working properly, run the following command.
systemctl status postgresql
This is followed by other useful systemctl commands to manage the Postgres service in systemd.
systemctl start postgresql
systemctl restart postgresql
systemctl stop postgresql
To re-read the configuration after a reboot, run:
systemctl reload postgresql
By default, Postgres uses the concept of roles to manage permissions, database access, and database roles conceptually completely separate from operating system users. The role can be a user or group and role with the right logon is called by the user.
The newly initialized system always contains one predefined role called postgres, and has the same name as the user account operating system, called postgres, which is used to access psql (the Postgres shell) and other database programs.
Account system user Postgres without a password, to protect it you can create a password using the passwd utility.
In addition, the role Postgres (or, if desired, the database superuser) by default is not protected. You also need to protect it with a password. Now switch to user account system postgres, and the postgres role (don’t forget to install a reliable and secure password) as shown below.
su - postgres
psql -c "ALTER USER postgres WITH PASSWORD 'newPassword';"
Next, log out of postgres, to continue with the setup. The main configuration file for Postgres is located here: /etc/postgresql/11/main/postgresql.conf. In addition to this file Postgres uses two other configuration file that control the client authentication.
Client authentication is controlled by configuration file /etc/postgresql/11/main/pg_hba.conf. Postgres provides many different methods of client authentication, including authentication based on the password. Client connections are authenticated based on the address of the client host, database and user.
If you decide to use authentication based on password, you can use one of these methods: md5 or password, working in a similar way, except the method of transmitting the password to the connection, namely MD5 hash and plaintext, respectively.
The use of password authentication md5 prevents interception of passwords by hackers, and allows you to avoid storing passwords on the server in plain text. The password method can be used only if the connection is protected by SSL encryption.
In this guide we will show you how to configure authentication password for md5 authentication of the client.
Locate the following line and change the authentication method to md5 as shown in the screenshot.
local all all md5
Save the changes to file and exit it. Then apply the latest changes by restarting the Postgres service in the following way.
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 a Postgres shell in the following way.
su - postgres
To create a database named “test_db”, run the following SQL command:
CREATE DATABASE test_db;
Then create a database user (role with the right sign) that will manage the new database:
CREATE USER test_user PASSWORD ‘newPassword’;
The configuration of the postgresql Debian 10 is almost completed. To connect to test_db, on behalf of the user test_user, run:
psql -d test_db -U test_user
For more information, see the PostgreSQL Documentation 11.
This, so far, all! In this article we showed you how to make a Debian Postgresql installation 10, and how you can protect and configure the obtained database server. If you have any questions or thoughts you want to share? Use the comments form below.