How to Install PostgreSQL on Debian 9
Updated on
•6 min read

PostgreSQL, often known simply as Postgres, is an open-source general-purpose object-relational database management system. PostgreSQL has many advanced features such as online backups, point in time recovery, nested transactions, SQL and JSON querying, multi-version concurrency control (MVCC), asynchronous replication and more.
In this tutorial, we will show you how to install PostgreSQL on Debian 9 and explore the fundamentals of basic database administration.
Prerequisites
Before proceeding with this tutorial, make sure the user you are logged in as has sudo privileges .
Installing PostgreSQL
At the time of writing this article, the latest version of PostgreSQL available from the Debian repositories is PostgreSQL version 9.6.
To install PostgreSQL on your Debian server complete the following steps:
Begin by updating the local package index:
sudo apt update
Install the PostgreSQL server and PostgreSQL contrib package which provides additional features for the PostgreSQL database:
sudo apt install postgresql postgresql-contrib
When the installation is completed, the PostgreSQL service will start automatically. To verify the installation we’ll connect to the PostgreSQL database server using the
psql
utility and print the server version :sudo -u postgres psql -c "SELECT version();"
The output will look like this:
version ----------------------------------------------------------------------------------------------------------- PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit (1 row)
PostgreSQL Roles and Authentication Methods
PostgreSQL handles database access permissions using the concept of roles. A role can represent a database user or a group of database users.
PostgreSQL supports a number of authentication methods . The most commonly used methods are:
- Trust - With this method, the role can connect without a password, as long as the criteria defined in the
pg_hba.conf
are met. - Password - A role can connect by providing a password. The passwords can be stored as
scram-sha-256
md5
andpassword
(clear-text) - Ident - This method is only supported on TCP/IP connections. 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
. By default for local connections, PostgreSQL is set to use the peer authentication method.
The postgres
user is created automatically when you install PostgreSQL. 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 the postgres user first you need to switch to the user
postgres and then you can access a PostgreSQL prompt using the psql
utility:
sudo su - postgres
psql
From here, you can interact with your PostgreSQL instance. To exit out of the PostgreSQL shell type:
\q
You can use the sudo
command to access the PostgreSQL prompt without switching users:
sudo -u postgres psql
The postgres
user is typically used only from the local host and it is recommended not to set the password for this user.
Creating PostgreSQL Role and Database
You can create new roles from the command line using the createuser
command. Only superusers and roles with CREATEROLE
privilege can create new roles.
In the following example, we will create a new role named john
a database named johndb
and grant privileges on the database.
Create a new PostgreSQL Role
The following command will create a new role named “john”:
sudo su - postgres -c "createuser john"
Create a new PostgreSQL Database
Create a new database named “johndb” using the
createdb
command:sudo su - postgres -c "createdb johndb"
Grant privileges
To grant permissions to the
john
user on the database we created in the previous step, connect to the PostgreSQL shell:sudo -u postgres psql
and run the following query:
GRANT ALL PRIVILEGES ON DATABASE johndb TO john;
Enable remote access to PostgreSQL server
By default the PostgreSQL, server listens only on the local interface 127.0.0.1
. To enable remote access to your PostgreSQL server open the configuration file postgresql.conf
and add listen_addresses = '*'
in the CONNECTIONS AND AUTHENTICATION
section.
sudo vim /etc/postgresql/9.6/main/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
save the file and restart the PostgreSQL service with:
sudo service postgresql restart
Verify the changes with the ss
utility:
ss -nlt | grep 5432
LISTEN 0 128 0.0.0.0:5432 0.0.0.0:*
LISTEN 0 128 [::]:5432 [::]:*
As you can see from the output above the PostgreSQL server is listening on all interfaces (0.0.0.0).
The last step is to configure the server to accept remote connections by editing the pg_hba.conf
file.
Below are some examples showing different use cases:
# TYPE DATABASE USER ADDRESS METHOD
# The user jane will be able to access all databases from all locations using a md5 password
host all jane 0.0.0.0/0 md5
# The user jane will be able to access only the janedb from all locations using a md5 password
host janedb jane 0.0.0.0/0 md5
# The user jane will be able to access all databases from a trusted location (192.168.1.134) without a password
host all jane 192.168.1.134 trust
Conclusion
You have learned how to install and configure PostgreSQL on your Debian 9 server. For more information on this topic, consult the PostgreSQL Documentation .
If you have any questions, please leave a comment below.