PostgreSQL or Postgres is a powerful, high performance object relational database management system (ORDBMS) released under a flexible BSD stillness. 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 shows 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 the basis for this setting.
- Ubuntu 20.04
- Root privileges
What we will do?
- Install PostgreSQL and phpPgAdmin
- Create new user PostgreSQL
- Configure Apache2
- Setup UFW firewall
Step 1 – Install PostgreSQL, phpPgAdmin and some dependencies4 packages on the Ubuntu system, update and upgrade all packages with the apt command below.
sudo apt update
sudo apt upgrade
sudo apt update
sudo apt upgrade
And now we are ready to install 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
When all installation is complete, start the PostgreSQL service and add it to the system startup.
systemctl start postgresql
systemctl enable postgresql
Then, check the PostgreSQL service with the following command.
systemctl status postgresql
As a result, the PostgreSQL service is running.
And all package installation of PostgreSQL and phpPgAdmin has been completed.
Step 2 - Create New User PostgreSQL
PostgreSQL uses user authentication and authorization roles, just like Unix-Style permissions. By default, PostgreSQL creates a new user named "postgres" for basic authentication.
In this step, we create a new PostgreSQL user who has privileges as a super user, creates a database, creates a role and logs in. The new user will be used to log in to PostgreSQL via & # 39; phpPgAdmin & # 39 ;, and in order to do so, we must log in to the PostgreSQL shell as the default postgres user.
Log in to the PostgreSQL shell with the command below.
sudo -i -u postgres psql
Then create a new role & # 39; hakase & # 39; with the password & # 39; chakaspspsql & # 39; with the following question.
CREATE ROLE hakase WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD & # 39; hakasepasspgsql & # 39 ;;
Now check all available users on the PostgreSQL system.
And you get a new user & # 39; hakase & # 39; on the list, now enter & # 39; q & # 39; to leave the PostgreSQL shell.
As a result, a new user named & # 39; hakase & # 39; created and it will be able to log into the PostgreSQL server via phpPgAdmin.
Step 3 - Configure Apache Web Server
In this step, we configure Apache configuration for phpPgAdmin, which is automatically generated during the package installation.
Go to directory & # 39; / etc / apache2 / conf-available & # 39; and edit the configuration file & # 39; phppgadmin.conf & # 39; with vim by typing:
cd / etc / apache2 / conf-available /
By default, phpPgAdmin is available through the URL: "phppgadmin". For security reasons, we will change the default path address for phppgadmin by changing the "Alias" option.
Change the default path address "phppgadmin" with your own path as below.
Alias / pgsqladminlogin / usr / share / phppgadmin
No comment from line & # 39; #Require local & # 39; by adding a # in front of the line and adding below the line allow from everyone so you can access from your browser.
Request all granted
Save and close.
Then test the Apache configuration and make sure there is no error, then restart the Apache service.
systemctl restart apache2
As a result, the apache configuration for phpPgAdmin has been completed.
Step 4 - Setting up the 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 with the following command.
for svc in ssh http https
ufw allow $ svc
Then the UFW firewall starts and activates.
And you will be asked to make sure you want to run the UFW firewall service. Type & # 39; y & # 39; and press Enter to start the firewall.
And the UFW firewall is running with ssh, http and https protocol on top.
Check the UFW firewall status with the command below.
ufw status numbered
Below is the result you will get.
Step 5 - Test
Before proceeding, check the PostgreSQL and Apache port services with the command ss below.
And you get the PostgreSQL port & # 39; 5432 & # 39; and Apache port & # 39; 80 & # 39; is in LISTEN mode, both services are running.
Then open your web browser and type the server IP address with your custom path of phpPgAdmin.
And you get the default phpP gAdmin page as below.
Now click on the & # 39; Server tab and you will get the phpPgAdmin login page.
Type the PostgreSQL user and password created on top and click the [ Login ].
And you will get this phpPgAdmin dashboard interface.
Installation of the PostgreSQL database with phpPgAdmin on Ubuntu 20.04 LTS has been successfully completed.
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 the primary front-end, which can be used to directly enter SQL queries or run them from a file. phpPgAdmin is a web-based management tool for PostgreSQL written in PHP that facilitates the administration of Postgres databases.