[ic] Interchange, Connecting to Other (Not Default) and Remote Database

Peter peter at pajamian.dhs.org
Fri Jun 30 08:03:29 EDT 2006


On 06/30/2006 03:50 AM, Andrew N wrote:
> Hello again.
> 
> I have an Interchange catalog running on one machine connecting to its
> one default database on the same machine.  However I must now access
> data from one table on another machine on the Internet.  Both are
> postgres databases.  I have never done anything like this.
> 
> 1.  Connect to another database, meaning not the default pgsql database
> 2.  This other database is on another machine on the Internet, meaning
> not on the same local network
> 3.  Setup this other postgres database I am connected to to receive
> external connections
> 
> ANY advice, tips, links, etc would be much appreciated.

Add a user to the remote database, assign the minimal privlidges you 
need to that user for the tables(s) you want and assign a password.

Edit the pg_hba.conf file (in the data directory, same location as 
postgresql.conf) for the remote database.  You need to add a line 
towards the end of the file like this (note this is for my postgresql 
8.0 database, older versions have a different format):
host    dbname   username        0.0.0.0/32       md5

...of course you should substitute the appropriate dbname, username, and 
IP address of the interchange server.  You can do more lenient 
permissions, of course, but I suggest makign it as tight as possible for 
a remote connection.  The aove line would allow only the one specific 
user to connect to the one specific db and only from the one specific IP 
address and will use md5 password authentication.

Restart the remote postgresql db.

add or modify the catroot/dbconf/pgsql/tablename.pgsql.  Add or modify 
the following lines:
Database mytable mytable.txt dbi:Pg:dbname=mydb;host=dbserver.example.com
Database mytable USER username
Database mytable PASS password

here mydb is the remote database name, host is the remote hostname, 
username and password are for connecting to the remote db.

Apply changes and you should be set.

Peter


More information about the interchange-users mailing list