Linux, Windows, Virtualization, OpenSource & Blogging

How To Install phpPgAdmin on Debian 9 / Ubuntu 16.04 / LinuxMint 18

0

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 a 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 Debian 9 / Ubuntu 16.04 / LinuxMint 18.

Install PostgreSQL

Before setting up phpPgAdmin, take a look at how to install PostgreSQL on Debian 9 / Ubuntu 16.04 / LinuxMint 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 Thu 2017-09-21 11:15:41 CDT; 2 days ago
 Main PID: 829 (code=exited, status=0/SUCCESS)
    Tasks: 0 (limit: 4915)
   CGroup: /system.slice/postgresql.service

Sep 21 11:15:41 mydebian systemd[1]: Starting PostgreSQL RDBMS...
Sep 21 11:15:41 mydebian 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

PostgreSQL listens on loopback adapter (127.0.0.1) only. Sometimes the application hosted on external may need to connect to the database. For that, we need to configure PostgreSQL to listen on all adaptors.

nano /etc/postgresql/9.6/main/postgresql.conf

Update the below line,

FROM:

#listen_addresses = 'localhost'

TO:

Change 192.168.1.6 with your ipaddress.

listen_addresses = '192.168.1.5'

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.

sudo nano /etc/postgresql/9.6/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 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

<Directory /usr/share/phppgadmin>

<IfModule mod_dir.c>
DirectoryIndex index.php
</IfModule>
AllowOverride None

# Only allow connections from localhost:
Require local

<IfModule mod_php.c>
 php_flag magic_quotes_gpc Off
 php_flag track_vars On
 #php_value include_path .
</IfModule>
<IfModule !mod_php.c>
 <IfModule mod_actions.c>
 <IfModule mod_cgi.c>
 AddType application/x-httpd-php .php
 Action application/x-httpd-php /cgi-bin/php
 </IfModule>
 <IfModule mod_cgid.c>
 AddType application/x-httpd-php .php
 Action application/x-httpd-php /cgi-bin/php
 </IfModule>
 </IfModule>
</IfModule>

</Directory>

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

<Directory /usr/share/phppgadmin>

<IfModule mod_dir.c>
DirectoryIndex index.php
</IfModule>
AllowOverride None

# Only allow connections from localhost:
# Require local
Require all granted
<IfModule mod_php.c>
 php_flag magic_quotes_gpc Off
 php_flag track_vars On
 #php_value include_path .
</IfModule>
<IfModule !mod_php.c>
 <IfModule mod_actions.c>
 <IfModule mod_cgi.c>
 AddType application/x-httpd-php .php
 Action application/x-httpd-php /cgi-bin/php
 </IfModule>
 <IfModule mod_cgid.c>
 AddType application/x-httpd-php .php
 Action application/x-httpd-php /cgi-bin/php
 </IfModule>
 </IfModule>
</IfModule>

</Directory

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:

Install phpPgAdmin on Debian 9 - phpPgAdmin Page
Install phpPgAdmin on Debian 9 – phpPgAdmin Page

Do not login. We have few configurations to be set.

Configure phpPgAdmin

If you want to manage multiple PostgreSQL instances from a single web console then follow this step. Otherwise, you can skip this step.

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 - Debian';
        $conf['servers'][1]['desc'] = 'PostgreSQL - Ubuntu';

        // 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';
        $conf['servers'][1]['host'] = '192.168.1.6';

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

        // Database SSL mode
        // Possible options: disable, allow, prefer, require
        // To require SSL on older servers use option: legacy
        // To ignore the SSL mode, use option: unspecified
        $conf['servers'][0]['sslmode'] = 'allow';
        $conf['servers'][1]['sslmode'] = 'allow';

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

To override this extra security, change it to false.

$conf['extra_login_security'] = false;

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

$conf['owned_only'] = true;

Work with phpPgAdmin

Now, refresh the page to see the changes you made to phpPgAdmin.

Install phpPgAdmin on Debian 9 - phpPgAdmin Page with Multiple PostgreSQL Instances
Install phpPgAdmin on Debian 9 – phpPgAdmin Page with Multiple PostgreSQL Instances

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

Install phpPgAdmin on Debian 9 - Login Page of PostgreSQL Instance
Install phpPgAdmin on Debian 9 – Login Page of PostgreSQL Instance

You will now get the list of databases.

Install phpPgAdmin on Debian 9 - Database List
Install phpPgAdmin on Debian 9 – Database List

Same way you can login to other instances as well. Logged in into multiple PostgreSQL instances using phpPgAdmin.

Install phpPgAdmin on Debian 9 - Logged in into Multiple PostgreSQL Instances
Install phpPgAdmin on Debian 9 – Logged in into Multiple PostgreSQL Instances

That’s All.

 

You might also like

How To Install phpPgAdmin on Debian 9 / Ubuntu 16.04 / LinuxMint 18

0