Tutorials > How to install a PostgreSQL server on Ubuntu 18.04

How to install a PostgreSQL server on Ubuntu 18.04

Published on: 16 January 2020

Database PostgreSQL

PostgreSQL is a worldwide supported open-source database. With over 30 years of service, PostgreSQL, thanks to the various add-ons available, is famous for its robustness, scalability, and performance.

First, connect to your server via an SSH connection. If you haven’t done so yet, following our guide is recommended to connect securely with SSH. In case of a local server, go to the next step and open the terminal of your server.

Installation of PostgreSQL

To proceed with the installation of PostgreSQL, use the apt package manager. Make sure to update the cache:

$ sudo apt update && sudo apt install postgresql postgresql-contrib

Once the installation is completed, the service will be immediately available. Check the log file in /var/log/postgresql/postgresql-10-main.log just to make sure

#  tail /var/log/postgresql/postgresql-10-main.log

...LOG:  database system is ready to accept connections

...

If everything works correctly, the above text will be in the last lines of the log file.

Verifying installation

To verify the successful installation of PostgreSQL, use the command line client called psql. First, use the default user installed by the service. To access the database, the authentication process of the default configuration of PostgreSQL uses the same system users.. 

To log in with the default user , from the command line, run

$ sudo -i -u postgres

Now, start the client and use the \l command to view the list of the databases available :

$ sudo -i -u postgres

$ psql

postgres=# \l
                                                  List of databases

   Name    | Owner   | Encoding |   Collate | Ctype    | Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          | |             | | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          | |             | | postgres=CTc/postgres

(3 rows)
                postgres=# \q

[email protected]:~$ exit

Once completed,exit the client with the command \q and return to the original user by typing ‘exit’.

Managing users and permissions

As previously mentioned, PostgreSQL assumes that every user present in its service is also present in the system. In order to create a new user, first add it to the system via adduser:

$ sudo adduser tutorial

Adding user `tutorial' ...

Adding new group `tutorial' (1000) ...

Adding new user `tutorial' (1000) with group `tutorial' ...

Creating home directory `/home/tutorial' ...

Copying files from `/etc/skel' ...

Enter new UNIX password:

Retype new UNIX password:

passwd: password updated successfully

Changing the user information for tutorial

Enter the new value, or press ENTER for the default

        Full Name []:

        Room Number []:

        Work Phone []:

        Home Phone []:

        Other []:

Is the information correct? [Y/n] y

In the following example a user called "tutorial" is being created, without specifying other information, such as the name or the phone number. Once completed, the PostgreSQL user can be created. by first logging in with the postgres user:

$ sudo -i -u postgres
                $ createuser --interactive

Enter name of role to add: tutorial

Shall the new role be a superuser? (y/n) n

Shall the new role be allowed to create databases? (y/n) n

Shall the new role be allowed to create more new roles? (y/n) n

After creating the user, set a password, logging in through the superuser postgres and altering the user you just created:

$ psql

postgres=# ALTER USER tutorial PASSWORD 'password'; ALTER ROLE;

A database for the newly created user might also be created by running the command:

$ createdb tutorial

Once completed, log out and try to log in with the newly created user:

$ sudo -i -u tutorial

[email protected]:~$ psql

psql (10.9 (Ubuntu 10.9-0ubuntu0.18.04.1))

Type "help" for help.
                tutorial=> \l

                                  List of databases

   Name    | Owner   | Encoding |   Collate | Ctype    | Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          | |             | | postgres=CTc/postgres

 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

           |          | |             | | postgres=CTc/postgres

 tutorial  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

(4 rows)

Enabling access to external clients

In some cases, it may be necessary to allow access by external clients (for example your machine) to one or more databases on the server. Normally, PostgreSQL is not configured to accept connections that are not local, so alter some configuration files.

The first file to be modified is the main configuration file, in /etc/postgresql/10/main/postgresql.conf.

Make sure to edit the following line from:

listen_addresses = 'localhost'

With:

listen_addresses = '*'

In this way you inform PostgreSQL to expect incoming connections from any network interface.

Make sure to save your change. Now move on to editing the file that manages the server access rules in /etc/postgresql/10/main/pg_hba.conf, by adding the following line:

host    tutorial        tutorial 0.0.0.0/0               md5

This modification allows the "tutorial" user to access his database from any host. In particular , the fields indicated are:

  1. host: the type of connection you are accepting. Possible values ​​are:

    • local: non-network connections from the system
    • host: any type of TCP / IP connection
    • hostssl: TCP / IP connections only under SSL protocol
    • hostnossl: TCP / IP connections NOT under SSL protocol
  2. tutorial: name of database to allow access, use "all" to refer to all databases

  3. tutorial: username to grant access

  4. 0.0.0.0/0: IP address, in your case you are authorizing access to any IPv4 address

  5. md5: accepted authentication methodology, some of the most important are:

    • md5: password authentication
    • trust: accepts unconditionally the connection unconditionally
    • peer: uses the system username to connect only to the database of the same name.

For further information access the address https://www.postgresql.org/docs/9.1/auth-pg-hba-conf.html or the official PostgreSQL document.

Once ended, restart the service to apply all the changes:

$ sudo systemctl restart postgresql