Tutorial

How To Install and Use phpPgAdmin on Ubuntu 12.04

Published on October 25, 2012
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:

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 phpPgAdmin

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

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about us


About the authors

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
10 Comments


This textbox defaults to using Markdown to format your answer.

You can type !ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

We had to update to php 5.5 and in the process updated Apache to 2.4.9. In the latest version of Apache the conf files need to be in conf-enabled.

See the answer here:

http://askubuntu.com/a/452110 <pre> sudo cp /etc/apache2/conf.d/phppgadmin /etc/apache2/conf-enabled/phppgadmin.conf sudo service apache2 restart </pre> Also, as a side note, your virtual hosts (in sites-enabled) should end in .conf, too.

We also need pgamin 4 for nginx ubuntu servers.

Hi, I have created some tables in postgres and I want to view them in pgadmin3. I created a new user on on postgres using: $ createuser --interactive $sudo -u lisam postgres <<— allows me to login and create tables as lisam=#

Does anyone know how I link my new user with pgadmin3?

Some users may not be able to access “ipaddress/phppgadmin” directly on a fresh install. Kindly add the following commands in your tutorial. sudo cp /etc/apache2/conf.d/phppgadmin /etc/apache2/conf-enabled/phppgadmin.conf sudo /etc/init.d/apache2 restart

In the section Configure the .htaccess Authentication

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

has to be

sudo nano /etc/apache2/sites-available/000-default.conf

When running this line:

"sudo htpasswd -c /etc/phppgadmin/./htpasswd username’

I get the following error message:

“sudo: htpasswd: command not found”.

Any fix to this? I put the changes of <Directory> in a file within /etc/apache2/sites-enabled/000-default.conf rather than just 000-default, as it didn’t exist

This step seems to be missing from the instructions…

For Ubuntu 14.10 edit /etc/apache2/apache2.conf and add the following line to it:

Include /etc/apache2/conf.d/phppgadmin

For older Ububtu releases only: to set up under Apache all you need to do is include the following line in /etc/apache2/apache2.conf.

Include /etc/phppgadmin/apache.conf

Hi, i’m at “Accessing phpPgAdmin” step and i’m getting the 404 error when attempting to visit the site at <myIP>/phppgadmin.

I put the “Directory” tag into the “/etc/apache2/sites-enabled/000-default.conf” file, instead of “/etc/apache2/sites-enabled/000-default” because “000-default” file does not exist. Is it correct?

Also, I put the “Directory” tag after de “VirtualHost” tag. Is it correct too?

I’m using ‘phppgadmin’ instead of ‘phpPgAdmin’ in all cases.

Any help?

This comment has been deleted

    Many people have commented that sudo htpasswd -c /etc/phpPgAdmin/.htpasswd username does not work.

    For later versions of postgres the directory is /etc/phppgadmin/ (note the lowercase) So substitute “phpPgAdmin” with lower case letters “phppgadmin” wherever you find it in the tutorial and everything should be OK.

    Try DigitalOcean for free

    Click below to sign up and get $200 of credit to try our products over 60 days!

    Sign up

    Join the Tech Talk
    Success! Thank you! Please check your email for further details.

    Please complete your information!

    Get our biweekly newsletter

    Sign up for Infrastructure as a Newsletter.

    Hollie's Hub for Good

    Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

    Become a contributor

    Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

    Welcome to the developer cloud

    DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

    Learn more
    DigitalOcean Cloud Control Panel