How To Install and Use phpPgAdmin on Ubuntu 12.04

Status: Deprecated #

This article covers a version of Ubuntu that is no longer supported. If you are currently operate a server running Ubuntu 12.04, we highly recommend upgrading or migrating to a supported version of Ubuntu:

Upgrade to Ubuntu 14.04.
Upgrade from Ubuntu 14.04 to Ubuntu 16.04
Migrate the server data to a supported version

Reason:
Ubuntu 12.04 reached end of life (EOL) on April 28, 2017 and no longer receives security patches or updates. This guide is no longer maintained.
See Instead:
This guide might still be useful as a reference, but may not work on other Ubuntu releases. If available, we strongly recommend using a guide written for the version of Ubuntu you are using. You can use the search functionality at the top of the page to find a more recent version.

About phpPgAdmin #

phpPgAdmin is a php-based web application that provides a GUI interface for the postgresql system. It performs a similar function to phpMyAdmin, which allows users to manipulate database information in a visual program in MySQL.

Step One—Install phpPgAdmin #

Start off by ensuring that the apt-get repository is up to date:

sudo apt-get update

Once the process has completed, go ahead and install postgresql, helpful additional dependencies, and phpgadmin. During its installation, phpPgAdmin will also install the required php and apache packages.

sudo apt-get install postgresql postgresql-contrib phppgadmin

Start Apache:

sudo service apache2 start

Step Two—Adjust The Security Settings #

Once phpgadmin Is installed, you may be able to access it by going to youripaddress/phpPgAdmin.
You can run the following command to reveal your server’s IP address.

ifconfig eth0 | grep inet | awk '{ print $2 }'

You may find, however, that attempting to reach the phpPgAdmin page may result in a forbidden 403 error.
In order to make this page accessible, we should make it available to all visitors (don’t worry, we’ll lock it down in the next step):

sudo nano /etc/apache2/conf.d/phppgadmin

Within the file find the following section and uncomment the line, “allow from all”. The section should look like this:

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

Configure the .htaccess Authentication #

With the .htaccess file allowed, we can proceed to set up a native user whose login would be required to even access the phpPgAdmin login page.
Start by creating the .htaccess authentication section your site’s configuration file. For example’s sake, I will use the default site:

sudo nano /etc/apache2/sites-enabled/000-default

Follow up by setting up the user authorization. Create a new section within the virtual host file, pasting the following information in:

<Directory "/usr/share/phpPgAdmin">
        AuthUserFile /etc/phpPgAdmin/.htpasswd
        AuthName "Restricted Area"
        AuthType Basic
        require valid-user
</Directory>

Below you’ll see a quick explanation of each line
AuthUserFile: This line designates the server path to the password file (which we will create in the next step.)
AuthType: This refers to the type of authentication that will be used to the check the passwords. The passwords are checked via HTTP and the keyword Basic should not be changed.
AuthName: This is text that will be displayed at the password prompt. You can put anything here.
Require valid-user: This line tells the .htaccess file that only users defined in the password file can access the phpPgAdmin login screen.

Create the htpasswd file #

Now we will go ahead and create the valid user information.
Start by creating a htpasswd file. Use the htpasswd command, and place the file in a directory of your choice as long as it is not accessible from a browser. Although you can name the password file whatever you prefer, the convention is to name it .htpasswd.

sudo htpasswd -c /etc/phpPgAdmin/.htpasswd username

A prompt will ask you to provide and confirm your password.
Once the username and passwords pair are saved you can see that the password is encrypted in the file.
FInish up by restarting apache:

sudo service apache2 restart

Accessing phpPgAdmin #

phpPgAdmin will now be much more secure since only authorized users will be able to reach the login page. Accessing youripaddress/phpPgAdmin should display a screen like
Fill it in with the username and password that you generated. After you login you can access phpPgAdmin with your Postgres username and password.

Postscript—How to Create a Postgres User #

Change the authentication method in the Authentication Configuration File:

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

The change can be made in the following line:

# "local" is for Unix domain socket connections only
local   all             all                                    md5

To begin creating users, first switch into the default superuser and create the database that the user will log into:

sudo su - postgres

Subsequently, create a new database where you will store your tables:

createdb newdb

Although the database has been created, the only user with access to it is the default postgres user. We can allow other users to access and manipulate this database by creating new users.
Once logged in as the default superuser, you can move forward to create more roles in your PostgreSQL system.
To outfit your user with a password, you can add the option -P to the createuser command:

createuser -P

Enter name of role to add: newuser
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) y
Password:  enter the superuser’s password here

You can then log into postgres in one of several ways.
If you are logging in using the peer identification, you can simply type the following command, specifying the database that you are logging into:

psql newdb

If you are logging into using the md5 identification, you can include the user that you prefer to log in as:

psql –U newuser  -W newdb

By Etel Sverdlov