|
Configuring SQL Relay You can use sqlr-config-gtk to configure SQL Relay, but you can also edit the configuration file (usually /usr/local/firstworks/etc/sqlrelay.conf) by hand. The file format is complicated and is best explained with an example. <!DOCTYPE instances SYSTEM "sqlrelay.dtd"> <instances> <instance id="example" port="9000" socket="/tmp/examplesocket" dbase="oracle8" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener and connection"> <users> <user user="user1" password="password1"/> <user user="user2" password="password2"/> <user user="user3" password="password3"/> </users> <connections> <connection connectionid="db1" string="user=exampleuser1;password=examplepassword1;oracle_sid=EXAMPLE1;oracle_home=/u01/app/oracle/product/8.1.7" metric="1"/> <connection connectionid="db1" string="user=exampleuser2;password=examplepassword2;oracle_sid=EXAMPLE2;oracle_home=/u01/app/oracle/product/8.1.7" metric="3"/> <connection connectionid="db1" string="user=exampleuser3;password=examplepassword3;oracle_sid=EXAMPLE3;oracle_home=/u01/app/oracle/product/8.1.7" metric="5"/> <connection connectionid="db1" string="user=exampleuser4;password=examplepassword4;oracle_sid=EXAMPLE4;oracle_home=/u01/app/oracle/product/8.1.7" metric="6"/> </connections> </instance> </instances> As you can see, it's an XML file. Below is it's DTD. <!DOCTYPE sqlrelay [ <!ELEMENT instances (instance*)> <!ELEMENT instance (users,connections)> <!ATTLIST instance id CDATA #REQUIRED> <!ATTLIST instance port CDATA> <!ATTLIST instance socket CDATA> <!ATTLIST instance dbase (oracle7|oracle8|mysql|msql|postgresql|sqlite|lago|freetds|sybase|odbc|db2|interbase) #REQUIRED> <!ATTLIST instance connections CDATA> <!ATTLIST instance endofsession (commit|rollback)> <!ATTLIST instance maxconnections CDATA> <!ATTLIST instance maxqueuelength CDATA> <!ATTLIST instance growby CDATA> <!ATTLIST instance ttl CDATA> <!ATTLIST instance sessiontimeout CDATA> <!ATTLIST instance runasuser CDATA> <!ATTLIST instance runasgroup CDATA> <!ATTLIST instance cursors CDATA> <!ATTLIST instance authtier (listener|connection|listener and connection|database) #REQUIRED> <!ATTLIST instance handoff (pass|reconnect) #REQUIRED> <!ATTLIST instance deniedips CDATA> <!ATTLIST instance allowedips CDATA> <!ATTLIST instance debug (none|listener|connection|listener and connection)> <!ELEMENT users (user*)> <!ELEMENT user EMPTY> <!ATTLIST user user CDATA #REQUIRED> <!ATTLIST user password CDATA #REQUIRED> <!ELEMENT connections (connection*)> <!ELEMENT connection EMPTY> <!ATTLIST connection connectionid CDATA #REQUIRED> <!ATTLIST connection string CDATA #REQUIRED> <!ATTLIST connection metric CDATA #REQUIRED> ]> So what do all these tags do? The instances tag is just the root tag. The sqlrelay.conf file should have only one of these tags surrounding all other tags. Each instance tag defines an instance of sqlrelay. An instance consists of one sqlr-listener and one or more sqlr-connection daemons. You can define as many of these as you like. The users tag surrounds the list of users that may connect to the instance. Each user tag defines a user/password combination that may be used to connect to the instance. The connections tag surrounds the list of sqlr-connection daemon configurations used by the instance. Each connection tag defines a sqlr-connection daemon configuration. In most cases, there will be only 1 of these tags. In cases where clustered or replicated databases are used, the sqlr-connection daemons may need to be able to connect to multiple machines. In that case, there would be more than 1 line here. Below is a description of the attributes for each tag.
Most of the tag attributes are straightforward. The most complicated one are the metric attribute of the connection tag and the authtier attribute of the instance tag. MetricThe metric attribute doesn't define how many connections are started, the connections attribute of the instance tag defines that. The metric attribute influences how many of the total connections will be of that connection type. The higher the metric relative to the other metrics, the more of that connection type will be started. AuthtierThe client will send a user and password to the sqlr-listener and sqlr-connection daemons when it connects to them. The authtier attribute indicates which daemon will actually pay attention to that user and password. If the authtier attribute is set to "listener", the sqlr-listener will compare the user/password to the list of user/passwords in the sqlrelay.conf file defined in the users tag and accept or reject the client connection. If the client is accepted, it is handed off to the sqlr-connection-database daemon which assumes that it has already been authenticated and does not perform it's own authentication. If the authtier attribute is set to "connection", the sqlr-listener will ignore the user/password and just hand off the client to the sqlr-connection-database daemon which will compare the user/password to the list of user/passwords in the sqlrelay.conf file and accept or reject the client connection. If the authtier attribute is set to "listener and connection" then both daemons will perform the authentication. This is the most secure method and the default but is slower than "listener" or "connection" alone. If the authtier attribute is set to "database", the sqlr-listener daemon will ignore the user/password and the sqlr-connection-database daemon will use database-specific methods for authenticating and proxying the user. Currently this is only supported by Oracle 8i. The sqlr-connection-database daemon must be configured to log into the database as a user that can proxy other users and the client must attempt to log in to SQL Relay as one of the users that can be proxied. See this document for more information including instructions for configuring Oracle. HandoffWhen an SQL Relay client needs to talk to the database, it connects to a listner process which queues it up until a database connection daemon is available. When a daemon is available, the client is "handed off" to it. This "handoff" can be done in one of two ways. The client can disconnect from the listener and reconnect to the connection daemon, or the existing connection to the client can be passed from the listener to the connection daemon. These two methods are referred to as "reconnect" and "pass". "Reconnect" works on every platform. "Pass" works on most platforms but not all. SCO OpenServer and Linux kernels prior to 2.2 don't support "pass". Other platforms may not support "pass" as well but those certainly don't. If SQL Relay is being run on a different machine than it's clients, "pass" is substantially faster than "reconnect" and more lightweight in any case. It should be used if possible. DebugIf the debug attribute is not set to "none", when sqlr-start is run, it runs special versions of the sqlr-listener and sqlr-connection daemons which log debug information to files in /usr/local/firstworks/var/sqlrelay/debug. When the daemons start up, they print out the exact filename that they are logging to. This filename will be listener.PID or connection.PID where PID is the process ID of the daemon. Note that the sqlr-listener forks each time it gets a client connection and generates a seperate file for each forked process as well as one for the main process. The debug attribute can be one of: "none", "listener", "connection" or "listener and connection". If "none" is specified, no debugging information is logged. If "listener" is specified, then only the listener daemon logs debug information. If "connection" is specified, then only the connection daemons log debug information. If "listener and connection" is specified, all daemons log debug information. |