First we will give a general introduction to PostgreSQL.
PostgreSQL is an open-source general-purpose object-relational database management system.It has many powerful features such as online backups, point in time recovery, nested transactions, SQL and JSON querying, multi-version concurrency control (MVCC), asynchronous replication, and more.
This article will show you the steps of installing the PostgreSQL database server on Debian 10.
To install PostgreSQL on your Debian server perform the following steps as root or user with sudo privileges:
1、updating the APT package index and Install the PostgreSQL server and contrib package.
sudo apt update sudo apt install postgresql postgresql-contrib
psql tool to verify the installation.
sudo -u postgres psql -c "SELECT version();"
PostgreSQL 11.5 (Debian 11.5-1+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
psqlis an interactive terminal program that allows you to interact with the PostgreSQL server.
Second,PostgreSQL Roles and Authentication Methods
PostgreSQL handles database access permissions using the concept of roles. Depending on how you set up the role, it can represent a database user or a group of database users.
PostgreSQL supports several authentication methods. The most frequently used methods are:
- Trust – A role can connect without a password, as long as the criteria defined in the
- Password – A role can connect by providing a password. The passwords can be stored as
- Ident – Only supported for TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.
- Peer – Same as Ident, but it is only supported on local connections.
PostgreSQL client authentication is defined in the configuration file named
pg_hba.conf. For local connections, PostgreSQL is set to use the peer authentication method.
The “postgres” user is automatically created when PostgreSQL is installed. This user is the superuser for the PostgreSQL instance, and it is equivalent to the MySQL root user.
To log in to the PostgreSQL server as “postgres”, switch to the user postgres and access a PostgreSQL prompt using the
sudo su - postgres psql
From here you can interact with the PostgreSQL server. To exit out of the PostgreSQL shell type:
You can use the
sudo command to access the PostgreSQL prompt without switching users:
sudo -u postgres psql
postgres user is typically used only from the localhost.
Third,Create PostgreSQL Role and Database
createuser command allows you to create new roles from the command line. Only superusers and roles with
CREATEROLE privilege can create new roles.
In the following example, we’ll create a new role named
linuxhowto, a database named
linuxhowtodb and grant privileges on the database to the role.
1、create the role
sudo su - postgres -c "createuser linuxhowto"
2、create the database
sudo su - postgres -c "createdb linuxhowtodb"
3、To grant permissions to the user on the database
sudo -u postgres psql
Run the following query:
grant all privileges on database linuxhowtodb to linuxhowto;
Last,Enable Remote Access to the PostgreSQL Server
By default, the PostgreSQL, server listens only on the local interface
If you want to connect to the PostgreSQL server from remote locations, you need to set the server to listen on the public interface and edit the configuration to accept remote connections.
Open the configuration file
sudo nano /etc/postgresql/11/main/postgresql.conf
listen_addresses = '*' in the
CONNECTIONS AND AUTHENTICATION section. This instruct the server to listen on all network interfaces.
#------------------------------------------------------------------------------ # CONNECTIONS AND AUTHENTICATION #------------------------------------------------------------------------------ # - Connection Settings - listen_addresses = '*' # what IP address(es) to listen on;
Save the file and restart the PostgreSQL service:
sudo service postgresql restart
Verify the changes:
ss -nlt | grep 5432
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:* LISTEN 0 128 [::]:5432 [::]:*
The output should show that the PostgreSQL server listens on all interfaces (0.0.0.0).
The last step is to configure the server to accept remote logins by editing the
Below are some examples showing different use cases:
# TYPE DATABASE USER ADDRESS METHOD # The user linuxhowto will be able to access all databases from all locations using an md5 password host all linuxhowto 0.0.0.0/0 md5 # The user jane will be able to access only the janedb from all locations using an md5 password host linuxhowtodb linuxhowto 0.0.0.0/0 md5 # The user linuxhowto will be able to access all databases from a trusted location (192.168.1.134) without a password host all linuxhowto 192.168.1.134 trust
We’ve explain to you how to install and configure PostgreSQL on Debian 10. For more information on this article, consult the PostgreSQL Documentation.