Synology Photostation PostgreSQL Database

Posted on by

Last update : November 17, 2015

The Synology DSM 5.0 operating system uses the database PostgreSQL version 9.3 for Photostation 6.0 which can be administered with phpPgAdmin.

The Synology phpPgAdmin package created by Nigel Barnes (alias Pernod 70) has been updated on March 31, 2014 to work with the new Synology DSM version 5.1. The new package version is 5.1.0-002, the sources are available at Github.

The following configuration files are used :

by phpPgAdmin

  • /usr/syno/synoman/phpsrc/phpPgAdmin/conf/config.inc.php
  • /usr/syno/synoman/phpsrc/phpPgAdmin/conf/config.inc.php-dist

by PosrtgreSQL

  • /etc/postgresql/pg_hba.conf
  • /etc/postgresql/pg-ident.conf
  • /etc/postgresql/postgresql.conf
  • /etc.defaults/postgresql/pg_hba.conf
  • /etc.defaults/postgresql/pg-ident.conf
  • /etc.defaults/postgresql/postgresql.conf

config.inc.php

I named this configuration file phpPgAdmin in the Synology Config File Editor. The original content is show below :


<?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 by Synology';

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

 // 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' seperated 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. */
 

 // 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'] = false;

 // 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?
 $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;

?>

config.inc.php-dist

This is a backup copy of the main configuration file config.inc.php.

pg_hba.conf

Client authentication in PostgreSQL is controlled by a configuration file, which traditionally is named pg_hba.conf (HBA stands for host-based authentication). I named this configuration file PostgreSQL in the Synology Config File Editor. The original content is show below :


# TYPE DATABASE USER ADDRESS        METHOD
local  all      all                 trust
host   all      all  127.0.0.1/32   trust
host   all      all  ::1/128        trust

A backup copy with these default values is stored in the /etc.defaults/ folder.

pg-ident.conf

The configuration file pg-ident.conf is used to map the operating system user name to a database user name if an external authentication system is involved. In the Synology setup this file and the backup copy stored in the /etc.defaults/ folder are empty.

postgresql.conf

The original content is show below :


hba_file = '/etc/postgresql/pg_hba.conf'
ident_file = '/etc/postgresql/pg_ident.conf'

external_pid_file = '/run/postgresql/postmaster.pid'

listen_addresses = '127.0.0.1'
max_connections =64

shared_buffers = 24MB

log_destination = 'syslog'
syslog_ident = 'postgres'
client_min_messages = notice
log_min_messages = warning
log_min_error_statement = error
log_min_duration_statement = -1

track_activities = off
track_counts = off

autovacuum = off

datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'

escape_string_warning = off
synchronize_seqscans = off

standard_conforming_strings = off

A backup copy with these default values is stored in the /etc.defaults/ folder.

Configuration

Out of the box with the default configuration parameters, the login to the PostgreSQL database with the phpPgAdmin app works with the username postgres and an empty password.

After an update or upgrade of the DSM operating systme, the phpPgAdmin webpage (http://yourdomain/phpPgAdmin/) is usually no longer accessible. You must reinstall the 3rd party phpPgAdmin installation package with the following steps :

  1. deinstall phpPgAdmin
  2. set the confidence level in parameters to the required 3rd party installation
  3. install manually the latest phpPgAdmin package
  4. check the configuration files with the configuration editor (mainly the extra_login_security parameter which I set to false in the config-file named phpPgAdmin)
  5. Start the phpPgAdmin package in the package center
  6. Go to the phpPgAdmin webpage, enter the default login credentials and verify your databases
phpPgAdmin Interface on Synology

PostgreSQL database management with phpPgAdmin on Synology

The last update of the Synology System was done on November 17, 2015, followed by a new installation of phpPgAdmin.

Export

To export the Photo PostgreSQL database for backup purposes, I select the photo database, click the tab “Export” in the menu bar, select “Structure and Data” with the Format “SQL”, chose the option “download” and finally click the button “Export”. The file is saved with the name “dump.sql” in the standard local download folder.

Export

Export PostgreSQL Synology photostation database with phpPgAdmin

Links

A list with links to sources providing additional informations about the Synology PostgreSQL database is shown hereafter :