Chapter 13. HyperSQL Network Listeners (Servers)

As described in the Running and Using HyperSQL chapter, network listeners (servers) provide connectivity to catalogs from different JVM processes. The HyperSQL listeners support both ipv4 and ipv6 network addressing.

    java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:mydb --dbname.0 xdb

    java -cp ../lib/hsqldb.jar org.hsqldb.server.Server

    java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --props myserver.props

    java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --help

    java -cp ../lib/hsqldb.jar org.hsqldb.server.WebServer

Properties files for running the servers are not created automatically. You should create your own files that contain server.property=value pairs for each property. The server.properties or webserver.properties files must be located in the directory where the command to run the org.hsqldb.server.Server class is issued.

In all properties files, values are case-sensitive. All values apart from names of files or pages are required in lowercase (e.g. server.silent=FALSE will have no effect, but server.silent=false will work). Supported properties and their default values (if any) are as follows:

In HyperSQL version 2.0, each server can serve an unlimited number of databases simultaneously. The server.database.0 property defines the filename / path whereas the server.dbname.0 defines the lowercase alias used by clients to connect to that database. The digit 0 is incremented for the second database and so on. Values for the server.database.n property can use the mem:, file: or res: prefixes and connection properties as discussed under CONNECTIONS. For example,

    database.0=mem:temp;sql.enforce_strict_size=true;

Properties or default values specific to server.properties are:

Properties or default values specific to webserver.properties are:

An example of the contents of a server.properties file is given below:

    server.database.0=file:/opt/db/accounts
    server.dbname.0=accounts

    server.database.1=file:/opt/db/mydb
    server.dbname.1=enrolments

    server.database.2=mem:adatabase
    server.dbname.2=quickdb

In the above example, the server.properties file indicates that the server provides access to 3 different databases. Two of the databases are file-based, while the third is all-in-memory. The aliases for the databases that the users connect to are accounts, enrolments and quickdb.

All the above properties and their values can be specified on the command line to start the server by omitting the server. prefix. If a property/value pair is specified on the command line, it overrides the property value specified in the server.properties or webserver.properties file.

	Note
	Upgrading: If you have existing custom properties files, change the values to the new naming convention. Note the use of digits at the end of server.database.n and server.dbname.n properties.

If you want to start the server from within your application, as opposed to the command line or batch files, you should create an instance of Server or Web Server, then assign the properties and start the Server. An working example of this can be found in the org.hsqldb.test.TestBase source. The example below sets the same properties as in the server.properties file example.

    HsqlProperties p = new HsqlProperties();
    p.setProperty("server.database.0","file:/opt/db/accounts");
    p.setProperty("server.dbname.0","an_alias");
    // set up the rest of properties

    // alternative to the above is
    Server server = new Server();
    server.setProperties(p);
    server.setLogWriter(null); // can use custom writer
    server.setErrWriter(null); // can use custom writer
    server.start();

The Server object has several alternative methods for setting databases and their public names. The server should be shutdown using the shutdown() method.

If the server.remote_open property is true, the Server works differently from the normal mode. In this mode, it is not necessary to have any databases listed as server.database.0 etc. in the Server startup properties. If there are databases listed, they are opened as normal. The server does not shutdown when the last database is closed.

In this mode, a connection can be established to a database that is not open or does not exist. The server will open the database or create it, then return a connection to the database.

The connection URL must include the path to the database, separated with a semicolon from the alias. In the example below, the database path specified as file:C:/files/mydatabase is opened and the database alias xdb is assigned to the database. After this, the next connection to the specified alias will connect to the same database. The database path can also point to a mem: database. If you use database properties on the URL, these properties are used when the new database is created. If no database properties are used on the URL, you can also specify the path with filepath=<path>. Examples below:

Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb;file:C:/files/mydatabase", "SA", "");
Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb;mem:test;sql.enforce_types=true", "SA", "");
Connection c = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost/xdb;filepath=file:C:/files/mydatabase", "SA", "");

The path can be a file: or mem: database.

Each database started by a Server has its own URL. When new databases are created by the server, the database properties for each of the new database can be appended to the database URL. Examples below:

// example in server.propertie file
    server.database.0=file:/opt/db/accounts;hsqldb.default_table_type=cached;sql.enforce_names=true
    server.dbname.0=accounts

// example for setting the property programatically
    HsqlProperties p = new HsqlProperties();
    p.setProperty("server.database.0","file:/opt/db/accounts;hsqldb.default_table_type=cached;sql.enforce_names=true");

The specified properties apply only to a new database. They have no effect on an existing database apart from a few properties such as readonly listed in the Properties chapter.

This section explains how to encrypt the stream between JDBC network clients and HyperSQL Listeners. If you are running an in-process (non-Listener) setup, this chapter does not apply to you.

