Linux

How to Install PostgreSQL on Ubuntu 20.04

How to Install PostgreSQL on Ubuntu 20.04

PostgreSQL or Postgres is an open source general purpose relational database management system with many advanced features that allow you to build fault tolerant environments or complex applications.

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

Precondition

To be able to install the package, you must be logged in as root or 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 repository is PostgreSQL version 10.4.

Run the following command to install the PostgreSQL server on Ubuntu:
sudo apt update
sudo apt install postgresql postgresql-contrib

We also installed the PostgreSQL contrib package which provides several additional features for the PostgreSQL database system.

After the installation is complete, the PostgreSQL service will start automatically. Use the psql tool to verify the installation by connecting to the PostgreSQL database server and printing the 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

There she is. PostgreSQL has been installed, and you can start using it.

The Role of PostgreSQL and Authentication Methods

Database access permissions in PostgreSQL are handled with the concept of roles. Roles can represent database users or groups of database users.

PostgreSQL supports many authentication methods. The most commonly used methods are:

  • Trust – Roles can be connected without a password, as long as the conditions specified in pg_hba.conf are fulfilled.
  • Password – Roles can be linked by providing a password. Passwords can be saved as scram-sha-256, md5, and passwords (clear text).
  • Ident – Only supported on TCP / IP connections. It works by getting the client operating system user name, by mapping optional user names.
  • 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, PostgreSQL uses the peer authentication method for local connections.

Postgres users are automatically created 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 a postgres user, first switch to the user and then access the PostgreSQL prompt using the psql utility:
sudo su - postgres
psql

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

postgre=# \q

Another way to access the PostgreSQL prompt without redirecting users, is to use the sudo command:

sudo -u postgres psql

In general, you must enter the database server as postgres only from localhost.

Creating PostgreSQL Roles and Databases

Only super administrators and roles with CREATEROLE privileges can create new roles.

The following example shows how to create a new role named john database named johndb and give the database privileges:

1. Create a new PostgreSQL role:

sudo su - postgres -c "createuser john"

2. Create a new PostgreSQL Database:

sudo su - postgres -c "createdb johndb"

To give a user permission to the database, connect to the PostgreSQL shell:

sudo -u postgres psql

and run the following query:

postgres=# grant all privileges on database johndb to john;

Enable Remote Access to the PostgreSQL server

By default, the PostgreSQL server list is only on the local interface (127.0.0.1).

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

sudo nano /etc/postgresql/12/main/postgresql.conf

/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

Verify changes with the ss utility:

ss -nlt | grep 5432

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

Output :

LISTEN  0        244              0.0.0.0:5432           0.0.0.0:*              
LISTEN  0        244                 [::]:5432              [::]:* 

The next step is to configure the server to accept remote connections by editing the pg_hba.conf file.

Here are a few examples that show various use cases:

/etc/postgresql/12/main/pg_hba.conf

# 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 final 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 will run the following command:

PostgreSQL or Postgres is an open-source general-purpose object-relational database management system with many advanced features that allows you to build fault-tolerant environments or complex applications.

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

Prerequisites

To be able to install packages, you need to be logged in as root or 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.

Run the following commands to install PostgreSQL server on Ubuntu:

sudo apt updatesudo apt install postgresql postgresql-contrib

We’re also installing the PostgreSQL contrib package that provides several additional features for the PostgreSQL database system.

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

sudo -u postgres psql -c "SELECT version();"
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.

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 methods are:

  • Trust – A role can connect without a password, as long as the conditions 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 – Only supported on TCP/IP connections. It works by obtaining the client’s operating system user name, with an optional user name mapping.
  • Peer – Same as Ident, but it is supported on local connections only.

PostgreSQL client authentication is defined in the configuration file named pg_hba.conf. By default, PostgreSQL is using the peer authentication method for local connections.

The postgres user is automatically created 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 switch to the user and then access the 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

Another way to access the PostgreSQL prompt without switching users, is to use the sudo command:

sudo -u postgres psql

Generally, you should log in to the database server as postgres only from the localhost.

Creating PostgreSQL Role and Database

Only superusers and roles with CREATEROLE privilege can create new roles.

The following example shows how to create a new role named john a database named johndb and grant privileges on the database:

  1. Create a new PostgreSQL role:
    sudo su - postgres -c "createuser john"
  2. Create a new PostgreSQL Database:
    sudo su - postgres -c "createdb johndb"

To grant permissions to the user on the database, 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 nano /etc/postgresql/12/main/postgresql.conf
/etc/postgresql/12/main/postgresql.conf
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

listen_addresses = '*'     # what IP address(es) to listen on;

Copy

Save the file and restart the PostgreSQL service:

sudo service postgresql restart

Verify the changes with the ss utility:

ss -nlt | grep 5432

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

LISTEN  0        244              0.0.0.0:5432           0.0.0.0:*              
LISTEN  0        244                 [::]:5432              [::]:* 

The next 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:

/etc/postgresql/12/main/pg_hba.conf
# 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 the port 5432 in 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 would run the following command:

sudo ufw allow proto tcp from 192.168.1.0/24 to any port 5432

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

Conclusion

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

If you have questions, please leave a comment below.

Related posts

How to Launch a Terminal Window on Ubuntu Linux

Linux

How to Install the NoSQL Apache CouchDB Database on CentOS 8

Linux

How to Install Jenkins on Ubuntu 20.04 with Nginx and SSL

Linux

How to Install PostgreSQL and phpPgAdmin on Ubuntu 20.04 LTS

Linux

How to Install Yarn on Ubuntu 20.04

Linux

How to Use Sudo Commands on Linux

Linux

How to Grep Way for Various Strings and Patterns

Linux

How to run Linux on an Android device

Howto

How to Install Apache Cassandra on Ubuntu 20.04

Linux