Applix Data access of Remote PostgreSQL Databases

I got e-mail asking about accessing remote PostgreSQL databases from
Applix Data, so I thought I'd write up how it all works.  I'm going to
attach the (short text) document so it gets in the archives, since I'm
not all that confident that my current ISP is long for this world.

Comments?  Questions?

-- cary

----------------------------- cut -----------------------------

Accessing Remote PostgreSQL Databases from Applix Data
------------------------------------------------------

There are two ways to access remote PostgreSQL databases from
Applix Data:  using the PostgreSQL protocol or using
the Axnet protocol over the TCP/IP link.

In this document the term 'Server' means the machine with
the PostgreSQL database being accessed.  The 'Client' is the
machine running Applixware.

NOTE:

Allowing TCP/IP Connections To Your Database may Compromise
the security and integrity of your data, as well as open potential
security holes on your machine[1].  Think first!

Using The PostgreSQL Protocol
-----------------------------

To do this, make sure that PostgreSQL on the server is configured to
accept connections from the client.

1) Make sure postmaster is started with the -i option so it listens
   for incoming connections on a TCP/IP socket.

2) Make sure your pg_hba.conf (in the PG_DATA directory) is set up
   to allow connections from the client machine.

You can test this by running "pgsql -h server" from the client machine.
You should be able to connect and view your data.

Next, configure .odbc.ini on the client machine.  Add another data source,
and set the hostname to be the name of the server machine.  For example,
the following .odbc.ini has both a local and a remote database.

    [ODBC Data Sources]
    cary_pgsql=Cary's Local PostgreSQL Database
    cary_pgsql_jason=Cary's PostgreSQL Database on Jason
    [cary_pgsql]
    Driver=/home/cary/new/odbc/pgsql/src/libpsqlodbc.so.0.25
    Description="Carys PSQL Database"
    Servername=localhost
    Port=5432
    Username=cary
    Password=cary
    Database=cary
    ReadOnly=0
    TextAsLongVarchar=0
    [cary_pgsql_jason]
    Driver=/home/cary/new/odbc/pgsql/src/libpsqlodbc.so.0.25
    Description="Carys PSQL Database on Jason"
    Servername=jason
    Port=5432
    Username=cary
    Password=cary
    Database=cary
    ReadOnly=0
    TextAsLongVarchar=0
    ...

At this point you should be able to start Applix Data on the client
machine.  Query->Select Server, Select ODBC databases, and click 'browse'
(make sure the Host: entry is empty).  The new, remote database should
appear.

Select this database, click OK, and you can now access the remote
database.  What happens is that the PostODBC ODBC driver on the
client connects to the postmaster process on the server, and communicates
using the postgresql front-end/back-end protocol.  Note that in
this configuration, Applix is NOT required on the database server
machine.


Using the Axnet Protocol
------------------------

To do this you need to have Applixware installed on both machines.
On the Server machine, configure axnet and run as root, as described
in the 'Linux Sys Admin' book.  This involves adding an entry
to /etc/services, and starting axnet as root.

Once this is done, start Applix Data on the client machine.  Select ODBC,
enter the Host name of the server machine, and click browse.  A list of
ODBC data sources will appear.  Select the one you want.  An entry
like

  cary_pgsql on jason using Axnet in ODBC

Will appear in the List of Database Connections.  Select this and click
ok.  You can now select tables end execute queries from the remote
database.

Note that if on the server machine you have configured a remote
database as described in "Using the PostgreSQL Protocol", you should
be able to use the first server to connect to yet another database
server. (I have not done this yet).


Debugging
----------

1) If using the PostgreSQL protocol, make sure you can access
   the database from the client using psql.

2) Use strace on either the axnet or postmaster process.  I.E. strace -s 512 -f -p <pid>.

3) "connect: Cannot launch gateway on server err 10313"  means the axnet process
   on the server cannot be contacted.

Notes
-----

[1] Axnet needs to run as root, and since it is closed-source we don
    not know if it has security holes.  The postmaster process runs
    as the database owner (bad, but not root), and can be audited
    for security problems.


Copyright Cary B. O'Brien, 1998

Permission for non-profit electronic redistribution is granted.  All
other rights reserved.