How to Set up PhpPgAdmin for PostgreSQL?
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:
[js]
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib phppgadmin php5-fpm
[/js]
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
[js]
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
[/js]
Step 3: Configuring a Read Only user/role for PostgreSQL
Creating a read only user/role on PostgreSQL for a particular database
[js]
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>;
[/js]
Step 4: Configuring Apache
Now, editing the following lines in the file /etc/apache2/sites-enabled/000-default.conf
[js]
ServerAdmin localhost
DocumentRoot /usr/share/phppgadmin/index.php
ServerName localhost
[/js]
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
[js]
order deny,allow
deny from all
allow from 127.0.0.0/255.0.0.0 ::1/128
allow from all
[/js]
Now, Restarting our Apache Web Server
[js]sudo service apache2 restart[/js]
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 
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:
[js]
$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’;
[/js]
Now, we can log in using the PostgreSQL user we created in Step 3 into the PhpPgAdmin.
You can now easily set up PhpPgAdmin for PostgreSQL.
And the Step 3 ? Do we enter the commands or?