Applix Data access of Remote PostgreSQL Databases - Mailing list pgsql-interfaces
From | Cary B. O'Brien |
---|---|
Subject | Applix Data access of Remote PostgreSQL Databases |
Date | |
Msg-id | 199810301624.LAA15136@access5.digex.net Whole thread Raw |
List | pgsql-interfaces |
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.
pgsql-interfaces by date: