PostgreSQL or Postgres is a powerful high-performance object-relational database management system (ORDBMS) that is released under a flexible BSD style license. PostgreSQL is perfect for large databases and has many powerful 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’s easy to create databases, create roles, and create tables in Postgres.
This tutorial will show the PostgreSQL installation and phpPgAdmin web-based administration interface on Ubuntu 20.04 LTS (Bionic Beaver). I will use a minimum Ubuntu server as a base for this setting.
- Ubuntu 20.04
- Rooting privileges
What we will do?
- Install PostgreSQL and phpPgAdmin
- Create New PostgreSQL Users
- Configure Apache2
- UFW Firewall Settings
Step 1 – Install PostgreSQL, phpPgAdmin and All Dependencies
Before installing any package on an Ubuntu system, update and update all packages using the apt command below.
sudo apt update sudo apt upgrade
And now we are ready to install the PostgreSQL, phpPgAdmin, and Apache2 packages.
PostgreSQL and PhpPgAdmin are available in the Ubuntu repository. So, you only need to install it with the apt command.
sudo apt -y install postgresql postgresql-contrib phppgadmin
After all installation is complete, 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 PostgreSQL and phpPgAdmin installation packages are complete.
Step 2 – Create a New PostgreSQL User
PostgreSQL uses roles for user authentication and authorization, 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 who has privileges as a superuser, create a database, create roles, and log in. New users will be used to log in to PostgreSQL through ‘phpPgAdmin’, and to do that, we must enter the PostgreSQL shell as the default ‘postgres’ user.
Log in to the PostgreSQL shell using the command below.
sudo -i -u postgres psql
Next, create a new ‘hakase’ role with the password ‘hakasepasspgsql’ using the following request.
CREATE ROLE hakase WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'hakasepasspgsql';
Now check all the users available on the PostgreSQL system.
And you will get a new user ‘hakase’ in the list, now type ‘\ q’ to exit the PostgreSQL shell.
As a result, a new user named ‘hakase’ has been created, and it will be able to enter the PostgreSQL server via phpPgAdmin.
Step 3 – Configure Apache Web Server
In this step, we will configure the Apache configuration for phpPgAdmin, which is automatically generated during package installation.
Enter the ‘/ etc / apache2 / conf-available’ directory and edit the ‘phppgadmin.conf’ configuration file with vim by typing:
cd /etc/apache2/conf-available/ vim phppgadmin.conf
By default, phpPgAdmin can be accessed via the ‘phppgadmin’ URL path. For security reasons, we will change the default phppgadmin path URL by changing the ‘Alias’ option.
Change the default path URL ‘phppgadmin’ with your own path as below.
Alias /pgsqladminlogin /usr/share/phppgadmin
There are no comments outside the ‘#Local local’ line by adding # in front of the line and adding below the line that allows it from all so you can access it from your browser.
Require all granted
Save and close.
Next, test the Apache configuration and make sure there are no errors, then restart the Apache service.
apachectl configtest systemctl restart apache2
As a result, the Apache configuration for phpPgAdmin has been completed.
Step 4: Setup UFW Firewall
For this guide, we will run PostgreSQL and Apache services under the UFW firewall.
Add 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 activate the UFW firewall.
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 already active with the SSH protocol, http, and https on it.
Check the UFW firewall status using the command below.
ufw status numbered
Step 5 – Testing
Before proceeding further, check PostgreSQL and Apache port services using the ss command below.
And you will get PostgreSQL port ‘5432’ and Apache port ’80’ in LISTEN status, both services are already running and running.
Next, open your web browser and type the following server IP address with your custom phpPgAdmin path.
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 created above and click the ‘Login‘ button.
And you will get this phpPgAdmin dashboard interface.
The PostgreSQL database installation with phpPgAdmin on Ubuntu 20.04 LTS has been completed successfully.
PostgreSQL is a sophisticated 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 the main front-end, which can be used to enter SQL queries directly or run them from a file. phpPgAdmin is a web-based administration tool for PostgreSQL written in PHP that makes Postgres database administration easier.