How to Set up PhpPgAdmin for PostgreSQL?

23 / Feb / 2017 by Anup Yadav 1 comments

posgresql-and-phppgadmin-in-ubuntu-server-13.10

PostgreSQL is a powerful, open source object-relational database system (ORDBMS). The version 8.0 and above also comes with the native Windows compatibility.
Compared to other RDBMSs, PostgreSQL differs itself with its object-oriented and/or relational database functionality, such as the complete support for reliable transactions, i.e. Atomicity, Consistency, Isolation, Durability (ACID).

What happens if we want to manage the PostgreSQL databases from a graphical interface and not the command line?
We can use phpPgAdmin which is a is a web-based administration tool for PostgreSQL. PhpPgAdmin is an administration interface for PostgreSQL written in PHP. With PhpPgAdmin, we can administer a remote PostgreSQL Server.

Now let’s go step by step with PostgreSQL + phpPgAdmin running with Nginx on an Ubuntu 14.04 server. PhpPgAdmin can use Apache2 and Php5.

Steps to follow:

Step 1: Installing PhpPgAdmin
Install PhpPgAdmin using the below commands:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib phppgadmin php5-fpm

By default, the installation process configures the Apache2 server.

Step 2: Setting up PostgreSQL-9.6.1
Installing PostgreSQL on our Ubuntu 14.04 machine

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install postgresql-9.6

Step 3: Configuring a Read Only user/role for PostgreSQL
Creating a read only user/role on PostgreSQL for a particular database

CREATE ROLE <postgres_user> WITH LOGIN PASSWORD '<password>' NOSUPERUSER INHERIT NOCREATEDB NOREPLICATION NOCREATEROLE  VALID UNTIL 'infinity';
GRANT CONNECT ON DATABASE <database_name> TO <postgres_user>;
GRANT USAGE ON SCHEMA public TO <postgres_user>;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO <postgres_user>;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO <postgres_user>;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO <postgres_user>;

Step 4: Configuring Apache
Now, editing the following lines in the file /etc/apache2/sites-enabled/000-default.conf

ServerAdmin localhost
DocumentRoot /usr/share/phppgadmin/index.php
ServerName localhost

Adding the below line in the file /etc/apache2/apache2.conf :
Include /etc/apache2/conf.d/phppgadmin

Inside /etc/apache2/conf.d/phppgadmin file, it must contain

order deny,allow
deny from all
allow from 127.0.0.0/255.0.0.0 ::1/128
allow from all

Now, Restarting our Apache Web Server

sudo service apache2 restart

Step 5: Tweaking some parameters for PhpPgAdmin

Editing the below line in the file/etc/apache2/sites-enabled/000-default.conf :
conf[‘extra_login_security’] = true;
to
$conf[‘extra_login_security’] = false;

Step6: Getting started with our PhpPgAdmin GUI for PostgreSQL
Browse on web browser http://localhost:8080/phppgadmin Screenshot from 2017-02-20 00:18:48

Step7: Adding New Remote PostgreSQL Server (Optional)
Now, If we want to add a new server to our PhpPgAdmin then it can be done by adding the below line to the file /etc/phppgadmin/config.inc. file:

$conf['servers'][1]['desc'] = 'New_Postgres_Server';
$conf['servers'][1]['host'] = '<ip-address/hostname>';
$conf['servers'][1]['port'] = 5432;
//$conf['servers'][1]['sslmode'] = 'allow';
$conf['servers'][1]['pg_dump_path'] = '/usr/bin/pg_dump';
$conf['servers'][1]['defaultdb'] = 'template1';
$conf['servers'][1]['pg_dumpall_path'] = '/usr/bin/pg_dumpall';

Now, we can log in using the PostgreSQL user we created in Step 3 into the PhpPgAdmin.
phppgadmin-login

You can now easily set up PhpPgAdmin for PostgreSQL.

FOUND THIS USEFUL? SHARE IT

comments (1 “How to Set up PhpPgAdmin for PostgreSQL?”)

Leave a comment -