Guide on how to install PostgreSQL on Ubuntu 18.04 – PostgreSQL or Postgres is a general-purpose object-relational open-source database management system. PostgreSQL has many powerful features that will allow you to create web application complex.
In this tutorial, I will show you how to install PostgreSQL on Ubuntu 18.04 and explore the basics of database administration.
Before continuing with this tutorial, make sure you are logged in as a user with sudo privileges. Read → How to Create a Sudo User and a Sudo Group on Ubuntu.
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:
Refresh the local package index and install the PostgreSQL server together with the PostgreSQL contrib package which provides some additional features for the PostgreSQL database:
$ sudo apt update
$ sudo apt install postgresql postgresql-contrib
Verifying PostgreSQL Installation
After the installation is complete, the PostgreSQL service will start automatically.
To verify the installation, we will try to connect to the PostgreSQL database server using
psql and print the server version:
$ sudo -u postgres psql -c "SELECT version();"
psql is an interactive command line utility that allows you to interact with the PostgreSQL server.
PostgreSQL roles and authentication methods
Database access permissions in PostgreSQL are handled by role concept. A role can represent a database user or a group of database users.
PostgreSQL supports many authentication methods. The most commonly used are:
- Trust – With this method, roles can be connected without a password, as long as the criteria specified in
- Password – Roles can be linked by providing a password. Passwords can be saved as
- Ident – This method is only supported on TCP / IP connections. Works by obtaining client operating system user names, with optional user name mappings.
- Peer – Same as Ident but only supported on local connections.
PostgreSQL client authentication is defined in a 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 the equivalent of the MySQL root user.
To Log in to the PostgreSQL server as a postgres user you must first switch to user postgres and then you can access the PostgreSQL prompt using the utility
From here you can interact with your PostgreSQL instance. To exit a PostgreSQL shell type:
You can also access the PostgreSQL prompt without redirecting users using the sudo command:
$ sudo -u postgres psql
postgres usually only used from local hosts and it is advisable not to set a password for this user.
Create PostgreSQL and Database Roles
You can create a new role from the command line using commands
createuser. Only super users and roles with privileges
CREATEROLE which can create new roles.
In the following example, we will create a new role named
john named database
johndb and grant privileges to the database.
Create a new PostgreSQL Role
The following command will create a new role or roles named john:
$ sudo su - postgres -c "createuser john"
Create a new PostgreSQL Database
Create a new database called johndb using the command
$ sudo su - postgres -c "createdb johndb"
To grant permission to the user
john 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 the 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.
$ 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 by running the following command:
$ sudo service postgresql restart
Verify changes with the utility
$ ss -nlt | grep 5432
Output 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 listens for all interfaces (
The final step is to configure the server to accept remote connections by editing the file
Here are a few examples that demonstrate 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
You have learned how to install and configure PostgreSQL on your Ubuntu 18.04 server.
You can read PostgreSQL 10.4 documentation for more information on this topic.