How to Install PostgreSQL on Ubuntu 18.04

PostgreSQL or Postgres is an open-source general-purpose object-relational database management system. PostgreSQL has many advanced features which allow you to create complex web applications.

In this tutorial, we will show you how to install PostgreSQL on Ubuntu 18.04 and explore the fundamentals of basic database administration.

Prerequisites #

Before continuing with this tutorial, make sure you are logged in as a user with sudo privileges .

Install PostgreSQL on Ubuntu #

At the time of writing this article, the latest version of PostgreSQL available from the official Ubuntu repositories is PostgreSQL version 10.4.

To install PostgreSQL on your Ubuntu server follow the steps below:

1. Installing PostgreSQL

   Refresh the local package index and install the PostgreSQL server along with the PostgreSQL contrib package which provides several additional features for the PostgreSQL database:

   sudo apt updatesudo apt install postgresql postgresql-contrib

2. Verifying PostgreSQL Installation

   Once the installation is completed, the PostgreSQL service will start automatically.

   To verify the installation we will try to connect to the PostgreSQL database server using the psql and print the server version :

   sudo -u postgres psql -c "SELECT version();"

   

PostgreSQL Roles and Authentication Methods #

Database access permissions within PostgreSQL are handled with the concept of roles. A role can represent a database user or a group of database users.

PostgreSQL supports multiple authentication methods . The most commonly used 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 and password (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 - postgrespsql

From here, you can interact with your PostgreSQL instance. To exit out of the PostgreSQL shell type:

\q

You can also access the PostgreSQL prompt without switching users using the sudo command:

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.

1. Create a new PostgreSQL Role

   The following command will create a new role named “john”:

   sudo su - postgres -c "createuser john"

2. Create a new PostgreSQL Database

   Create a new database named “johndb” using the createdb command:

   sudo su - postgres -c "createdb johndb"

3. 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/10/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 Ubuntu 18.04 server.

Consult the PostgreSQL 10.4 Documentation for more information on this topic.

If you have any questions, please leave a comment below.