Table of Contents
<span class="warning"><p></p>
<div name="status-deprecated" data-unique="status-deprecated"></div><h2 id="status-deprecated"><strong>Status:</strong> Deprecated</h2>
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.
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 <i>username</i>
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: <i> enter the superuser’s password here</i>
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
