Wiki

• FrontPage
• RecentChanges
• FindPage
• HelpContents
• PhpPgAdmin

PhPPgAdmin

Synthesis

Introduction

PhpPgAdmin is an administration interface for PostgreSQL. It is coded in PHP. With PhpPgAdmin, you can administer a remote PostgreSQL Server. A similar project is ?PhpMyAdmin for MySQL Server.

Debian Stable installation procedure

Binary installation

• requires a functionnal apt configuration !

•  apt-get install phppgadmin 

Application configuration

PhpPgAdmin's application configuration is in /etc/phppgadmin/config.inc.php. The default configuration is sufficient to get the application running and connected to a PostgreSQL database on the same machine (localhost).

The application is configured in sections, e.g. servers, srv_groups, etc. A section in the configuration is specified in the following way:

• $conf['servers']

• $conf['srv_groups']

Each group has its own set of configuration items. The configuration file is well documented and provides some examples of configuration.

Adding another PostgreSQL server on Linux

$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'] = '/usr/bin/pg_dump';
$conf['servers'][1]['pg_dumpall_path'] = '/usr/bin/pg_dumpall';

Adding another PostgreSQL on Windows

$conf['servers'][2]['desc'] = 'Test Server';
$conf['servers'][2]['host'] = '127.0.0.1';
$conf['servers'][2]['port'] = 5432;
$conf['servers'][2]['sslmode'] = 'allow';
$conf['servers'][2]['defaultdb'] = 'template1';
$conf['servers'][2]['pg_dump_path'] = 'C:\\Program Files\\PostgreSQL\\8.0\\bin\\pg_dump.exe';
$conf['servers'][2]['pg_dumpall_path'] = 'C:\\Program Files\\PostgreSQL\\8.0\\bin\\pg_dumpall.exe';

Potential issue: Login disallowed for security reasons

If you try to log in to PhpPgAdmin right after installing PostgreSQL, before any other users besides the postgres user have been created, you may try to use the postgres user with an empty password. If you do, you may get the message Login disallowed for security reasons. This happens because of a setting in the /etc/phppgadmin/config.inc.php file called extra_login_security which prevents login by users with empty passwords or usernames like pgsql, postgres, root, or admin. To change this behavior, open the /etc/phppgadmin/config.inc.php file in a text editor and change the extra login security setting from:

$conf['extra_login_security'] = true

to

$conf['extra_login_security'] = false

Apache2 configuration

PhpPgAdmin uses Apache2 and Php8.2. By default, the installation process configures the Apache2 server. An Apache configuration file for PhpPgAdmin named phppgadmin.confis placed in  /etc/apache2/conf-available. This file contains settings for the PhpPgAdmin website served by Apache. Here is what the default file looks like:

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

# Allow connections from anybody granted permission:
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>

The URL for the PhpPgAdmin site is: http://<server name>/phppgadmin

Potential issue: 403 error when browsing to the site

By default, the PhpPgAdmin website is only able to be browsed to from the localhost; the site is not browsable from outside the server. To change this behavior, open /etc/apache2/conf-available/phppgadmin.conf in a text editor. Look for the following setting:

Require local

Change it to:

Require all granted

After changing the setting, make sure that the syntax is correct:

apache2ctl -t

Make sure that the config file is enabled (it probably already is; we're just doing this to be on the safe side):

a2enconf phppgadmin

After changing the configuration restart the Apache service:

systemctl restart apache2

Configure access to a remote PostgreSQL Server

With PhpPgAdmin, you can manage many PostgreSQL servers locally (on the localhost) or on remote hosts.

First, you have to make sure that the distant PostgreSQL server can handle your request, that you can connect to it. You can do this by modifying the file pg_hba.conf and adding a line like:

# PhpPgAdmin server access
host    all     db_admin        xx.xx.xx.xx      255.255.255.255 md5

Then, you need to add your distant PostgreSQL server into the config file for PhpPgAdmin. This file is  etc/phppgadmin/config.inc.php . Here is an example of such a file:

<?php

        /**
         * Central phpPgAdmin configuration.  As a user you may modify the
         * settings here for your particular configuration.
         *
         * $Id: config.inc.php-dist,v 1.44 2005/09/07 08:09:21 chriskl Exp $
         */

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

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

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

        // Change the default database only if you cannot connect to template1
        $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'] = '';
        $conf['servers'][0]['pg_dumpall_path'] = '';

        // Slony (www.slony.info) support?
        $conf['servers'][0]['slony_support'] = false;
        // Specify the path to the Slony SQL scripts (where slony1_base.sql is located, etc.)
        // No trailing slash.
        $conf['servers'][0]['slony_sql'] = '/usr/share/postgresql';

        // Default language. Eg: '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';

        // 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. (eg. Run 'SELECT * FROM pg_database' in the SQL area.)
        $conf['owned_only'] = true;

        // 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 types,
        // operators 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;

        // Display reports feature?  For this feature to work, you must
        // install the reports database as explained in the INSTALL file.
        $conf['show_reports'] = true;

        // Only show owned reports?
        // Note: This does not prevent people from accessing other reports by
        // other means.
        $conf['owned_reports_only'] = true;

        // 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?
        $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 headers?  Unless debugging, it's best to leave this off
        $conf['use_xhtml'] = false;

        // Base URL for PostgreSQL documentation.
        // '%s', if present, will be replaced with the PostgreSQL version
        // (7, 7.1, 7.2, 7.3, 7.4, or 8.0)
        $conf['help_base'] = 'http://www.postgresql.org/docs/%s/interactive/';

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

        $conf['version'] = 15;

?>

External Links

• InstallphpPgAdmin on Debian 12

• Login disallowed for security reasons - phppgadmin error - How we fix it