Install phpPgAdmin on Debian 12

In this guide, you will learn how to install phpPgAdmin on Debian 12. phpPgAdmin is a web-based administration tool for PostgreSQL.

Installing phpPgAdmin on Debian 12

phpPgAdmin Features

phpPgAdmin is a perfect PostgreSQL database administration tool offering features like:

• Administer multiple servers
• Manage all aspects of:
  • Users & groups
  • Databases
  • Schemas
  • Tables, indexes, constraints, triggers, rules & privileges
  • Views, sequences & functions
  • Advanced objects
  • Reports
• Easy data manipulation:
  • Browse tables, views & reports
  • Execute arbitrary SQL
  • Select, insert, update and delete
• Dump table data in a variety of formats: SQL, COPY, XML, XHTML, CSV, Tabbed,
  pg_dump
• Import SQL scripts, COPY data, XML, CSV and Tabbed
• Supports the Slony master-slave replication engine
• Excellent language support:
  • Available in 27 languages
  • No encoding conflicts. Edit Russian data using a Japanese interface!
• Easy to install and configure

Install PostgreSQL Database Server

Refer to our guides on how to install PostgreSQL server;

How to install PostgreSQL server on Linux

Install phpPgAdmin

As of this writing, phpPgAdmin 7.13.0 is the current stable release and is fortunately available on Debian 12 repositories.

As such, you can simply execute the command below to installing phpPgAdmin on Debian 12;

apt install phppgadmin

The command will install all required packages and their dependencies.

Configure phpPgAdmin

phpPgAdmin configuration

The default phpPgAdmin configuration file is /etc/phppgadmin/config.inc.php. Below is the default configs;

cat /etc/phppgadmin/config.inc.php

