How to install PostgreSQL on Ubuntu 20.04

This article was first published in: https://www.itcoder.tech/posts/how-to-install-postgresql-on-ubuntu-20-04/

PostgreSQL or Postgres is an open source, multi-purpose relational database management system. It has many advanced features that allow you to build fault-tolerant environments or complex applications.

In this guide, we will explain how to install PostgreSQL database server on Ubuntu 20.04 and explore the basic operations of PostgreSQL database management.

1. Prerequisites##

To install the package, log in as root or another user with sudo privileges.

2. Install PostgreSQL on Ubuntu

At the time of writing this article, the latest available version of PostgreSQL in the official Ubuntu software source is 10.4.

Run the following command to install PostgreSQL on Ubuntu:

sudo apt update
sudo apt install postgresql postgresql-contrib

We also install the PostgreSQL contrib package, which can provide some additional features of the PostgreSQL database system.

Once the installation is complete, the PostgreSQL service will start automatically. Use the psql tool to verify the installation by connecting to the PostgreSQL database and printing its version:

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

Output:

PostgreSQL 12.2(Ubuntu 12.2-4) on x86_64-pc-linux-gnu, compiled by gcc(Ubuntu 9.3.0-8ubuntu1)9.3.0,64-bit

that's it. PostgreSQL has been installed, and you can start using it.

Three, PostgreSQL roles and authentication methods##

PostgreSQL database access permissions are handled through roles. A role represents a database user or a database user group.

PostgreSQL supports multiple authentication methods. The most commonly used methods are as follows:

Trust-As long as the conditions defined by pg_hba.conf are met, a role can connect to the server without using a password
Password-With a password, a role can connect to the server. The password can be stored as scram-sha-256, md5, and password (plain text).
Ident-Only supports TCP/IP connection. It usually obtains the user name of the client operating system through an optional user name mapping table.
Peer-Like Ident, only local connections are supported.

PostgreSQL client authentication is usually defined in the pg_hba.conf file. By default, for local connections, PostgreSQL is set to authenticate against peers.

In order to log in to the PostgreSQL server as the postgres user, first switch the user, and then use the psql tool to access PostgreSQL.

sudo su - postgres
psql

From here, you can interact with the PostgreSQL instance. Exit the PostgreSQL Shell and enter:

\ q

You can also use the sudo command to access PostgreSQL without switching users:

sudo -u postgres psql

Usually, the postgres user is only used locally.

Fourth, create a PostgreSQL role and database##

Only super users and roles with CREATEROLE permission can create new roles.

In the following example, we create a role named john, a database named johndb, and grant permissions on the database:

Create a new PostgreSQL role:

sudo su - postgres -c "createuser john"

Create a new PostgreSQL database:

sudo su - postgres -c "createdb johndb"

To authorize users to operate the database, connect to the PostgreSQL shell:

sudo -u postgres psql

And run the following query:

grant all privileges on database johndb to john;

Five, enable remote access to PostgreSQL server##

By default, the PostgreSQL server only listens on the local network interface: 127.0.0.1.

To allow remote access to your PostgreSQL server, open the configuration file postgresql.conf and add listen_addresses ='*' in the section CONNECTIONS AND AUTHENTICATION.

sudo nano /etc/postgresql/12/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:

sudo service postgresql restart

Use the ss tool to verify the modification:

ss -nlt | grep 5432

The output shows that the PostgreSQL server is listening on all network interfaces (0.0.0.0):

LISTEN  02440.0.0.0:54320.0.0.0:*              
LISTEN  0244[::]:5432[::]:*

The next step is to configure the server to accept remote connections and edit the pg_hba.conf file.

Here are some examples showing different user scenarios:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# The user jane can access all databases from all locations using md5 password
host    all             jane            0.0.0.0/0                md5

# The user jane can access only the janedb from all locations using md5 password
host    janedb          jane            0.0.0.0/0                md5

# The user jane can access all databases from a trusted location(192.168.1.134) without a password
host    all             jane            192.168.1.134            trust

The last step is to open port 5432 on your firewall.

Assuming you are using UFW to manage your firewall, and you want to allow access from the 192.168.1.0/24 subnet, you should run the following command:

sudo ufw allow proto tcp from192.168.1.0/24 to any port 5432

Make sure that your firewall is configured and only accept connections from trusted IP ranges.

Six, summary##

We have shown you how to install and configure PostgreSQL on an Ubuntu 20.04 server. Browse PostgreSQL 12 Documentation for more information on this topic.

If you have any questions, please contact us in the following ways:

WeChat: sn0wdr1am86

WeChat group: add the above WeChat, remark the WeChat group

QQ: 3217680847

QQ Group: 82695646