How To Install phpPgAdmin on Ubuntu 18.04 / 16.04 & LinuxMint 19 / 18

phpPgAdmin is a web-based administration tool for managing PostgreSQL database, and it is very similar to phpMyAdmin, a web-based based tool for managing MySQL (MariaDB).

If you have work experience on phpMyAdmin, then it’s very easy for you to understand the functionalities of phpPgAdmin.

This guide will help you to install phpPgAdmin on Ubuntu 18.04 / Ubuntu 16.04 & LinuxMint 19 / Linux Mint 18.

Prerequisites

Before setting up phpPgAdmin, take a look at how to install PostgreSQL on Ubuntu 18.04 / Ubuntu 16.04 & LinuxMint 19 / Linux Mint 18.

Check the PostgreSQL service is running on the server.

sudo systemctl status postgresql

Output:

● postgresql.service - PostgreSQL RDBMS
   Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
   Active: active (exited) since Sat 2019-02-23 11:43:07 IST; 23h ago
 Main PID: 6536 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 2323)
   CGroup: /system.slice/postgresql.service

Feb 23 11:43:07 server systemd[1]: Starting PostgreSQL RDBMS...
Feb 23 11:43:07 server systemd[1]: Started PostgreSQL RDBMS.

Install phpPgAdmin

phpPgAdmin is available in the base repository, so, you can install it using apt-get install command.

sudo apt-get install -y  phppgadmin apache2

Configure PostgreSQL

By default, PostgreSQL accepts the authentication from localhost only. If you want to connect PostgreSQL from external machines, you would need to edit the pg_hba.conf file.

### PostgreSQL 11 ###

sudo nano /etc/postgresql/11/main/pg_hba.conf

### PostgreSQL 10 ###

sudo nano /etc/postgresql/10/main/pg_hba.conf

Enter the value as per your requirements in IPv4 and make sure it accepts the md5 password.

# IPv4 local connections:
host    all             all             127.0.0.1/32            md5

host    all             all             192.168.1.0/24          md5 

Configure phpPgAdmin

Edit /etc/phppgadmin/config.inc.php file.

sudo nano /etc/phppgadmin/config.inc.php

Add your PostgreSQL instances here.

        // Display name for the server on the login screen
        $conf['servers'][0]['desc'] = 'PostgreSQL 11';
       

        // Hostname or IP address for server.  Use '' for UNIX domain socket.
        // use 'localhost' for TCP/IP connection on this computer
        $conf['servers'][0]['host'] = 'localhost';
        

        // Database port on server (5432 is the PostgreSQL default)
        $conf['servers'][0]['port'] = 5432;

phpPgAdmin won’t allow users with no password or certain usernames (pgsql, postgres, root, administrator) to log in.

To override this extra security, change it to false.

$conf['extra_login_security'] = false;

Setting this true simply hide other users databases from the database list. However, they can get the data (from other databases) using SQL queries.

$conf['owned_only'] = false;

Configure Apache

Due to restriction, phpPgAdmin is accessible only on localhost. If you want to access phpPgAdmin web interface from external machines, then you need to edit the apache configuration (phppgadmin.conf) file.

sudo nano /etc/apache2/conf-enabled/phppgadmin.conf

Default config will look like below.

Alias /phppgadmin /usr/share/phppgadmin


DirectoryIndex index.php

AllowOverride None

# Only allow connections from localhost:
Require local


  php_flag magic_quotes_gpc Off
  php_flag track_vars On
  #php_value include_path .

      AddType application/x-httpd-php .php
      Action application/x-httpd-php /cgi-bin/php
    
     AddType application/x-httpd-php .php
     Action application/x-httpd-php /cgi-bin/php
    

Please comment out the Require local line and add Require all granted just below to the commented line.

It will look like below.

Alias /phppgadmin /usr/share/phppgadmin


DirectoryIndex index.php

AllowOverride None

# Only allow connections from localhost:
# Require local
Require all granted

  php_flag magic_quotes_gpc Off
  php_flag track_vars On
  #php_value include_path .

       AddType application/x-httpd-php .php
       Action application/x-httpd-php /cgi-bin/php
    
       AddType application/x-httpd-php .php
       Action application/x-httpd-php /cgi-bin/php
    

Restart the services.

sudo systemctl restart postgresql
sudo systemctl restart apache2

Access phpPgAdmin

Now access the phpPgAdmin from your web browser, URL will be

http://your-ip-address/phppgadmin

OR:

http://localhost/phppgamin

phpPgAdmin’s initial page:

Work with phpPgAdmin

Click the server listed in the left pane to login into the PostgreSQL instance. Log in using the database user.

You will now get the list of databases.

That’s All.