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.
- Fresh installed Ubuntu 20.04 server
- Sudo Privileged user account
- 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
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.
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
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
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:
Click on the Servers=> PostgreSQL. You will get the PhpPgAdmin login screen as:
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:
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.