<?php

	/**
	 * Central phpPgAdmin configuration.  As a user you may modify the
	 * settings here for your particular configuration.
	 *
	 * $Id: config.inc.php-dist,v 1.55 2008/02/18 21:10:31 xzilla Exp $
	 */

	// An example server.  Create as many of these as you wish,
	// indexed from zero upwards.

	// Display name for the server on the login screen
	$conf['servers'][0]['desc'] = 'PostgreSQL';

	// Hostname or IP address for server.  Use '' for UNIX domain socket.
	// use 'localhost' for TCP/IP connection on this computer
	$conf['servers'][0]['host'] = 'localhost';

	// Database port on server (5432 is the PostgreSQL default)
	$conf['servers'][0]['port'] = 5432;

	// Database SSL mode
	// Possible options: disable, allow, prefer, require
	// To require SSL on older servers use option: legacy
	// To ignore the SSL mode, use option: unspecified
	$conf['servers'][0]['sslmode'] = 'allow';

	// Change the default database only if you cannot connect to template1.
	// For a PostgreSQL 8.1+ server, you can set this to 'postgres'.
	$conf['servers'][0]['defaultdb'] = 'template1';

	// Specify the path to the database dump utilities for this server.
	// You can set these to '' if no dumper is available.
	$conf['servers'][0]['pg_dump_path'] = '/usr/bin/pg_dump';
	$conf['servers'][0]['pg_dumpall_path'] = '/usr/bin/pg_dumpall';

	// Example for a second server (PostgreSQL for Windows)
	//$conf['servers'][1]['desc'] = 'Test Server';
	//$conf['servers'][1]['host'] = '127.0.0.1';
	//$conf['servers'][1]['port'] = 5432;
	//$conf['servers'][1]['sslmode'] = 'allow';
	//$conf['servers'][1]['defaultdb'] = 'template1';
	//$conf['servers'][1]['pg_dump_path'] = 'C:\\Program Files\\PostgreSQL\\8.0\\bin\\pg_dump.exe';
	//$conf['servers'][1]['pg_dumpall_path'] = 'C:\\Program Files\\PostgreSQL\\8.0\\bin\\pg_dumpall.exe';


	/* Groups definition */
	/* Groups allow administrators to logicaly group servers together under
	 * group nodes in the left browser tree
	 *
	 * The group '0' description
	 */
	//$conf['srv_groups'][0]['desc'] = 'group one';

	/* Add here servers indexes belonging to the group '0' separated by comma */
	//$conf['srv_groups'][0]['servers'] = '0,1,2';

	/* A server can belong to multi groups. Here server 1 is referenced in both
	 * 'group one' and 'group two'*/
	//$conf['srv_groups'][1]['desc'] = 'group two';
	//$conf['srv_groups'][1]['servers'] = '3,1';

	/* A group can be nested in one or more existing groups using the 'parents'
	 * parameter. Here the group 'group three' contains only one server and will
	 * appear as a subgroup in both 'group one' and 'group two':
	 */
	//$conf['srv_groups'][2]['desc'] = 'group three';
	//$conf['srv_groups'][2]['servers'] = '4';
	//$conf['srv_groups'][2]['parents'] = '0,1';

	/* Warning: Only groups with no parents appears at the root of the tree. */

	/* You can apply specific theme depending on servers, users and databases
	 * The priority order is :
	 *   * the theme defined for a server
	 *   * the theme defined for a database apply over the server one
	 *   * the theme defined for a user apply over the database one
	 */
	/* Example for servers */
	//$conf['servers'][0]['theme']['default'] = 'default';
	/* Example for users */
	//$conf['servers'][0]['theme']['user']['specific_user'] = 'default';
	/* Example for databases */
	//$conf['servers'][0]['theme']['db']['specific_db'] = 'default';

	// Default language. E.g.: 'english', 'polish', etc.  See lang/ directory
	// for all possibilities. If you specify 'auto' (the default) it will use
	// your browser preference.
	$conf['default_lang'] = 'auto';

	// AutoComplete uses AJAX interaction to list foreign key values
	// on insert fields. It currently only works on single column
	// foreign keys. You can choose one of the following values:
	// 'default on' enables AutoComplete and turns it on by default.
	// 'default off' enables AutoComplete but turns it off by default.
	// 'disable' disables AutoComplete.
	$conf['autocomplete'] = 'default on';

	// If extra login security is true, then logins via phpPgAdmin with no
	// password or certain usernames (pgsql, postgres, root, administrator)
	// will be denied. Only set this false once you have read the FAQ and
	// understand how to change PostgreSQL's pg_hba.conf to enable
	// passworded local connections.
	$conf['extra_login_security'] = true;

	// Only show owned databases?
	// Note: This will simply hide other databases in the list - this does
	// not in any way prevent your users from seeing other database by
	// other means. (e.g. Run 'SELECT * FROM pg_database' in the SQL area.)
	$conf['owned_only'] = false;

	// Display comments on objects?  Comments are a good way of documenting
	// a database, but they do take up space in the interface.
	$conf['show_comments'] = true;

	// Display "advanced" objects? Setting this to true will show
	// aggregates, types, operators, operator classes, conversions,
	// languages and casts in phpPgAdmin. These objects are rarely
	// administered and can clutter the interface.
	$conf['show_advanced'] = false;

	// Display "system" objects?
	$conf['show_system'] = false;

	// Minimum length users can set their password to.
	$conf['min_password_length'] = 1;

	// Width of the left frame in pixels (object browser)
	$conf['left_width'] = 200;

	// Which look & feel theme to use
	$conf['theme'] = 'default';

	// Show OIDs when browsing tables?
	// Only supported in versions <=11
	$conf['show_oids'] = false;

	// Max rows to show on a page when browsing record sets
	$conf['max_rows'] = 30;

	// Max chars of each field to display by default in browse mode
	$conf['max_chars'] = 50;

	// Send XHTML strict headers?
	$conf['use_xhtml_strict'] = false;

	// Base URL for PostgreSQL documentation.
	// '%s', if present, will be replaced with the PostgreSQL version
	// (e.g. 8.4 )
	$conf['help_base'] = 'http://www.postgresql.org/docs/%s/interactive/';

	// Configuration for ajax scripts
	// Time in seconds. If set to 0, refreshing data using ajax will be disabled (locks and activity pages)
	$conf['ajax_refresh'] = 3;

	/** Plugins management
	 * Add plugin names to the following array to activate them
	 * Example:
	 *   $conf['plugins'] = array(
	 *     'Example',
	 *     'Slony'
	 *   );
	 */
	$conf['plugins'] = array();

	/*****************************************
	 * Don't modify anything below this line *
	 *****************************************/

	$conf['version'] = 19;

