Author: Robert Bernier
There are three well-known open source clients for managing PostgreSQL databases: psql, pgAdmin, and phpPgAdmin. If you use Postgres in a collaborative team, however, you should get to know phpPgAdmin, which is expressly designed for such environments. It lets users and administrators create user accounts, databases, tables, sequences, functions, and triggers.
PhpPgAdmin is a Web-based application written in PHP that can manage one or more PostgreSQL databases. It is 100% compatible with PostgreSQL. It performs all the standard Data Definition Language (DDL) and Data Manipulation Language (DML) statements. It can back up and restore an entire cluster, and can manage a Slony replication cluster, all in an easy-to-understand interface.
Installation
Before you install phpPgAdmin you need a PHP-enabled Web server with the appropriate PHP connectivity module for Postgres.
Installing phpPgAdmin can be a painless task or it can involve a good deal of preparation and planning, depending on how you choose to install it — via your distribution’s package manager, manually, or under Windows.
Distro-based installs, using tools such as Debian’s APT, often tailor the configuration files in order to further secure the system. For example, the Web server may block connection attempts to the locally installed Postgres server if the URL is neither http://localhost/path_to_phppgadmin nor http://127.0.0.1/path_to_phppgadmin. If you discover this is the case, you will need to edit the Apache configuration file, as well discuss in a moment.
Getting phpPgAdmin from the project’s Web site rather than the distro’s package repository guarantees that you get the most up-to-date and feature-filled implementation. Uncompress the file you download and install the software either by copying it directly into the server’s file path — i.e. /var/log/www/phppgadmin — or, following industry practices, copying the scripts to a directory outside the server’s file path and using an Apache directive similar to this one below:
Alias /phppgadmin /usr/share/phppgadmin/
<DirectoryMatch /usr/share/phppgadmin/>
Options +FollowSymLinks
AllowOverride None
order deny,allow
deny from all
allow from localhost
</DirectoryMatch>
The directive makes it harder for unauthorized people to edit the phpPgAdmin source code in the unlikely event that the server is compromised, thus enforcing a security policy.
No matter the method of installation, making the software work properly is a matter of setting up both the Apache and phpPgAdmin configuration files correctly.
If you’ve installed everything via your distro’s package manager then you’ve pretty much accomplished everything you need to do, assuming of course that your intention is to connect a Web server to a Postgres server that is located on the same host. But if you’re building everything yourself, or want to control access from other machines, or have more than one server running on disparate servers, you’ll need to tinker with the configuration file, just as you will if you want to change default IP addresses or if Postgres is listening on a non-standard port.
Making phpPgAdmin work well depends on its configuration file and whether it was installed via a package that was tuned for your Linux distro, or from the distro-independent version from SourceForge.net.
Since the utility is really a collection of PHP scripts, the changes you make in the configuration file will take effect immediately and can be seen by refreshing the browser.
The configuration file, /my_path_to/phppgadmin/conf/config.inc.php, uses named pairs to define behaviour — i.e. myparameter=myvalue — and includes detailed comments describing what each named pair does.
In the file, lines 10 to 31 provide the necessary parameters for one database server that sits on the local host. If you need to identify and configure more than one server, copy and paste lines 33-41 as many times as required. Remember to uncomment the lines and increment the array by one.
Lines 42 – 101 define global parameters. For the most part you should be able to live with them. However, consider editing line 64 from ‘false’ to ‘true’ so that ordinary users can work with databases they don’t own:
$conf['owned_only'] = true
The Apache configuration file
Even if you’ve configured config.inc.php correctly, you may still not be able to connect to the server. The Debian package for phpPgAdmin adds an Apache directive that prohibits all connection attempts from hosts other than localhost. Editing the /etc/apache2/conf.d/phppgadmin file and restarting the Web server should correct this issue. For example, to permit connections from hosts belonging to the network 192.168.2.0, add the line allow from localhost 192.168.2 after deny from all .
You can activate the reports generation function by setting the variable $conf[‘show_reports’] in /my_path_to/phppgadmin/conf/config.inc.php to ‘true’ and running the script reports-pgsql.sql. Reports are essentially user-defined scripts that consist of SQL instructions, similar to psql script files. You can set privileges controlling who can generate reports with commands like:
GRANT SELECT,INSERT,UPDATE,DELETE ON ppa_reports TO PUBLIC;
GRANT SELECT,UPDATE ON ppa_reports_report_id_seq TO PUBLIC;
You can also permitting users who don’t own the reports to see them too by editing the setting for the variable $conf[‘owned_reports_only’].
There are no ready-made report scripts. PhpPgAdmin’s implementation of reports consists of creating and running user-defined SQL scripts that are stored in a database expressly created for that purpose.
The Postgres Statistics Collector must be active before you can generate reports relating to monitoring database activity (refer to the FAQ for more information).
Using phpPgAdmin
The ease with which you’ll master phpPgAdmin depends a lot on your understanding of SQL and Postgres. The more experience you have as a DBA, the easier you’ll find using this client.
For our purposes, the URL for accessing phpPgAdmin is http://localhost/custom_phppadmin, although you can set it to anything you want so long as it starts within the topmost directory of the phpPgAdmin scripts.
The phpPgAdmin interface is split into two panels. The left panel, or object browser, shows the defined entities. The right panel, or main page, gives more detailed information and is the panel where all the administration tasks are carried out. You log in by clicking on either “Servers” which is located on the main page of the opening screen, or the server icon in the object browser. After a successful login you’ll see a list of databases on the main page. Clicking the ‘+’ in the object browser returns a view of all the databases in the cluster.
The main panel lists cluster-wide operations for databases, roles, tablespaces, export, and reports, though you must first run the report script to get the last one. Drilling down from either the main panel or the object browser returns more objects. Because the object browser and main panel to a certain extent work independently, you can insert data in a table in the main panel while viewing table definitions from another database.
PhpPgAdmin is robust; it will not freeze in the middle of a long transaction, since its behaviour is based on the inherent properties of your Web browser and server. You can make phpPgAdmin as secure as want by taking advantage of SSL and Web server security directives. Protecting the database against unauthorized login depends on the installation method. PhpPgAdmin logins are restricted to the localhost if phpPgAdmin was installed via a Linux distro, but remote hosts could access phpPgAdmin if you installed the client yourself and failed to configure the appropriate security policy using the Web server’s file directives. Be advised that the default configuration file, config.inc.php, connects to the Postgres server using Unix domain sockets, and the default authentication policy on Postgres permits anybody to connect without using a password on Unix domain sockets. Cookies should be enabled on your browser to manage your server sessions because phpPgAdmin doesn’t use any JavaScript or Ajax.
Administrating with phpPgAdmin
What makes administrating with phpPgAdmin cool are the page layouts for the various DDL and DML operations. The activities can be executed in an efficient manner. Inserting data into a table, for example, is a fast and easy activity for anyone who prefers keeping their fingers on the keyboard — that is, no mouse. PhpPgAdmin implements wizards, such as the creation of views, that simplifies routine administration tasks. You can also examine function source code, and each line is numbered, which is great for debugging purposes.
Navigation
Navigating through the server and its various objects is a simple matter of clicking with your mouse using either the tree directory in the object browser, or from the main panel, which offers choices in the form of URL links. Drilling down from either location via the + symbol returns more objects. There are convenience buttons for managing tables that allow you to browse, execute a SQL query, insert data, empty a table of data, drop the table, or vacuum it.
The object browser and main panel, to a certain extent, work independently. For example, you can insert data in a table in the main panel while viewing table definitions from another database.
Creating functions
You can create three kinds of functions with phpPgAdmin: SQL/PL, which is an installed procedural function, and internal and C language functions, which are functions that call a previously created function that has been compiled as an object file.
You can create a function by navigating down the schema and clicking on the “function” link on top of the main panel. After choosing the available procedural language, you can type in the function’s source code.
You can also examine existing functions’ source code, and debugging is pretty easy since each line is numbered.
Exporting data
Exporting data requires that the Postgres server utilities pg_dump and pg_dumpall be installed on the same host as the Web server, even if the Postgres server you’re managing isn’t on that host. The following code demonstrates how one could configure the file config.inc.php to enable phpPgAdmin to dump from a Postgres server:
$conf['servers'][0]['pg_dump_path'] = '/usr/bin/pg_dump';
$conf['servers'][0]['pg_dumpall_path'] = '/usr/bin/pg_dumpall';
Conclusion
PhpPgAdmin is an easy-to-use PostgreSQL client that is simple to incorporate into your production environment. When in doubt, read the project’s FAQ, which is also part of the installation documentation.
Categories:
- Tools & Utilities
- Databases
