Home » Ubuntu » How to Install PhpPgAdmin on Ubuntu 20.04

How to Install PhpPgAdmin on Ubuntu 20.04

PhpPgAdmin is a fully managed web-based administration tool for the PostgreSQL database server. It can handle all the basic functionality and advanced features to manipulate database information.

In this article, we are going to learn how to install PhpPgAdmin on the Ubuntu 20.04 server.

Prerequisites

Installation of PhpPgAdmin on ubuntu server requires some basic requirements which are as followings.

  1. Fresh installed Ubuntu 20.04 server
  2. Sudo Privileged user account
  3. Internet connection to download packages.

Update ubuntu server

As recommended you need to update your system's package repository to the latest version.

To update the Ubuntu repository index, run the following command:

$ sudo apt-get update -y

Install PostgreSQL on Ubuntu

Lets first install PostgreSQL. PostgreSQL repository is available on Ubuntu 20.04 by default. To install PostgreSQL on Ubuntu using apt, type:

$ sudo apt install postgresql postgresql-contrib  -y

postgresql-contrib is a package which provides additional utilities and functionality.

After installation of PostgreSQL will be started automatically, In case not started, use the following command:

$ sudo systemctl start postgresql.service

By default, the Postgres service is not set up to start automatically after a system reboot. To enable the Postgres service to automatically start after a system reboot, run the following command:

$ sudo systemctl enable postgresql.service

To verify PostgreSQL status, run the following command:

$ sudo systemctl status postgresql.service
PostgreSQL service status

Setup a Database for PhpPgAdmin

You will need to set up a database and create a user for PhpPgAdmin. Login into postgres user account and type psql to enter the PostgreSQL shell.

$ sudo su - postgres
$ psql

Once login you will get the following output.

PostgreSQL Shell

In the next step, you need to create a database and user for PhpPgAdmin. Let us create a username jerry with the password mystrongpassword.

postgres=# CREATE USER jerry WITH PASSWORD 'mystrongpassword'

Create a database (eg. pgadmindb) using the following command.

postgres=# CREATE DATABASE pgadmindb;

To access and control the database you should have grant privileges. Here I am going to grant all privileges on database pgadmindb to user jerry.

postgres=# GRANT ALL PRIVILEGES ON DATABASE pgadmindb TO jerry;

To exit from the PostgreSQL shell, run the following command.

postgres=# \q

Install PhpPgAdmin on Ubuntu

PhpPgAdmin is available in the default repository of Ubuntu 20.04 by default. You can install it with following apt command:

$ sudo apt-get install phppgadmin -y

PhpPgAdmin is accessible only from localhost by default. To make it accessible externally, you need to make changes in the apache configuration file. Edit the file /etc/apache2/conf-available/phppgadmin.conf configuration file with your favorite text editor.

$ sudo nano /etc/apache2/conf-available/phppgadmin.conf

Find the following line in the configuration file.

Require local

Replace it with the following line and save configuration file.

Require all granted 

Apache configuration for external access

Check apache configuration for any error with following command:

$ sudo apachectl configtest

You will see following output for correct configuration.

Syntax OK

Now restart apache service to reflect the changes made.

$ sudo systemctl restart apache2

Setup UFW Firewall

If you have enabled the UFW firewall in your ubuntu machine, you may need to allow some service to access PhpPgAdmin. To allow HTTP and HTTPS service for an incoming connection, run the following command:

$ sudo ufw allow http
$ sudo ufw allow https

Verify the firewall rules by running the command:

$ sudo ufw status verbose
UFW firewalls status

Access PhpPgAdmin

Open your favorite web browser and access the PhpPgAdmin web interface using the URL http://server-ip/phppgadmin.

You will be able to see the following screen:

PhpPgAdmin Web Interface

Click on the Servers=> PostgreSQL. You will get the PhpPgAdmin login screen as:

PhpPgAdmin Login screen

Enter your PhpPgAdmin username, a password that was created previously in the above step, and click on the Login button. Finally, you can see the PhpPgAdmin dashboard as follow:

PhpPgAdmin Dashboard showing databases

From this dashboard you can create new database and run any query for PostgreSQL database server.

Conclusion

In this article, we learned how to install PhpPgAdmin on Ubuntu 20.04 including some basic configurations. We also learned to configure the UFW firewall which accepts incoming HTTP connections.

Leave a Comment