[Duraspace] Connecting to PostgreSQL

Wed Sep 23 17:40:50 SAST 2009

Hi,

You first need to ensure that in the pg_hba.conf file on the server where your database is installed (by default your DSpace server) you have assigned the necessary priviliges for a host on your network to connect to PostgreSQL on that server.  

This file should typically be /etc/postgresql/8.3/main/pg_hba.conf.

See below an example pg_hba configuration that allows any host on our network to connect to PostgreSQL using my own (wklap) authentication credentials.  The actual password for my username is saved in the ODBC setting on my pc - see attached screen dump.  

Please note that in this example I have created an additional user in PostgreSQL called "wklap" to allow for a proper audit trail when manipulating the database externally - of course using the constraints set by the DSpace SQL installation script - to separate activity logging from the default user "dspace" (as per our installation instructions).

If you think this is a bit risky (which it is...) then you can limit connections to only one host (146.232.75.15 in the example below).

But for this to work you must first open port 5432 on your DSpace server's firewall to allow for external traffic to access PostgreSQL.  On Ubuntu you use the "ufw" (Ubuntu Firewall) command.  First enable ufw ("ufw enable") as sudo and then list your open ports ("ufw status") and then add the port.  I suggest you familiarize yourself with ufw first ("man ufw").

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

#### "local" is for Unix domain socket connections only
local   all         all                               trust

#### IPv4 local connections:

#localhost trust all
host    all         all         127.0.0.1/32          trust

#localhost admin users trust all on SUN LAN
host    all         wklap       146.232.0.0/16        trust  #all hosts on the campus network using wouter's credentials.

#remote hosts with DB Users on SUN LAN
host    all         dspace      146.232.75.15/24      md5    #wouter's host (workstation) only.

#### IPv6 local connections:
host    all         all         ::1/128               trust

Regards,
Wouter
________________________________________________________
Wouter Klapwijk
Senior software specialist
Library and Information Service, Stellenbosch University
Tel: +27 21 808-4378, Fax: +27 21 808-3723, Mobile: 083 3888 270
http://library.sun.ac.za

-----Original Message-----
From: duraspace-admin at moo.sun.ac.za [mailto:duraspace-admin at moo.sun.ac.za] On Behalf Of Hilton Gibson
Sent: 23 September 2009 16:42
To: Sherwyn Mack
Cc: Duraspace
Subject: RE: [Duraspace] Connecting to PostgreSQL

On Wed, 2009-09-23 at 16:06 +0200, Sherwyn Mack wrote: 
> Hi All
> 
> Does anybody know how to ODBC to PostgreSQL? I have the PostgreSQL ODBC driver, but my connection attempts are being refused by the server. Actively refused if I dissalow ssl.
> 
> I've been trying this one for two days now.
I am not sure if you can ODBC to PostgreSQL from Dspace.
The connection uses JDBC to your PostgreSQL server.

Are the PostgreSQL server and Dspace installed on the same server ?

> 
> Thanks
> Sherwyn
> 
> -----Original Message-----
> From: duraspace-admin at moo.sun.ac.za [mailto:duraspace-admin at moo.sun.ac.za] On Behalf Of Hilton Gibson
> Sent: 23 September 2009 01:08 PM
> To: Duraspace
> Subject: [Duraspace] Dspace Manakin - BIB ITD
> 
> Hi All
> 
> The following wiki page has been updated with working examples of customising the Manakin XMLUI.
> http://services.lib.sun.ac.za/wiki/index.php/Dspace_Manakin
> 
> Enjoy
> 
> --
> Hilton Gibson
> Systems Administrator
> JS Gericke Library
> Stellenbosch University
> Private Bag X5036 Stellenbosch 7599
> South Africa
> 
> Tel: +27 21 808 4100 | Cell: +27 84 646 4758
> 
> _______________________________________________
> Duraspace mailing list
> Duraspace at moo.sun.ac.za
> http://moo.sun.ac.za/mailman/listinfo/duraspace
> 
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.409 / Virus Database: 270.13.112/2390 - Release Date: 09/23/09 05:52:00
> _______________________________________________
> Duraspace mailing list
> Duraspace at moo.sun.ac.za
> http://moo.sun.ac.za/mailman/listinfo/duraspace

-- 
Hilton Gibson
Systems Administrator
JS Gericke Library
Stellenbosch University
Private Bag X5036 Stellenbosch 7599
South Africa

Tel: +27 21 808 4100 | Cell: +27 84 646 4758

_______________________________________________
Duraspace mailing list
Duraspace at moo.sun.ac.za
http://moo.sun.ac.za/mailman/listinfo/duraspace
-------------- next part --------------
A non-text attachment was scrubbed...
Name: pgAdmin.JPG
Type: image/jpeg
Size: 171241 bytes
Desc: pgAdmin.JPG
URL: <http://lists.lib.sun.ac.za/pipermail/duraspace/attachments/20090923/2fd035c3/attachment.jpe>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: ODBC.jpg
Type: image/jpeg
Size: 28748 bytes
Desc: ODBC.jpg
URL: <http://lists.lib.sun.ac.za/pipermail/duraspace/attachments/20090923/2fd035c3/attachment-0002.jpg>