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?
- Install PostgreSQL and phpPgAdmin
- Create New User PostgreSQL
- Configure Apache2
- Setup UFW Firewall
- 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 upgradeAnd 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 phppgadminOnce all installation is completed, start the PostgreSQL service and add it to the system boot.
systemctl start postgresql
systemctl enable postgresqlNext, check the PostgreSQL service using the following command.
systemctl status postgresqlAs 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 psqlNext, 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.
duAnd 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.confBy 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/phppgadminNo 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 grantedSave and close.
Next, test the Apache configuration and make sure there is no error, then restart the Apache service.
apachectl configtest
systemctl restart apache2As 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
doneNext, start and enable the UFW firewall.
ufw enableAnd 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 numberedBelow 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 -plntAnd 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.
