How to Install PostgreSQL and phpPgAdmin on Ubuntu 20.04 LTS

PostgreSQL or Postgres is a powerful high-performance object-relational database management system (ORDBMS) released under a flexible BSD-style license. PostgreSQL is well suited for large databases and has many advanced features.

PostgreSQL is available for many operating systems including Linux, FreeBSD, Solaris, and Microsoft Windows. PhpPgAdmin is a PHP-based web application for managing PostgreSQL databases. With Phppgadmin, it is easy to create a database, create a role and create tables in Postgres.

This tutorial will show the installation of PostgreSQL and its web-based administration interface phpPgAdmin on Ubuntu 20.04 LTS (Bionic Beaver). I will use the Ubuntu minimal server as a basis for this setup.

Prerequisites

• Ubuntu 20.04
• Root privileges

What we will do?

1. Install PostgreSQL and phpPgAdmin
2. Create New User PostgreSQL
3. Configure Apache2
4. Setup UFW Firewall
5. Testing

Step 1 - Install PostgreSQL, phpPgAdmin and All Dependencies

Before installing any packages on the Ubuntu system, update and upgrade all packages using the apt command below.

sudo apt update
sudo apt upgrade

And now we're ready for installing PostgreSQL, phpPgAdmin, and Apache2 packages.

PostgreSQL and PhpPgAdmin are available in the Ubuntu repository. So you just need to install them with the apt command.

sudo apt -y install postgresql postgresql-contrib phppgadmin

Once all installation is completed, start the PostgreSQL service and add it to the system boot.

systemctl start postgresql
systemctl enable postgresql

Next, check the PostgreSQL service using the following command.

systemctl status postgresql

As a result, the PostgreSQL service is up and running.

And all packages installation of PostgreSQL and phpPgAdmin has been completed.

Step 2 - Create New User PostgreSQL

PostgreSQL uses roles for user authentication and authorization, it just like Unix-Style permissions. By default, PostgreSQL creates a new user called "postgres" for basic authentication.

In this step, we will create a new PostgreSQL user which have privileges as a superuser, create a database, create role, and login. The new user will be used to login to the PostgreSQL through the 'phpPgAdmin', and to do that, we must log in to the PostgreSQL shell as a default 'postgres' user.

Login to the PostgreSQL shell using the command below.

sudo -i -u postgres psql

Next, create a new role 'hakase' with the password 'hakasepasspgsql' using the following query.

CREATE ROLE hakase WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'hakasepasspgsql';

Now check all available users on the PostgreSQL system.

\du

And you will get a new user 'hakase' on the list, now type '\q' to exit from the PostgreSQL shell.

As a result, a new user named 'hakase' has been created, and it will be able to login to the PostgreSQL server through the phpPgAdmin.

Step 3 - Configure Apache Web Server

In this step, we will configure the Apache configuration for phpPgAdmin, which is automatically generated during packages installation.

Goto the '/etc/apache2/conf-available' directory and edit the configuration file 'phppgadmin.conf' with vim by typing:

cd /etc/apache2/conf-available/
vim phppgadmin.conf

By default, the phpPgAdmin is accessible through the path URL 'phppgadmin'. For security reasons, we will change the default path URL of phppgadmin by changing the 'Alias' option.

Change the default path URL 'phppgadmin' with your own path as below.

Alias /pgsqladminlogin /usr/share/phppgadmin

No comment out the line '#Require local' by adding a # in front of the line and add below the line allow from all so that you can access from your browser.

Require all granted

Save and close.

Next, test the Apache configuration and make sure there is no error, then restart the Apache service.

apachectl configtest
systemctl restart apache2

As a result, the Apache configuration for phpPgAdmin has been complete.

Step 4 - Setup UFW Firewall

For this guide, we will run the PostgreSQL and Apache services under the UFW firewall.

Add the ssh, http, and https ports to the ufw firewall using the following command.

for svc in ssh http https
do
ufw allow $svc
done

Next, start and enable the UFW firewall.

ufw enable

And you will be asked to ensure that you want to run the UFW firewall service. Type 'y' and press Enter to start the firewall.

And the UFW firewall is up and running with the ssh, http, and https protocol on top of it.

Check the UFW firewall status using the command below.

ufw status numbered

Below is the result you will get.

Step 5 - Testing

Before going any further, check the PostgreSQL and Apache port services using the ss command below.

ss -plnt

And you will get the PostgreSQL port '5432' and the Apache port '80' is on the LISTEN state, both services is up and running.

Next, open your web browser and type the server IP address following with your custom path of phpPgAdmin.

http://10.5.5.32/pgsqladminlogin/

And you will get the default phpPgAdmin page as below.

Now click on the 'Server' tab and you will get the phpPgAdmin login page.

Type the PostgreSQL user and password that created on top and click the 'Login' button.

And you will get this phpPgAdmin dashboard interface.

Installation of the PostgreSQL database with phpPgAdmin on Ubuntu 20.04 LTS has been completed successfully.

Conclusion

PostgreSQL is an advanced object-relational database management system (ORDBMS). It is Open Source and has a large and active community. PostgreSQL provides the psql command-line program as primary front-end, which can be used to enter SQL queries directly or execute them from a file. phpPgAdmin is a web-based administration tool for PostgreSQL written in PHP that makes the administration of Postgres databases easier.