?>

In its basic setup, the default configurations suffice.

Feel free to modify other settings as you see fit and save the changes when done.

phpPgAdmin Web Server Configuration

By default, phpPgAdmin uses Apache HTTP server. Upon installation, the phpPgAdmin HTTP server configuration is handled automatically.

The default configuration file is /etc/apache2/conf-available/phppgadmin.conf.

The default configs are ;

cat /etc/apache2/conf-available/phppgadmin.conf

Alias /phppgadmin /usr/share/phppgadmin

<Directory /usr/share/phppgadmin>

<IfModule mod_dir.c>
DirectoryIndex index.php
</IfModule>
AllowOverride None

# Only allow connections from localhost:
Require local

<IfModule mod_php.c>
  php_flag magic_quotes_gpc Off
  php_flag track_vars On
  #php_value include_path .
</IfModule>
<IfModule !mod_php.c>
  <IfModule mod_actions.c>
    <IfModule mod_cgi.c>
      AddType application/x-httpd-php .php
      Action application/x-httpd-php /cgi-bin/php
    </IfModule>
    <IfModule mod_cgid.c>
      AddType application/x-httpd-php .php
      Action application/x-httpd-php /cgi-bin/php
    </IfModule>
  </IfModule>
</IfModule>

</Directory>

By default, access is only allowed locally from within the host, Require local. To allow external access to the web server, change that line to Require all granted.

Enable phpPgAdmin configuration (it should already be enabled);

a2enconf phppgadmin

Check for any Apache configuration errors;

apache2ctl -t

Ensure output is Syntax OK.

If you make any changes to the Web server configurations, restart and enable Apache Web server to run on system boot (if not already enabled);

systemctl restart apache2

systemctl enable apache2

Accessing phpPgAdmin Web Interface

You should now be able to access phpPgAdmin web interface from the browser.

Ensure to open the web server ports on firewall, if any is running.

You can access phpPgAdmin web interface via http://your_domain_or_ip/phppgadmin. Replace your_domain_or_ip with the domain name or IP address of your server.

Login to PostgreSQL Server on phpPgAdmin by clicking on PostgreSQL under Servers.

If upon login you get the error;

Version of PostgreSQL not supported. Please upgrade to version or later.

You need to update the phpPgAdmin configuration file to support PostgreSQL 15. Thus;

vim /usr/share/phppgadmin/classes/database/Connection.php

Change this section;

		// Detect version and choose appropriate database driver
        switch (substr($version,0,2)) {
            case '14': return 'Postgres';break;
            case '13': return 'Postgres13';break;
            case '12': return 'Postgres12';break;
            case '11': return 'Postgres11';break;
            case '10': return 'Postgres10';break;
        }  

Include version 15 of PostgreSQL server;

		// Detect version and choose appropriate database driver
        switch (substr($version,0,2)) {
            case '15': return 'Postgres';break;
            case '14': return 'Postgres14';break;
            case '13': return 'Postgres13';break;
            case '12': return 'Postgres12';break;
            case '11': return 'Postgres11';break;
            case '10': return 'Postgres10';break;
        }    

Save and exit the file.

Restart Apache;

systemctl restart apache2

Next, you should be able to login to PostgreSQL 15 databases;

And that is it. You should now be able to administer your PostgreSQL server from the web interface.

That concludes our guide on installing phpPgAdmin on Debian 12.

Other Tutorials

Install pgAdmin 4 on Debian 12

Install MySQL Workbench on Oracle Linux 8