Install phpPgAdmin On CentOS 7

phpPgAdmin is a widely used PostgreSQL management tool. You can use it to manage PostgreSQL databases in an intuitive web interface.

In this article, we will cover the necessary steps to install phpPgAdmin on a Vultr CentOS 7 x64 server instance.

Before you move on, you need to:

1. Deploy a new Vultr CentOS 7 x64 server instance.
2. Log in the server instance with a sudo user.

Step 1: Update the system to the latest status

On CentOS 7, it is always recommended to update the system to the latest status using yum:

sudo yum update
sudo reboot

Note: You only need to reboot your instance if kernel updates were applied.

Once the server reboots, log in again using the same sudo user.

Step 2: Install Apache Web Server

Since phpPgAdmin is a web-based tool, a web server is required for it to run.
For the purpose of this tutorial, we will installing Apache. You are however free to pick your Niginx or Lighttpd if you prefer.

sudo yum install httpd
sudo systemctl start httpd.service
sudo systemctl enable httpd.service

Step 3: Configure the firewall

You need to modify the default firewall configuration before you can access phpPgAdmin from a web browser:

sudo firewall-cmd --zone=public --permanent --add-service=http
sudo firewall-cmd --zone=public --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

Then you can visit http://[YourServerIP] from your browser to confirm your configuration.

Note: For your information, SELinux is disabled on the Vultr CentOS 7 x64 server instance by default. If you turn it on manually, you need also configure SELinux as below:

sudo setsebool -P httpd_can_network_connect on
sudo setsebool -P httpd_can_network_connect_db on

Step 4: Install PHP 5 and the necessary extensions

phpPgAdmin is written in PHP, you need to install PHP 5 and some extensions to serve phpPgAdmin.

sudo yum install php php-pgsql

Step 5: Install and configure PostgreSQL

On the phpPgAdmin official website, find the latest stable version of PostgreSQL for CentOS 7 x64 and its URL. As of writing, the latest stable version of PostgreSQL is 9.5.

5.1) Use the following commands to install PostgreSQL 9.5 on your CentOS 7 server:

sudo yum install http://yum.postgresql.org/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm
sudo yum groupinstall "PostgreSQL Database Server 9.5 PGDG"

5.2) Initiate the database:

sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb

5.3) Setup database user authentication method:

sudo vi /var/lib/pgsql/9.5/data/pg_hba.conf

Find the following section:

# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                ident

Modify the authentication method of IPv4 local connections to md5:

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Save and quit:

:wq

5.4) Setup PostgreSQL listening addresses:

sudo vi /var/lib/pgsql/9.5/data/postgresql.conf

Find:

#listen_addresses = 'localhost'

modify it to:

listen_addresses = '*'

Find:

#port = 5432

modify it to:

port = 5432

Save and quit:

:wq

5.5) Start the PostgreSQL service:

sudo systemctl start postgresql-9.5.service
sudo systemctl enable postgresql-9.5.service

5.6) Setup database user credentials:

By default, PostgreSQL program will create a database user “postgres”. For security purposes, however, you need to create another database user for remote log in.

sudo -u postgres psql

In the psql shell:

CREATE USER pgdbuser CREATEDB CREATEUSER ENCRYPTED PASSWORD 'pgdbpass';
CREATE DATABASE mypgdb OWNER pgdbuser;
GRANT ALL PRIVILEGES ON DATABASE mypgdb TO pgdbuser;
/q

Step 6: Install and Use phpPgAdmin

Install phpPgAdmin with the following command:

sudo yum install phpPgAdmin

Then configure phpPgAdmin as accessible from outside:

sudo vi /etc/httpd/conf.d/phpPgAdmin.conf

Replace:

Require local

with:

Require all granted

Replace:

Deny from all

with:

Allow from all

Save and quit:

:wq

Modify the config.inc.php file:

sudo vi /etc/phpPgAdmin/config.inc.php

Find the line:

$conf['servers'][0]['host'] = '';

Replace the line with:

$conf['servers'][0]['host'] = 'localhost';

Find the line:

$conf['owned_only'] = false;

Replace the line with:

$conf['owned_only'] = true;

Save and quit:

:wq

Reload PostgreSQL and httpd services:

sudo systemctl start postgresql-9.5.service
sudo systemctl reload httpd.service

That’s all. Now you can visit phpPgAdmin from your browser: http://[YourServerIP]/phpPgAdmin/. Use the username “pgdbuser” and the password “pgdbpass” we setup earlier to log in.