Encrypting your JDBC connection

At this time, only 1-way, server-cert encryption is tested.

Client-Side

Just use one of the following protocol prefixes.

Hsqldb TLS URL Prefixes

• jdbc:hsqldb:hsqls://

• jdbc:hsqldb:https://

At this time, the latter will only work for clients running with Java 1.4 or later.

If the listener you wish to connect to is using a certificate approved by your default trust keystore, then there is nothing else to do. If not, then you need to tell Java to "trust" the server cert. (It's a slight over-simplification to say that if the server certificate was purchased, then you are all set; if somebody "signed their own" certificate by self-signing or using a private ca certificate, then you need to set up trust).

First, you need to obtain the cert (only the "public" part of it). Since this cert is passed to all clients, you could obtain it by writing a Java client that dumps it to file, or perhaps by using openssl s_client. Since in most cases, if you want to trust a non-commercial cert, you probably have access to the server keystore, I'll show an example of how to get what you need from the server-side JKS keystore.

You may already have an X509 cert for your server. If you have a server keystore, then you can generate a X509 cert like this.

Example 13.1. Exporting certificate from the server's keystore

    keytool -export -keystore server.store -alias existing_alias -file server.cer

In this example, server.cer is the X509 certificate that you need for the next step.

Now, you need to add this cert to one of the system trust keystores or to a keystore of your own. See the Customizing Stores section in JSSERefGuide.html to see where your system trust keystores are. You can put private keystores anywhere you want to. The following command will add the cert to an existing keystore, or create a new keystore if client.store doesn't exist.

Example 13.2. Adding a certificate to the client keystore

    keytool -import -trustcacerts -keystore trust.store -alias new_alias -file server.cer

If you are making a new keystore, you probably want to start with a copy of your system default keystore which you can find somewhere under your JAVA_HOME directory (typically jre/lib/security/cacerts for a JDK, but I forget exactly where it is for a JRE).

Unless your OS can't stop other people from writing to your files, you probably do not want to set a password on the trust keystore.

If you added the cert to a system trust store, then you are finished. Otherwise you will need to specify your custom trust keystore to your client program. The generic way to set the trust keystore is to set the system property javax.net.ssl.trustStore every time that you run your client program. For example

Example 13.3. Specifying your own trust store to a JDBC client

    java -Djavax.net.ssl.trustStore=/home/blaine/trust.store -jar /path/to/hsqldb.jar dest-urlid

This example runs the program SqlTool. SqlTool has built-in TLS support however, so, for SqlTool you can set truststore on a per-urlid basis in the SqlTool configuration file.

Note: The hostname in your database URL must match the Common Name of the server's certificate exactly. That means that if a site certificate is admc.com, you can not use jdbc:hsqldb:hsqls://localhost or jdbc:hsqldb:hsqls://www.admc.com:1100 to connect to it.

If you want more details on anything, see JSSERefGuide.html on Sun's site, or in the subdirectory docs/guide/security/jsse of your Java SE docs.

Server-Side (Listener-Side)

Get yourself a JKS keystore containing a private key. Then set properties server.tls, system.javax.net.ssl.keyStore and system.javax.net.ssl.keyStorePassword in your server.properties or webserver.properties file. Set server.tls to true, system.javax.net.ssl.keyStore to the path of the private key JKS keystore, and system.javax.net.ssl.keyStorePassword to the password (of both the keystore and the private key record-- they must be the same). If you specify relative file path values, they will be resolved relative to the ${user.dir} when the JRE is started.

	Caution
	If you set any password in a .properties (or any other) file, you need to restrict access to the file. On a good operating system, you can do this like so: chmod 600 path/to/server.properties

The values and behavior of the system.* settings above match the usage documented for javax.net.ssl.keyStorePassword and javax.net.ssl.keyStore in the JSSE docs.

	Note
	Before version 2.0, HyperSQL depended on directly setting the corresponding JSSE properties. The new idiom is more secure and easier to manage. If you have an old password in a UNIX init script config file, you should remove it.

Making a Private-key Keystore

There are two main ways to do this. Either you can use a certificate signed by a certificate authority, or you can make your own. One thing that you need to know in both cases is, the Common Name of the cert has to be the exact hostname that JDBC clients will use in their database URL.

CA-Signed Cert

I'm not going to tell you how to get a CA-signed SSL certificate. That is well documented at many other places.

Assuming that you have a standard pem-style private key certificate, here's how you can use openssl and the program DERImport to get it into a JKS keystore.

Because I have spent a lot of time on this document already, I am just giving you an example.

Example 13.4. Getting a pem-style private key into a JKS keystore

    openssl pkcs8 -topk8 -outform DER -in Xpvk.pem -inform PEM -out Xpvk.pk8 -nocrypt

    openssl x509 -in Xcert.pem -out Xcert.der -outform DER

    java DERImport new.keystore NEWALIAS Xpvk.pk8 Xcert.der

	Important
	Make sure to set the password of the key exactly the same as the password for the keystore!

You need the program DERImport.class of course. Do some internet searches to find DERImport.java or DERImport.class and download it.

If DERImport has become difficult to obtain, I can write a program to do the same thing-- just let me know.

Non-CA-Signed Cert

Run man keytool or see the Creating a Keystore section of JSSERefGuide.html.

JDBC connections will always be denied if the supplied user and password are not found in the target catalog. But an HyperSQL listener can also restrict access at the listener level, even protecting private catalogs which have insecure (or default) passwords. If you have an in-process setup, this section of the Guide doesn't apply to you.

Many (in fact, most) distributed database applications don't have application clients connect directly to the database, but instead encapsulate access in a controlling process. For example, a web app will usually access the data source on behalf of users, with end-user web browsers never accessing the database directly. In these cases and others, the security benefits of restricting listener access to specific source addresses is well worth the effort. ACLs work by restricting access according to the source address of the incoming connection request. This is efficient because the database engine never even gets the request until it is approved by the ACL filter code.

The sample file sample/acl.txt in your HyperSQL distribution explains how to write an ACL file.

# $Id: acl.txt 826 2009-01-17 05:04:52Z unsaved $

# Sample HyperSQL Network Listener ACL file.
# Specify "allow" and "deny" rules
# For address specifications, individual addresses, host names, and
# network addresses with /bit suffix are allowed, but read the caveat about
# host names below, under the sample "localhost" rule.

# Blank lines ignored.
   # Lines with # as the first non-whitespace character are ignored.


allow 2001:db8::/32
# Allow this 32-bit ipv4 subnet

allow localhost
# You should use numerical addresses in ACL files, unless you are certain that
# the name will always be known to your network address resolution system
# (assume that you will lose Internet connectivity at some time).
# With a default name resolution setup on UNIX, you are safe to use names
# defined in your /etc/hosts file.

deny 192.168.101.253
# Deny a single IP address.
# In our example, 192.168.101.0/24 is our local, organizational network.
# 192.168.101.253 is the IP address of our Intern's PC.
# The Intern does not have permission to access our databases directly.

allow 192.168.101.0/24

# Any ipv4 or ipv6 candidate address not matched above will be denied

You put your file wherever it is convenient for you, and specify that path with the property server.acl or webserver.acl in your server.properties or webserver.properties file (depending on whether your listener instance is a Server or WebServer). You can specify the ACL file path with an absolute or relative path. If you use a relative path, it must be relative to the .properties file. It's often convenient to name the ACL file acl.txt, in the same directory as your .properties file and specify the property value as just acl.txt. This file name is intuitive, and things will continue to work as expected if you move or copy the entire directory.

	Warning
	If your Server or WebServer was started with a *.acl property, changes afterwards to the ACL file will be picked up immediately by your listener instance. You are advised to use the procedure below to prevent partial edits or mistakes from crippling your running server.

When you edit your ACL file, it is both more convenient and more secure to test it as explained here before activating it. You could, of course, test an ACL file by editing it in-place, then trying to connect to your listener with JDBC clients from various source addresses. Besides being mightily laborious and boring, with this method it is very easy to accidentally open access to all source addresses or to deny access to all users until you fix incorrect ACL entries.

The suggested method of creating or changing ACLs is to work with an inactive file (for new ACL files, just don't enable the *.acl property yet; for changing an existing file, just copy it to a temporary file and edit the temporary file). Then use the ServerAcl class to test it.

    java -cp path/to/hsqldb.jar org.hsqldb.server.ServerAcl path/to/acl.txt

If the specified ACL file fails validation, you will be given details about the problem. Otherwise, the validated rules will be displayed (including the implicit, default deny rules). You then type in host names and addresses, one-per-line. Each name or address is tested as if it were a HyperSQL network client address, using the same exact method that the HyperSQL listener will use. (HyperSQL listeners use this same ServerAcl class to test incoming source addresses). ServerAcl will report the rule which matches and whether access is denied or allowed to that address.

If you have edited a copy of an existing ACL file (as suggested above), then overwrite your live ACL file with your new, validated ACL file. I.e., copy your temp file over top of your live ACL file.

ServerAcl can be run in the same exact way described above, to troubleshoot runtime access issues. If you use an ACL file and a user or application can't get a connection to the database, you can run ServerAcl to quickly and definitively find if the client is being prohibited by an ACL rule.