The Rapid Inquiry Facility

The RIF helps epidemiologists and public health researchers in environmental health activities.

RIF40 Postgres database build from Github

  1. Postgres Requirements
    1. 1Memory Requirements
  2. Postgres Setup
    1. Database Development Environment
    2. Tool Chain
      1. Windows
        1. Fixing Windows Code page errors
      2. Unixen
    3. Building the Database
      1. Pre Build Tests
        1. Configuring make
      2. Build control using make
        1. Setup
          1. User setup
          2. Makefile.local settings
          3. Principal build target
          4. Issues with the build
          5. Other build targets
        2. Porting limitations
        3. Help
        4. Configuration File Examples
          1. Postgres user password file
          2. Authentication Setup (hba.conf)
          3. Proxy User Setup (ident.conf)

Postgres Requirements

The new V4.0 RIF uses either Postgres or Microsoft SQL server as a database backend.

WARNING: The RIF requires Postgres 9.3 or above to work. 9.1 and 9.2 will not work. In particular PL/pgsql GET STACKED DIAGNOSTICS is used which is a post 9.2 option. It has NOT yet been tested on Postgres 10.

It is possible to install Windows Postgres RIF using pg_dump and scripts. This could also be used for MacOS and Linux with shell scripts instead.

See: Windows Postgres Install using pg_dump and scripts

The database build creates the production database dump file sahsuland.dump is present in rapidInquiryFacility\rifDatabase\Postgres\production used to create production databases.

1Memory Requirements

Approximately:

16GB recommended. If you process large geographies using the Node.js tilemaker 48-64GB is recommended.

Postgres Setup

Postgres is usually setup in one of four ways:

  1. Standalone mode on a Windows firewalled laptop. This uses local database MD5 passwords and no SSL and is not considered secure for network use.
  2. Secure mode on a Windows server. This uses remote database connections using SSL; with MD5 passwords for psql and Java connectivity.
  3. Secure mode on a Windows server and Active directory network. This uses remote database connections using SSL; with SSPI (Windows GSS connectivity) for psql and secure LDAP for Java connectivity.
  4. Secure mode on a Linux server and Active directory network. This uses remote database connections using SSL; with GSSAPI/Kerberos for psql and secure LDAP for Java connectivity.

The front and and middleware require username and password authentications; so method 4 must not be used.

Postgres also can proxy users (see ident.conf examples in the bottom of the build notes. Typically this is used to allow remote postgres administrator user authentication and to logon as the schema owner (rif40).

Database Development Environment

Currently three databases are used: sahsuland, sahsuland_dev and sahusland_empty. The installer database sahsuland is kept stable for long periods, sahsuland_dev is the working database. The development database sahusland_dev is built from psql scripts which create and populate the database ojects. Then alter scripts not under development and then run and the database tested. The database is then exported and then imported into sahsuland. The sahsuland_dev database can then be enhanced with the alter scripts under development. sahusland_empty is used to test data loading and contains no health data, only the sahsuland geography.

The middleware always runs against sahsuland.

After the move from the SAHSU private network to the main Imperial network the database structure is only modified by alter scripts so that the SAHSU Private network and Microsoft SQL Server Ports can be kept up to date. Alter scripts are designed to be run more than once without side effects, although they must be run in numeric order.

Once the RIF is in production alter scripts will be setup to only be able to complete successfully once and cannot then be re-run.

The directory structure is *rifDatabase\Postgres*:

The sahsuland example data is contatined in *rifDatabase*:

These duretories are shared with the SQL Serer port.

The databases sahsuland, sahsuland_dev and sahusland_empty are built using make and Node.js.

Tool Chain

Windows

Fixing Windows Code page errors

See notes for windows users:

H:\>psql
psql (9.3.2)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

Unixen

Unix builds are not currently supported. For more information see:

Building the Database

Pre Build Tests

Run up a shell/command tool, not in the Postgres build directory (psql_scripts). The Window sizing needs to be at least 132 columns wide and 50 rows high; preferably with a multi thousand line buffer. Otherwiser psql scripts may require on scrolling:

If you type make in the rapidInquiryFacility directory make will build the middleware. This may not be what you want!

Configuring make

Do not edit the Makefile directly; subsequent git changes will cause conflicts and you may lose your changes. Instead, in the GitHub\rapidInquiryFacility\rifDatabase\Postgres\psql_scripts build directory, copy Makefile.local.example to Makefile.local and edit Makefile.local. Beware of your choice of editor on especially Windows. The RIF is developed on Windows and Linux at the same time so you will have files with both Linux and Windows semantics. Windows Notepad in particular does not understand Linux files.

If you enable PL/R then the directories R_library (in $PGDATA/R_Library) and $R_HOME/share/extension must exist

Build control using make

The RIF database is built using GNU make for ease of portability. The RIF is built from the directory GitHub\rapidInquiryFacility\rifDatabase\Postgres\psql_scripts.

Setup

User setup

Make MUST NOT be run as an administrator but a normal user (completely the oppposite of the SQL Server port!). Beware: you must NOT have power user privilege.

For make to work it needs to be able to logon as following users:

The password for all users must be set in your local .pgpass/pgpass.conf files. See Postgres documentation for its location on various ports. The postgres user password must be correct in the .pgpass/pgpass.conf file and in Makefile.local or you may be locked out of postgres. The accounts apart from postgres are created by db_create.sql.

IMPORTANT

E.g. C:\Users\pch\AppData\Roaming\postgresql\pgpass.conf:

localhost:5432:*:postgres:<password>
localhost:5432:*:pch:<password>
localhost:5432:*:rif40:<password>
localhost:5432:*:notarifuser:<password>

See Configuration File Examples below.

Set the following Postgres environment variables using the system control panel: Control Panel\All Control Panel Items\System:. This is well hidden on Windows 10, but you can type the path into Windows explorer! Choose Advanced System Settings, Environment variables and modify the System Variables.

Create as Administrator a directory for a system wide psql logon script (plsqrc) in Postgres

C:\Program Files\PostgreSQL\9.6\etc

This needs to be set so your database creation user can wruite file to this directory.

Once you have setup the pgpass file and the Postgres environment, check you can logon using psql as the database adminstrator account; postgres.

psql -d postgres -U postgres
You are connected to database "postgres" as user "postgres" on host "wpea-rif1" at port "5432".
psql (9.3.5)
Type "help" for help.

postgres=#

IT IS STRONGLY ADVISED TO LEAVE THIS WINDOW OPEN SO YOU CANNOT LOCK YOURSELF OUT OF THE DATABASE IF YOU SET IT UP WRONG.

This scripts do check the setup is correct; but this could fail. It is possible to login to postgres as postgres without a password using the administrator or root accounts. If you lock yourself out the hba.conf file will need the following line temporary added at the top of the file:

local  all   all   trust

or if local connections are not permitted by the Postgres build:

host  all   all  127.0.0.1/32  trust

Remove these lines after you have changed the password using:

psql -U postgres -d postgres
ALTER USER postgres PASSWORD <new password>;

See fixing windows code page errors if you get a code page error.

Makefile.local is used to set:

The is in the form of an Md5 hash of the password with the username appended. It can be generated as follows:

SELECT 'md5'||md5('Imperial1234'||'rif40') AS password;
              password
-------------------------------------
 md5a210d9711fa5ffb4f170c60676c8a63e
(1 row)

The database creation script db_create.sql check tyo see of the current Postgres adminstrator (postgres) password is the same as set in the Makefile, and will abort database creation if it is not.

psql:db_create.sql:147: INFO:  db_create.sql() User check: postgres
psql:db_create.sql:147: INFO:  db_create.sql() rif40 needs to be created encrypted password will be ="md5971757ca86c61e2d8f618fe7ab7a32a1"
psql:db_create.sql:147: ERROR:  db_create.sql() postgres encrypted password set in makefile="md5b631d55b5718b4d083a4b6e73e5fd0c5" differs from database: "md5ef9bbf3d76edb4da049ed82636ca74f1
CONTEXT:  PL/pgSQL function inline_code_block line 53 at RAISE

You can change the password direct using the encrypted hash; *beware this can get you locked out if you get it wrong!

ALTER USER postgres ENCRYPTED PASSWORD 'md5a210d9711fa5ffb4f170c60676c8a63e';

Once db_create.sql has created the user accounts, each is tested in turn to check that the .pgpass/pgpass.conf file is setup correctly.

See next section for Makefile.local example

Makefile.local settings

Typical example (Makefile.local.example):

# ************************************************************************
#
# GIT Header
#
# $Format:Git ID: (%h) %ci$
# $Id: e96a6b0aa1ba85325e1b7b0e57163d2b7707440b $
# Version hash: $Format:%H$
#
# Description:
#
# Rapid Enquiry Facility (RIF) - Makefile.local environment overrides (example)
#
# Copyright:
#
# The Rapid Inquiry Facility (RIF) is an automated tool devised by SAHSU
# that rapidly addresses epidemiological and public health questions using
# routinely collected health and population data and generates standardised
# rates and relative risks for any given health outcome, for specified age
# and year ranges, for any given geographical area.
#
# Copyright 2014 Imperial College London, developed by the Small Area
# Health Statistics Unit. The work of the Small Area Health Statistics Unit
# is funded by the Public Health England as part of the MRC-PHE Centre for
# Environment and Health. Funding for this project has also been received
# from the Centers for Disease Control and Prevention.
#
# This file is part of the Rapid Inquiry Facility (RIF) project.
# RIF is free software: you can redistribute it and/or modify
# it under the terms of the GNU Lesser General Public License as published by
# the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# RIF is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU Lesser General Public License for more details.
#
# You should have received a copy of the GNU Lesser General Public License
# along with RIF. If not, see <http://www.gnu.org/licenses/>; or write
# to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor,
# Boston, MA 02110-1301 USA
#
# Author:
#
# Peter Hambly, SAHSU
#
# Local overrides; copy Makefile.local.example to Makefile.local and edit Makefile.local
#
# Local Makefile overrides - example
#
# Postgres ENV variables
#
#PGHOST=localhost
#
# ENCRYPTED_POSTGRES/RIF40_PASSWORD hash comes from PGAdmin III or psql
#
# Postgres MD5 password format is:
#
# 'md5'||md5('password'||'username'); e.g. to set the rif40 password to: Imperial1234, use psql:
#
# SELECT 'md5'||md5('Imperial1234'||'rif40') AS password;
# SELECT 'md5'||md5('Imperial1234'||'postgres') AS password;
#
# The rif user password is always set to the username
#
#ENCRYPTED_POSTGRES_PASSWORD=md5913e79312c717f83cfc1626754233824
#ENCRYPTED_RIF40_PASSWORD=md5a210d9711fa5ffb4f170c60676c8a63e
#
# Only set SAHSULAND_TABLESPACE_DIR if postgres has access to the directory!
#
# Make you escape spaces in the correct manner for the OS:
#
# e.g.
#
# Windows: SAHSULAND_TABLESPACE_DIR=\"C:rif40 database\"
#
#SAHSULAND_TABLESPACE_DIR=\"C:/rif40 database\"

#
# Default Windows Administrator
#
DEFAULT_WINDOWS_ADMIN_USER=Administrator

#
# PL/pgsql debug levels (DEBUG_LEVEL);
#
# 0 - Suppressed, INFO only
# 1 - Major function calls
# 2 - Major function calls, data
# 3 - Reserved for future used
# 4 - Reserved for future used
#
# PSQL verbosity (VERBOSITY):
#
# verbose	- Messages/errors with full context
# terse 	- Just the error or message
#
# PSQL echo (ECHO)
#
# all: 		- All SQL
# none:		- No SQL
#
# PSQL script user (PSQL_USER)
#
# - Usually rif40 (schema owner)
#
# Use PL/R (USE_PLR)
#
# - Database has PL/R extension loaded (not needed by default)
#
# Create SAHSULAND database only (CREATE_SAHSULAND_ONLY)
#
# - Do not create SAHSULAND_DEV
#
#VERBOSITY=terse
#DEBUG_LEVEL=0
#ECHO=none
#PSQL_USER=rif40
#USE_PLR=N
#CREATE_SAHSULAND_ONLY=N

#
# Testuser: defaults to USERNAME; set if USERNAME is in mixed case or contains spaces
# Testpassword: password for testuser; defaults to USERNAME; set if USERNAME is in mixed case or contains spaces
#
# TESTUSER=myusername
# TESTPASSWORD=myusername

#
# Eof

Parameters:

Principal build target

DO NOT RUN AS AN ADMINISTRATOR

C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\Postgres\psql_scripts> make clean db_setup

Note that this does not apply the alter scripts under development

The principal build target (make db_setup) runs the following Makefiles/targets/scripts:

End of a successful db_setup make run:

pg_dump: setting owner and privileges for FK CONSTRAINT table_or_view_name_hide_fk
pg_dump: setting owner and privileges for FK CONSTRAINT table_or_view_name_hide_fk
make[1]: Leaving directory `/c/Users/pch/Documents/GitHub/rapidInquiryFacility/rifDatabase/Postgres/psql_scripts'
make -C ../../ERD dbms_tools
Debug level set to default: 0
make[1]: Entering directory `/c/Users/pch/Documents/GitHub/rapidInquiryFacility/rifDatabase/ERD'
Makefile:184: *** commands commence before first target.  Stop.
make[1]: Leaving directory `/c/Users/pch/Documents/GitHub/rapidInquiryFacility/rifDatabase/ERD'
make: [db_setup] Error 2 (ignored)
SAHSULAND and SAHSULAND_DEV setup completed OK
Issues with the build
Makefile:358: warning: overriding commands for target `../production/sahsuland.dump'
Makefile:350: warning: ignoring old commands for target `../production/sahsuland.dump'
Please create c:/Program Files/PostgreSQL/9.6/etc as root/Administrator
Please create c:/Program Files/PostgreSQL/9.6/etc as root/Administrator
powershell -ExecutionPolicy ByPass -file copy.ps1  ../etc/psqlrc c:/Program Files/PostgreSQL/9.6/etc
Please Create: c:/Program Files/PostgreSQL/9.6/etc as root/Administrator

psql:db_create.sql:147: INFO:  db_create.sql() User check: postgres
psql:db_create.sql:147: INFO:  db_create.sql() rif40 needs to be created encrypted password will be ="md5971757ca86c61e2d8f618fe7ab7a32a1"
psql:db_create.sql:147: ERROR:  db_create.sql() postgres encrypted password set in makefile="md5b631d55b5718b4d083a4b6e73e5fd0c5" differs from database: "md5ef9bbf3d76edb4da049ed82636ca74f1
CONTEXT:  PL/pgSQL function inline_code_block line 53 at RAISE
Other build targets

Porting limitations

Makefiles have the following limitations:

Help

The Makefile has help:

C:\Users\phamb\Documents\GitHub\rapidInquiryFacility\rifDatabase\Postgres\psql_scripts>make help
Debug level set to default: 0
Makefile:366: warning: overriding commands for target `../production/sahsuland.dump'
Makefile:358: warning: ignoring old commands for target `../production/sahsuland.dump'
findstr "#-" Makefile
#-
#- Rapid Enquiry Facility (RIF) - Makefile for \\GitHub\rapidInquiryFacility\rifDatabase\Postgres\psql_scripts
#-
#- DO NOT RUN THE SUBDIRECTORY MAKEFILES DIRECTLY; THEY REQUIRE ENVIRONMENT SETUP TO WORK CORRECTLY
#-
        HELP=findstr "\#-" Makefile
        HELP=grep "\#-" Makefile
#-
#- PL/pgsql debug levels (DEBUG_LEVEL);
#-
#- 0 - Suppressed, INFO only
#- 1 - Major function calls
#- 2 - Major function calls, data
#- 3 - Reserved for future used
#- 4 - Reserved for future used
#-
#- PSQL verbosity (VERBOSITY):
#-
#- verbose      - Messages/errors with full context
#- terse        - Just the error or message
#-
#- PSQL echo (ECHO)
#-
#- all:                 - All SQL
#- none:                - No SQL
#-
#- Targets
#-
#- 1. patching
#-
#- all: Run all completed alter scripts and test [DEFAULT]
#- patch: Run all completed alter scripts on both sahsuland_dev and sahusland
#- repatch: Re-run all in-complete alter scripts on both sahsuland_dev and sahusland
#- world: fully rebuild databases from source
#- dev: Run all alter scripts in development
#-
#- 2. build
#-
#- sahsuland_dev_no_alter: Rebuild sahsuland_dev, test [State of SAHSULAND at port to SQL server], finally VACUUM ANALYSE
#- sahsuland_dev: Rebuild sahsuland_dev, test, then patch dev only, retest, finally VACUUM ANALYZE
#- Does not run all alter scripts in development
#- Test can fail
#- sahsuland_empty: Rebuild sahsuland_empty, test, then patch dev only
#- Does not run all alter scripts in development
#- Test can fail
#- topojson_convert: GeoJSON to topoJSON converter
#-
#- 3. installers
#-
#- sahsuland.sql: Dump sahsuland database to plain SQL, excluding UK91, EW01 shapefile data from non dev dumps
#- sahsuland_dev_dump: Dump sahsuland_dev database to plain SQL, excluding UK91, EW01 shapefile data from non dev dumps
#-                     Used to create sahsuland
#-
#- 4. test
#-
#- test: Run all test scripts [Non verbose, no debug]
#- retest: Re-run incomplete test scripts [Non verbose, no debug]
#- test_no_alter: Run test scripts able to be run before the alter scripts [Non verbose, no debug]
#- test: Run all test scripts [debug_level=1]
#- test: Run all test scripts [debug_level=1]
#- test: Run all test scripts [Verbose, debug_level=2, echo=all]
#-
#- 5. cleanup
#-
#- clean: Remove logs so completed scripts can be re-run
#- devclean: Remove logs so alter scripts in development can be r-run
#-           Not normally needed as they abort.
#-
#- 7. Database setup. Needs to be able to connect to postgresDB as postgres
#-
#- db_setup: Re-create empty sahsuland, sahsuland_dev; build sahusland_dev from scripts;
#-           build dev dump files; restore sahsuland from dev dump; patch sahsuland to dev standard;
#-           build production dump file; rebuild ERD model
#- ERD: remake ERD
#- db_create: Create empty database
#-
#- 7. miscellaneous
#-
#- v4_0_vacuum_analyse_dev: VACUUM ANALYZE sahsuland dev database
#- help: Display this help
#- recurse: Recursive make target: make recurse <recursive target>
#-          e.g. make recurse alter_1.rpt
#-

Configuration File Examples

If you are running locally only (e.g. on a laptop) you do NOT need to edit the configuration files.

Postgres user password file

Postgres user password files are located in:

One line per host, database and account. Fields separated by “:”. Order is:

Authentication Setup (hba.conf)

You MUST read the Postgres manuals before editing this file.

Fields separated by TAB.

# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# Refer to the "Client Authentication" section in the PostgreSQL
# documentation for a complete description of this file.  A short
# synopsis follows.
#
# This file controls: which hosts are allowed to connect, how clients
# are authenticated, which PostgreSQL user names they can use, which
# databases they can access.  Records take one of these forms:
#
# local      DATABASE  USER  METHOD  [OPTIONS]
# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostssl    DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
# hostnossl  DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.
#
# DATABASE can be "all", "sameuser", "samerole", "replication", a
# database name, or a comma-separated list thereof. The "all"
# keyword does not match "replication". Access to replication
# must be enabled in a separate record (see example below).
#
# USER can be "all", a user name, a group name prefixed with "+", or a
# comma-separated list thereof.  In both the DATABASE and USER fields
# you can also write a file name prefixed with "@" to include names
# from a separate file.
#
# ADDRESS specifies the set of hosts the record matches.  It can be a
# host name, or it is made up of an IP address and a CIDR mask that is
# an integer (between 0 and 32 (IPv4) or 128 (IPv6) inclusive) that
# specifies the number of significant bits in the mask.  A host name
# that starts with a dot (.) matches a suffix of the actual host name.
# Alternatively, you can write an IP address and netmask in separate
# columns to specify the set of hosts.  Instead of a CIDR-address, you
# can write "samehost" to match any of the server's own IP addresses,
# or "samenet" to match any address in any subnet that the server is
# directly connected to.
#
# METHOD can be "trust", "reject", "md5", "password", "gss", "sspi",
# "krb5", "ident", "peer", "pam", "ldap", "radius" or "cert".  Note that
# "password" sends passwords in clear text; "md5" is preferred since
# it sends encrypted passwords.
#
# OPTIONS are a set of options for the authentication in the format
# NAME=VALUE.  The available options depend on the different
# authentication methods -- refer to the "Client Authentication"
# section in the documentation for a list of which options are
# available for which authentication methods.
#
# Database and user names containing spaces, commas, quotes and other
# special characters must be quoted.  Quoting one of the keywords
# "all", "sameuser", "samerole" or "replication" makes the name lose
# its special character, and just match a database or username with
# that name.
#
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can
# use "pg_ctl reload" to do that.

# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records.  In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# IPv4, IPv6 local connections:
#
host    all             postgres	127.0.0.1/32            md5
host    all             postgres      ::1/128                 md5
hostssl all		 postgres     146.179.138.xxx 	  255.255.255.255    md5
#
# Allow local connections as schema owner (usually use a proxy)
#
#hostssl    sahsuland	   pop             127.0.0.1/32            md5
#hostssl    sahsuland	   pop             ::1/128                 md5
#hostssl    sahsuland	   gis             127.0.0.1/32            md5
#hostssl    sahsuland	   gis             ::1/128                 md5
#hostssl    sahsuland	   rif40           127.0.0.1/32            md5
#hostssl    sahsuland	   rif40           ::1/128                 md5
#
# Active directory GSSAPI connections with pg_ident.conf maps for schema accounts
#
hostssl	sahsuland	all	 	127.0.0.1/32 		sspi 	map=sahsuland
hostssl	sahsuland	all	 	::1/128 		sspi 	map=sahsuland
hostssl	sahsuland_dev	all	 	127.0.0.1/32 		sspi 	map=sahsuland_dev
hostssl	sahsuland_dev	all	 	::1/128 		sspi 	map=sahsuland_dev
#
# Allow remote access from specified IP addresses by:
#
# a) SSPI (Windows native GSS [Kerberos] machanism
#
hostssl	sahsuland	all	 	146.179.138. xxx	255.255.255.255	sspi 	map=sahsuland
hostssl	sahsuland_dev	all	 	146.179.138. xxx	255.255.255.255	sspi 	map=sahsuland_dev
#
# b) LDAP (to be fixed – need to use different server
#
# hostssl	sahsuland_dev	all	 	146.179.138.157 	255.255.255.255 ldap ldapurl="ldaps:// xxx.ic.ac.uk/basedn;cn=;,o=Imperial College,c=GB"
#
# No LDAP URLs or username map on Windows
#
# 2014-03-12 13:44:24 GMT LOG: 00000: LDAP login failed for user "cn=pch,o=Imperial College,c=GB" on server " xxx.ic.ac.uk": Invalid Credentials 2014-03-12 13:44:24 GMT LOCATION: CheckLDAPAuth, src\backend\libpq\auth.c:2321
#
#host	sahsuland_dev	all	 	146.179.138.157 	255.255.255.255 ldap ldapserver= xxx.ic.ac.uk ldapprefix="uid=" ldapsuffix=",ou=phs,o=Imperial College,c=GB"
#
# 2014-03-12 13:50:33 GMT LOG: 00000: LDAP login failed for user "pch@IC.AC.UK" on server " xxx.ic.ac.uk": Invalid DN Syntax 2014-03-12 13:50:33 GMT LOCATION: CheckLDAPAuth, src\backend\libpq\auth.c:2321
#
#host	sahsuland_dev	all	 	146.179.138.157 	255.255.255.255 ldap ldapserver= xxx.ic.ac.uk ldapprefix= ldapsuffix="@IC.AC.UK"
#host	sahsuland_dev	all	 	146.179.138.157 	255.255.255.255 ldap ldapserver= xxx.ic.ac.uk ldapprefix= ldapsuffix=",o=Imperial College,c=GB"
#
# Other databases
#
hostssl	traffic		all	 	127.0.0.1/32 		sspi
hostssl	traffic		all	 	::1/128 		sspi
hostssl	traffic		all	 	146.179.138. xxx	255.255.255.255	sspi
#
#host    all             all             127.0.0.1/32            md5
#host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5
Proxy User Setup (ident.conf)

You MUST read the Postgres manuals before editing this file.

One line per per system user and map, fields separated by TAB in the order:

# PostgreSQL User Name Maps
# =========================
#
# Refer to the PostgreSQL documentation, chapter "Client
# Authentication" for a complete description.  A short synopsis
# follows.
#
# This file controls PostgreSQL user name mapping.  It maps external
# user names to their corresponding PostgreSQL user names.  Records
# are of the form:
#
# MAPNAME  SYSTEM-USERNAME  PG-USERNAME
#
# (The uppercase quantities must be replaced by actual values.)
#
# MAPNAME is the (otherwise freely chosen) map name that was used in
# pg_hba.conf.  SYSTEM-USERNAME is the detected user name of the
# client.  PG-USERNAME is the requested PostgreSQL user name.  The
# existence of a record specifies that SYSTEM-USERNAME may connect as
# PG-USERNAME.
#
# If SYSTEM-USERNAME starts with a slash (/), it will be treated as a
# regular expression.  Optionally this can contain a capture (a
# parenthesized subexpression).  The substring matching the capture
# will be substituted for \1 (backslash-one) if present in
# PG-USERNAME.
#
# Multiple maps may be specified in this file and used by pg_hba.conf.
#
# No map names are defined in the default configuration.  If all
# system user names and PostgreSQL user names are the same, you don't
# need anything in this file.
#
# This file is read on server startup and when the postmaster receives
# a SIGHUP signal.  If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect.  You can
# use "pg_ctl reload" to do that.

# Put your actual configuration here
# ----------------------------------

# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
#
sahsuland	pch			pop
sahsuland	pch			gis
sahsuland	pch			rif40
sahsuland	pch			pch
#
sahsuland_dev	pch			pop
sahsuland_dev	pch			gis
sahsuland_dev	pch			rif40
sahsuland_dev	pch			pch
sahsuland_dev	pch			postgres
#
# Eof